MDURATION
Formulas / MDURATIONCalculate the Macauley modified duration for a security with an assumed par value of $100.
=MDURATION(settlement, maturity, coupon, yld, freq, [basis])
- Settlement - the settlement date of the security after the issue date when the security is traded to the buyer's account
- Maturity - the date the security expires or the maturity date of the security
- Coupon - the annual coupon rate
- Yld - the annual return on the security
- Frequency - the number of coupon payments per year
- Basis - [OPTIONAL] specifies the type of day count basis to use
Examples
=MDURATION(DATE(2021,6,1),DATE(2026,6,1),0.04,0.04,2,0)This returns 5.57 years using the data hardcoded. This example calculates the modified duration of a bond with an annual coupon rate of 4%, semi-annual payments, and a maturity date of 6/1/2026.
=MDURATION(DATE(2021,6,1),DATE(2021,6,1),0.04,0.04,2,0)If the bond in Example 1 had a maturity date of 6/1/2021, and the result would be 0.00 years.
=MDURATION(C12,C14,C10,C11,C15,C16)This returns the modified duration of a bond with an annual coupon rate of 7%, quarterly payments, and a maturity date of 1/1/2028. This example uses the cell references C12, C14, C10, C11, C15, and C16 to input the values needed to calculate the modified duration.
=MDURATION(C12,C13,C10,C11,C15,C16)If the bond in Example 3 had a maturity date of 1/1/2021, and the result would be 0.00 years.
Summary
The MDURATION function calculates the modified Macauley duration of a security, which is a measure of the security's sensitivity to changes in interest rates, using the par value of $100.
- The MDURATION function calculates the modified Macauley Duration of a security with an assumed par value of $100.
- The modified Macauley Duration is a measure of the sensitivity of the security's price to changes in interest rates.
- The calculation of the modified Macauley Duration takes into account both the present value of the security's cash flows, as well as the time value of money.
Frequently Asked Questions
What is the MDURATION function?
What are the required arguments for the MDURATION function?
- Settlement (the security's settlement date)
- Maturity (the security's maturity date)
- Coupon (the security's annual coupon rate)
- Yld (the security's annual yield)
- Frequency (the number of coupon payments per year)
Is there an optional argument for the MDURATION function?
