DURATION Function in Google Sheets

Calculate the Macaulay duration of a security using the DURATION function.

DURATION Function

The DURATION function in Google Sheets calculates the Macaulay duration of a security that pays periodic interest.

Syntax

DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])
  • settlement: The security’s settlement date. This is a required parameter.
  • maturity: The security’s maturity date. This is a required parameter.
  • rate: The annual coupon rate of the security. 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 convention).

Examples

  1. Calculate Macaulay Duration

Calculate the duration of a security with a settlement date of January 1, 2024, maturity date of January 1, 2029, annual coupon rate of 5%, annual yield of 4%, and semi-annual payments:

=DURATION(DATE(2024,1,1), DATE(2029,1,1), 0.05, 0.04, 2)

This will output 4.645.

Notes

  • The DURATION function is useful for assessing the interest rate risk of a bond.
  • The day_count_convention parameter can significantly affect the output depending on the chosen convention.
  • MDURATION: Calculate the modified duration of a bond.
  • YIELD: Calculate the yield of a security.