What is the MDURATION Function?
The MDURATION Function is categorized under Financial functions. It will calculate the duration of a security using the Modified Macauley method.
In financial analysis, we often evaluate a bond and develop a strategy that will match the duration of the assets and liabilities. It is done to minimize the impact of interest rates on the net worth of business. The MDURATION function helps us formulate a similar strategy.
The Modified Duration is defined as follows:
MDURATION is very similar to the DURATION function. The MDURATION function returns the annual duration of a security with periodic interest payments, but it calculates the duration if the yield were to increase 1%.
The Macaulay duration is calculated for an assumed par value of $100. Both duration and modified duration allow bonds of different maturities and coupon rates to be compared directly. It can be useful to use DURATION in conjunction with MDURATION.
=MDURATION(settlement, maturity, coupon, yld, frequency, [basis])
The MDURATION function uses the following arguments:
- Settlement (required argument) – It is the settlement date of the security after the issue date when the security is traded to the buyer.
- Maturity (required argument) – It is the maturity date of the security or the date when security expires.
- Coupon (required argument) – It is the annual coupon rate.
- Yld (required argument) – It is the annual yield of the security.
- Frequency (required argument) – It is the number of coupon payments per year. The frequency is 1 for annual payments, 2 for semi-annual and 4 for quarterly payments.
- Basis (optional argument) – It is the type of day count basis to use.
|Basis||Day Count basis|
|0 or omitted||US(NASD) 30/360|
How to use the MDURATION Function in Excel?
To understand the uses of the MDURATION function, let’s consider an example:
Let’s assume that a bond is priced at $10,000, pays a 5% coupon and matures in three years. The interest rate is at 5%. The bond pays the coupon twice a year and pays the principal on the final payment.
The formula to use will be:
We get the result below:
Here, we omitted the basis argument, so the function used the default value 0 (denoting the US (NADS) 30/360 day count basis).
Few notes about the MDURATION Function
- Remember that Excel stores date in sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2017 is serial number 42736 because it is 42,736 days after January 1, 1900.
- #NUM! error – Occurs when:
- The given settlement date is greater than or less than the maturity date.
- If coupon, yld, frequency or basis arguments given are invalid numbers.
- The MDURATION function will truncate the decimal in settlement, maturity, frequency or basis to integers.
- The date arguments should be supplied to the function as either:
- References to cells containing dates; or
- Dates returned from formulas.
- #VALUE! error – Occurs when:
- Any of the given argument is non-numeric.
- When any or one of the given settlement or maturity dates are invalid MS Excel dates.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources: