MDURATION

In this comprehensive article, we will explore everything you need to know about the MDURATION formula in Excel. MDURATION is a financial function that calculates the Macaulay modified duration of a security with an assumed par value of $100. The modified duration is a measure of the price sensitivity of a bond to interest rate changes and is commonly used in bond portfolio management to assess interest rate risk. By the end of this article, you will have a deep understanding of the MDURATION formula, its syntax, examples, tips and tricks, common mistakes, and related formulae.

MDURATION Syntax

The syntax for the MDURATION formula in Excel is as follows:

=MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

Where the function arguments are:

• settlement – The security’s settlement date, which is the date after the issue date when the security is traded to the buyer.
• maturity – The security’s maturity date, which is the date when the security expires.
• coupon – The annual coupon rate of the security.
• yld – The annual yield of the security.
• frequency – The number of coupon payments per year. For annual payments, use 1; for semiannual, use 2; and for quarterly, use 4.
• [basis] – (Optional) The day count basis to use. If omitted, the default is 0, which represents the US (NASD) 30/360 day count basis. Other options include: 1 for Actual/Actual, 2 for Actual/360, 3 for Actual/365, and 4 for European 30/360.

MDURATION Examples

Let’s look at some examples of using the MDURATION formula in Excel:

1. Example 1: Suppose you have a bond with a settlement date of January 1, 2020, a maturity date of January 1, 2030, an annual coupon rate of 5%, an annual yield of 6%, and semiannual payments. To calculate the modified duration, you would use the following formula:

=MDURATION(“1/1/2020”, “1/1/2030”, 5%, 6%, 2)

This formula would return the modified duration of the bond, which is a measure of its price sensitivity to interest rate changes.

1. Example 2: If you have a bond with a settlement date of March 15, 2020, a maturity date of March 15, 2025, an annual coupon rate of 4%, an annual yield of 3%, and quarterly payments, you would use the following formula:

=MDURATION(“3/15/2020”, “3/15/2025”, 4%, 3%, 4)

This formula would return the modified duration of the bond, which helps you assess its interest rate risk.

MDURATION Tips & Tricks

• Remember to input the settlement and maturity dates in a format that Excel can recognize as a date, such as “MM/DD/YYYY” or by using the DATE function.
• When comparing bonds with different maturities and coupon rates, the modified duration can help you determine which bond is more sensitive to interest rate changes. A higher modified duration indicates a higher sensitivity to interest rate changes, and vice versa.
• Keep in mind that the MDURATION function assumes a par value of $100 for the security. If the bond has a different par value, you may need to adjust the coupon rate and yield accordingly.
• Use the optional [basis] argument to specify the day count basis if it differs from the default US (NASD) 30/360 day count basis.

Common Mistakes When Using MDURATION

• Using incorrect date formats for the settlement and maturity arguments. Make sure to use a format that Excel recognizes as a date.
• Forgetting to adjust the coupon rate and yield if the bond’s par value is different from $100.
• Not specifying the correct day count basis if it differs from the default US (NASD) 30/360 day count basis.
• Using the wrong frequency value for the number of coupon payments per year. Remember to use 1 for annual payments, 2 for semiannual payments, and 4 for quarterly payments.

Why Isn’t My MDURATION Working?

If your MDURATION formula isn’t working, consider the following troubleshooting steps:

• Check the settlement and maturity date formats to ensure they are recognized by Excel as dates.
• Verify that the coupon rate and yield are expressed as percentages, not decimals.
• Ensure that the frequency value is correct for the number of coupon payments per year.
• Double-check the optional [basis] argument to make sure it is set to the appropriate day count basis for your security.

MDURATION: Related Formulae

Here are some related formulae that you may find useful when working with the MDURATION function in Excel:

1. DURATION: Calculates the Macaulay duration of a security, which is a measure of the weighted average time until the bond’s cash flows are received.
2. YIELD: Calculates the yield of a security that pays periodic interest, such as a bond.
3. PRICE: Calculates the price of a security that pays periodic interest, based on the security’s yield.
4. COUPDAYS: Calculates the number of days in the coupon period that contains the settlement date.
5. COUPNCD: Calculates the next coupon date after the settlement date for a security that pays periodic interest.

By mastering the MDURATION formula and its related functions, you can effectively analyze and manage the interest rate risk of your bond portfolio in Excel.