The MDURATION function in Google Sheets is vital for investors and financial analysts, as it calculates the modified Macaulay duration of a security that pays periodic interest. This metric is essential for assessing interest rate risk and understanding the sensitivity of a bond’s price to changes in interest rates.

Syntax

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

• settlement: The date when the bond is purchased.
• maturity: The date when the bond matures.
• coupon: The annual coupon payment of the bond (in percentage).
• yield: The bond’s annual yield (as a decimal).
• frequency: The number of coupon payments per year (e.g., 1 for annual, 2 for semi-annual).
• [basis]: Optional; the type of day count basis to use (0-4, default is 0). This determines how the bond’s interest calculation is handled.

Example #1

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

This example calculates the modified duration for a bond bought on January 1, 2023, maturing on January 1, 2030, with an annual coupon rate of 5% and a yield of 4%, with semi-annual payments. The result might be around 5.67 years, indicating the bond’s price sensitivity to interest rate changes.

Example #2

=MDURATION("2023-06-15", "2033-06-15", 0.06, 0.045, 1)

In this case, the function computes the modified duration for a bond purchased on June 15, 2023, maturing on June 15, 2033, with a 6% coupon and a yield of 4.5% with annual payments. The output could be approximately 6.23 years, reflecting how the bond would react to interest rate fluctuations.

Example #3

=MDURATION("2024-03-01", "2028-03-01", 0.04, 0.03, 4, 1)

Here, this function calculates the modified duration for a bond purchased on March 1, 2024, maturing on March 1, 2028, with a 4% coupon at a 3% yield, making quarterly payments. The result could be around 3.85 years, indicating a specific level of interest rate risk.

Error handling

• NUM!: This error can occur if the settlement date is later than the maturity date.
• VALUE!: This indicates that one or more arguments are of the wrong type, such as text instead of a date or number.
• DIV/0!: Occurs if the frequency is set to 0, meaning there are no coupon payments specified.

Conclusion

The MDURATION function is an essential tool for finance professionals, providing insights into the interest rate risk associated with various securities. Understanding how to use this function effectively allows investors to make informed decisions and better manage their portfolios in fluctuating market conditions.