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.
Using the MDURATION function:
|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.|
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.|