DURATION

Formulas / DURATION
Calculate the Macauley duration for a $100 par value.
=DURATION(settlement, maturity, coupon, yld, frequency, [basis])
  • Settlement - required, date when the coupon is purchased
  • Maturity - required, date when the coupon expires
  • Coupon - required, annual coupon rate
  • Yld - required, annual yield
  • Frequency - required, number of coupon payments per year
  • [Basis] - [OPTIONAL] financial day count basis

Examples

  • =DURATION("01/01/2023", "01/01/2033", 0.05, 0.06, 2, 0)

    The DURATION function calculates the Macaulay duration of a bond with a settlement date of January 1, 2023, a maturity date of January 1, 2033, an annual coupon rate of 5%, a yield to maturity of 6%, semi-annual coupon payments (2 payments per year), and a day count basis of 0 (30/360). The formula returns approximately 7.87, which is the Macaulay duration of the bond in years.

  • =DURATION("01/01/2023", "01/01/2028", 0.04, 0.045, 4, 0)

    In this example, the DURATION function calculates the Macaulay duration of a bond with a settlement date of January 1, 2023, a maturity date of January 1, 2028, an annual coupon rate of 4%, a yield to maturity of 4.5%, quarterly coupon payments (4 payments per year), and a day count basis of 0 (30/360). The formula returns approximately 4.47, which is the Macaulay duration of the bond in years.


Summary

The DURATION function is a financial function used to calculate the Macauley duration for a $100 par value. It requires several arguments such as settlement, maturity, coupon, yield, frequency, and basis.

  • The DURATION function measures a bond price's response to changes in yield, by calculating the weighted average of the present value of cash flows over a period of time.

Frequently Asked Questions

What is the DURATION function?
The DURATION function is a financial function that calculates the Macauley duration for an assumed par value of $100. The duration is the weighted average of the present value of cash flows over the life of the investment.
What does the DURATION function calculate?
The DURATION function calculates the Macauley duration for an assumed par value of $100. This is the weighted average of the present value of cash flows over the life of the investment.
What are the benefits of using the DURATION function?
  • The DURATION function helps to measure the volatility of a bond by calculating the weighted average of the present value of cash flows over the life of the investment.
  • The DURATION function can help investors make more informed decisions when it comes to investing in bonds.
  • The DURATION function is an efficient and easy way to calculate the Macauley duration of a bond.
What is the Macauley duration?
The Macauley duration is the weighted average of the present value of cash flows over the life of the investment. It is a measure of the volatility of a bond.

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