MDURATION Function in Google Sheets

Calculate the modified duration of a security with the MDURATION function.

MDURATION Function

The MDURATION function in Google Sheets calculates the modified duration of a security, which measures its price sensitivity to changes in yield.

Syntax

MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])
  • settlement: The security’s settlement date, when the buyer takes ownership. This is a required parameter.
  • maturity: The maturity date, when the security expires. This is a required parameter.
  • rate: The annual coupon rate. This is a required parameter.
  • yield: The annual yield of the security. This is a required parameter.
  • frequency: The number of coupon payments per year. This is a required parameter.
  • day_count_convention: (Optional) The day count convention to use. Default is 0 (30/360).

Examples

  1. Modified Duration Calculation

Calculate the modified duration of a bond with a settlement date of 01/01/2023, maturity date of 01/01/2033, annual coupon rate of 5%, annual yield of 6%, and semi-annual payments:

=MDURATION("01/01/2023", "01/01/2033", 5%, 6%, 2)

This will output approximately 7.96.

Notes

  • A higher modified duration indicates greater sensitivity to interest rate changes.
  • DURATION: Calculate the Macaulay duration of a security.
  • YIELD: Calculate the yield of a security paying periodic interest.