DURATION function

The DURATION function in Excel is used to calculate the Macauley duration of a bond, which is a measure of the bond’s interest rate sensitivity and the weighted average time to receive the bond’s cash flows. This function is important in financial analysis as it helps assess the interest rate risk of a bond.

Syntax

DURATION(settlement, maturity, coupon, yld, frequency, [basis])

Parameters

• settlement: The settlement date of the bond, which is the date after issuance when the bond is traded to the buyer. This must be entered as a valid Excel date.
• maturity: The maturity date of the bond, which is the date when the bond expires and the principal is repaid. This must also be a valid Excel date.
• coupon: The coupon rate of the bond, expressed as a decimal. For example, for a 6% coupon bond, you would enter 0.06.
• yld: The annual yield of the bond, expressed as a decimal. For example, for a 5% yield, you would enter 0.05.
• frequency: The number of coupon payments per year:
  • 1 for annual payments.
  • 2 for semi-annual payments.
  • 4 for quarterly payments.
• [basis] (optional): The day count basis to use for the calculation. This specifies how the days between the settlement and maturity are calculated. The values for this argument are:
  • 0 = US (NASD) 30/360 (default)
  • 1 = Actual/Actual
  • 2 = Actual/360
  • 3 = Actual/365
  • 4 = European 30/360

How It Works

The DURATION function calculates the Macauley duration of a bond, which is a weighted average of the times until cash flows are received, taking into account the present value of each cash flow. Macauley duration is used to estimate the bond’s price sensitivity to interest rate changes. A higher duration indicates greater sensitivity to interest rate movements.

Formula Example

Let’s assume you have the following information:

• Settlement date: January 1, 2023
• Maturity date: January 1, 2033
• Coupon rate: 6% (0.06)
• Yield: 5% (0.05)
• Frequency: 2 (semi-annual payments)

You can calculate the bond’s duration using the DURATION function:

=DURATION("2023-01-01", "2033-01-01", 0.06, 0.05, 2)

Explanation of the Example:

• "2023-01-01": The settlement date.
• "2033-01-01": The maturity date.
• 0.06: The coupon rate (6%).
• 0.05: The annual yield (5%).
• 2: Frequency of coupon payments (semi-annual payments).
• [basis] is not provided, so it defaults to 0 (US 30/360).

The function will return the Macauley duration of the bond, which indicates the weighted average time it takes to receive the bond’s cash flows, considering the present value of each cash flow.

Important Notes

• Use of Dates: Ensure that both the settlement and maturity dates are valid Excel dates for accurate calculations.
• Yield and Coupon: The yield and coupon rate are key to determining the bond’s interest rate risk and should be entered as decimals (e.g., 6% = 0.06).
• Frequency: The frequency of coupon payments is typically 1 (annual), 2 (semi-annual), or 4 (quarterly). This depends on how often the bond pays interest.

Summary

The DURATION function is a useful tool in bond analysis to calculate the Macauley duration of a bond, which helps investors understand the interest rate risk associated with the bond. A bond with a higher duration is more sensitive to interest rate changes, meaning its price will fluctuate more with changes in interest rates.