The MDURATION function in Excel is a financial tool that calculates the modified duration of a bond or other fixed-income security. This function helps investors understand the sensitivity of a security’s price to changes in interest rates, providing valuable insights for managing interest rate risk. A higher duration indicates greater sensitivity to rate changes, making this function essential for bond portfolio management.

Syntax

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

• settlement: The security’s settlement date, representing when you buy the security.
• maturity: The security’s maturity date when the principal is repaid.
• coupon: The annual coupon rate of the security.
• yield: The security’s annual yield to maturity.
• frequency: The number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly).
• [basis]: (Optional) The type of day count basis to use. It defaults to 0 if omitted.

Example #1

=MDURATION("2023-10-01", "2033-10-01", 0.05, 0.04, 2)

This function computes the modified duration for a bond settled on October 1, 2023, maturing on October 1, 2033, with a 5% coupon and a 4% yield, resulting in a duration of approximately 8.88 years.

Example #2

=MDURATION("2024-01-01", "2026-01-01", 0.06, 0.05, 1)

This example calculates the modified duration for a bond purchased on January 1, 2024, maturing on January 1, 2026, with a 6% coupon and a 5% yield. The result would be approximately 1.87 years.

Example #3

=MDURATION("2023-07-15", "2025-07-15", 0.03, 0.02, 4)

This function estimates the modified duration for a bond with a settlement date of July 15, 2023, maturing on July 15, 2025, with a 3% coupon and a yield of 2%, yielding a duration of around 2.56 years.

Error handling

• NUM!: This error occurs if the settlement date is after the maturity date or if the frequency is invalid.
• VALUE!: This error appears when non-numeric arguments are supplied to the function, such as text strings instead of numbers.
• REF!: This error indicates that a cell reference is not valid, often occurring when a referenced cell is deleted or moved.

Conclusion

In summary, the MDURATION function is a powerful tool for financial analysts and investors alike. By providing insights into how sensitive a bond’s price is to interest rate changes, it enables better decision-making in bond management and portfolio strategies. Understanding how to use this function effectively can provide a significant advantage in the complex realm of fixed-income investing.