DURATION Function

What is the DURATION Function?

The DURATION excel function is a financial function that returns the Macauley duration of a security that pays a periodic interest rate on an assumed par value of $100.

In 1938, economist Frederick Macaulay introduced the concept of Macaulay duration. Macaulay duration is the measure of the price volatility of bonds. It is a measure of time. In other words, the formula returns the result in the number of years.

The Macaulay duration will usually be between zero and the maturity date for a standard bond with fixed cash flows. When the Macaulay duration is calculated for a zero-coupon bond, the result will equal the maturity date. This is the only instance when the formula returns such a result.

The Macauley duration is important in helping investors understand the risks associated with fixed-income securities. As such, portfolio managers frequently use it when implementing immunization strategies.

The immunization strategy is a strategy that matches the duration of assets and liabilities so that the overall portfolio is protected against changes in interest rates.

The DURATION function can be found under the Financial functions category on the Formulas tab. It can also be used as a worksheet function, which can be entered as part of a formula in a worksheet cell.

Macauley Duration explained

Macauley duration measures the length of time needed for an investor to recover the money invested in securities (such as bonds) through the collection of coupon and principal repayments.

In other words, it estimates the number of years an investor should maintain a position in the bond until the amount paid for the bond is recovered (the present value of the bond’s cash flows equals the amount paid for it).

The Macaulay duration is calculated as the weighted average term before an investor would receive the bond's estimated cash flows.

where the parts of the equation are:

• CF - cash flow at a specific time 
• i - yield to maturity or market discount rate
• P – the price of the bond

The duration is affected by the variables that go into the formula – bond price, term to maturity, coupon rate, and yield to maturity. With all else being equal, the duration increases when the term to maturity increases as it takes longer to receive the principal payment at maturity.

On the other hand, the duration increases when the bond’s coupon decreases (an inverse relationship). This is the case as smaller cash amounts are paid out in the early periods.

There is also an inverse relationship between the duration and yield to maturity, a higher yield to maturity results in a lower duration.

In addition, an increase in interest rates and the existence of a sinking fund or call provisions lower a bond’s duration.

Macauley Duration Example

Now you have a theoretical understanding of the concept, let’s proceed with the practical use of the formula.

Consider an investor who invested in an 8% annual payment bond. The bond has a maturity of 3 years, a yield-to-maturity of 9%, and a current price of $96 per $100 of par. The Macaulay duration of the said bond is:

It will take an average of 2.82 years for the investor of this 3-year bond to recover the amount paid for it.

Among some drawbacks of the Macaulay Duration:

• The Macaulay duration only shows the price change trend when the given yield changes. However, it does not show the price change percentage when the given yield changes and cannot help investors solve the interest rate risk problem. 
• The Macaulay duration does not apply to securities with variable cash flow or cash flow timing. Thus, the Macaulay duration is worthless for callable bonds.

How to use the DURATION Function in Excel?

There are three ways to access the DURATION function in excel:

1. Formulas tab

First, you can utilize the Formulas tab. The function can be found under the Financial category.

After you select the DURATION function from the list, the formula automatically appears. All you have to do is to select the arguments specified below.

2. Entering formula into the worksheet

Alternatively, you can click on any cell in a worksheet and type the formula. The excel formula is as follows:

= DURATION (settlement; maturity; coupon; yld; frequency; basis)

where:

• Settlement (required) – the security’s settlement date, the date when the security is traded to the buyer. 
• Maturity (required) - the security’s maturity date, the date when the security expires.
• Coupon (required) – the security’s coupon rate.
• Yld (required) - the security’s yield.
• Frequency (required) – the number of coupon payments per year (annual payments = 1; semiannual payments = 2; quarterly payments = 4).
• Basis (optional) – the type of day count basis (US (NASD) 30/360, actual/actual, actual/360, actual/365, European 30/360).

3. Utilizing the Formulas tab with manual input

Finally, you can simultaneously utilize the Formulas tab and the manual input of the formula. Select any cell in a worksheet, continue to the Formulas tab, press the Insert Function button, and start typing the formula.

Example: Consider a situation where on January 1st, 2023, an investor purchases a bond with a maturity date falling on December 31st, 2026. The coupon rate is 8%, while the yield to maturity is 9%. Coupon payments are made annually.

The result means that the bond investor will take an average of 3.6 years to recover the amount paid for it.

Things to remember about the DURATION Function

Like with any other Excel function, some things need to be considered when using the DURATION function.

The things to remember are

• The function will not work with the 01/01/1899 date; by default, 01/01/1900 is a starting point in Excel. For the same reason, the function will also not work with 31/12/9999.
• If the settlement date or maturity date is not in a valid date format, the function returns the #VALUE! Error value.
• The function returns #NUM! Error value if coupon rate or yield to maturity is less than 0.
• In cases when the frequency is any number other than 1, 2, or 4, the DURATION function returns #NUM! Error value.
• If the basis is any number other than 0 through 4, the function returns #NUM! Error value.
• DURATION function returns #NUM! Error value when the settlement date exceeds or is equal to the maturity date.

Researched and authored by Kseniia Tokarieva | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• MDURATION Function
• NPV Function
• PRICE Function
• PV Function
• RATE Function