The DURATION function in Excel is a crucial tool for financial analysts and investors, offering a precise means to determine the duration of a security that pays interest at regular intervals. Duration is a critical concept in finance, as it measures the sensitivity of a security’s price to interest rate changes, thereby helping investors to assess interest rate risk effectively.

Syntax

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

• settlement: The date when the security is purchased.
• maturity: The date when the security expires.
• coupon: The annual coupon rate of the security.
• yield: The annual yield on the security.
• frequency: The number of interest payments per year (1 for annual, 2 for semi-annual, and 4 for quarterly).
• basis: (optional) The day count basis used for the calculation, default is 0 (US (NASD) 30/360).

Example #1

DURATION("2023-01-01", "2030-01-01", 0.05, 0.04, 2)

Calculates the duration of a bond purchased on January 1, 2023, maturing on January 1, 2030, with a 5% coupon rate and a 4% yield, with semi-annual interest payments. The result might be approximately 6.12 years.

Example #2

DURATION("2023-06-15", "2033-06-15", 0.06, 0.05, 1)

Determines the duration of a security bought on June 15, 2023, maturing on June 15, 2033, with a 6% annual coupon rate and a yield of 5%. The output could be around 8.85 years.

Example #3

DURATION("2024-03-01", "2029-03-01", 0.04, 0.045, 2)

This function evaluates the duration of a bond acquired on March 1, 2024, expiring on March 1, 2029, that has a 4% coupon and a 4.5% yield, with semi-annual payments. The result is likely near 4.73 years.

Error handling

• VALUE!: Indicates an invalid argument, such as a non-date entry for settlement or maturity.
• NUM!: Occurs when yield is less than zero or when the calculation cannot be performed due to non-reconcilable inputs.
• NAME?: Appears when the function name is not recognized, possibly due to misspelling.

Conclusion

The DURATION function in Excel is an essential tool for evaluating the interest rate sensitivity of securities. By accurately calculating the time until cash flows are received, this function enables investors to make informed decisions regarding their fixed-income investments. Understanding how to use it effectively can greatly enhance financial analysis and portfolio management strategies.