logo-v-light

Our Service

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:

    2. =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:

    2. =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.

Related

Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications. 
 
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.
Learn More