The DURATION function in Excel is used to calculate the Macauley duration of a security that pays periodic interest, which is often used in the analysis of bond investments. This function can be helpful for investors to measure the sensitivity of a bond’s price to changes in interest rates. Here’s how you can use the DURATION function:
Syntax
DURATION(settlement, maturity, coupon, yld, frequency, [basis])
Parameters
- Settlement: The settlement date of the security, which is the date after the issue date when the security is traded to the buyer. This should be entered as a serial date number or a date string.
- Maturity: The maturity date of the security, which is the date when the security expires. This should also be entered as a serial date number or a date string.
- Coupon: The annual coupon rate of the security. This is the interest rate paid by the security and is expressed as a decimal (e.g., for 5%, use 0.05).
- Yld: The annual yield of the security. Similar to the coupon rate, it is expressed as a decimal.
- Frequency: The number of coupon payments per year. Valid arguments are:
- 1 for annual payments
- 2 for semi-annual payments
- 4 for quarterly payments
- [Basis]: Optional. The day count basis to be used. The options are:
- 0 or omitted: US (NASD) 30/360
- 1: Actual/actual
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
Example
Suppose you are analyzing a bond with the following characteristics:
- Settlement date: January 1, 2023
- Maturity date: January 1, 2033
- Annual coupon rate: 6%
- Annual yield: 5%
- Coupon payments are semi-annual
- Basis: Actual/actual (no basis change needed)
The formula to calculate the duration would be:
=DURATION(DATE(2023,1,1), DATE(2033,1,1), 0.06, 0.05, 2, 1)
Steps
- Format dates: Ensure that your settlement and maturity dates are properly formatted as Excel date values.
- Enter the formula: Click on the cell where you want to display the duration, type the formula using the DURATION function, and press Enter.
- Adjust parameters as necessary: Modify the values of settlement, maturity, coupon, yld, frequency, and basis according to the specifics of the bond you are evaluating.
Using the DURATION function helps bond investors and analysts estimate the average time it takes to receive the bond’s cash flows and how sensitive the bond’s price is to interest rate changes.
Unlock Your Potential
Excel
Access
Power BI
