The MDURATION function discussion should start with the DURATION function, which returns the annual duration of a security with periodic interest payments. This is also known as the Macaulay duration and is calculated for an assumed par value of $100.

MDURATION takes 5 required arguments and 1 optional argument:

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

DURATION and the MDURATION functions are closely related, so we'll address both functions herein.

First, we know that the maturity of a bond is simply the amount of time until it matures. And we also know that the price of a bond changes more dramatically with interest rates the longer the maturity of the bond is. But maturity isn't a great measure is because it does not account for the differences in bond coupons. For example, a 10-year bond with a 4% coupon is more sensitive to interest rate changes than a 10-year bond with an 7% coupon. Duration and Excel's DURATION take both maturity and coupon rate into account in order to compare securities with different coupons.

Duration is defined as the weighted average of the present value of the cash flows and is used as a measure of a bond price's response to changes in yield. The longer the duration, the longer is the average maturity, and thus, the greater the sensitivity to interest rate changes. Duration allows bonds of different maturities and coupon rates to be compared directly.

Modified duration, Excel's MDURATION, calculates the duration if the yield were to increase 1%. A bond's price volatility can be assessed by comparing DURATION to MDURATION for the same bond.

#1)
Using the MDURATION function:
The MDURATION function for the same bond:
#2)
The arguments for the MDURATION function are:
Argument Required? Description
settlement Required The security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
maturity Required The security's maturity date. The maturity date is the date when the security expires.
coupon Required The security's annual coupon rate.
yld Required The security's annual yield.
frequency Required The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
basis Optional The type of day count basis to use.
Possible basis argument values:
#3)
A few more things:
Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2014 is serial number 41640 because it is 41,640 days after January 1, 1900.
settlement, maturity, frequency, and basis are truncated to integers.
If settlement, or maturity is not a valid date, MDURATION returns the #VALUE! error value.
If coupon, < 0 or if yld < 0, MDURATION returns the #NUM! error value.
If frequency, is any number other than 1, 2, or 4, MDURATION returns the #NUM! error value.
If basis, < 0 or if basis, > 4, MDURATION returns the #NUM! error value.
If settlement, ≥ maturity, MDURATION returns the #NUM! error value.

Summary

The MDURATION is similar to the DURATION function in that it returns the annual duration of a security with periodic interest payments, but it calculates the duration if the yield were to increase 1%. This is also known as the Macaulay duration and is calculated for an assumed par value of $100. 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.
comments powered by Disqus