MDURATION

Formulas / MDURATION
Calculate 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?
The MDURATION function returns the modified Macauley duration for a security with an assumed par value of $100.
What are the required arguments for the MDURATION function?
The required arguments for the MDURATION function are:
  • 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?
Yes, the optional argument for the MDURATION function is Basis, which is the type of day count basis.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV