The MDURATION function returns the modified Macauley duration for a security with an assumed par value of $100.
Note: 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.
|settlement||The security’s settlement date – the date after the issue date when the security is traded to the buyer|
|maturity||The security’s maturity date – the date when the security expires|
|coupon||The security’s annual coupon rate|
|yld||The security’s annual yield|
|frequency||The number of coupon payments per year. Must be one of the following:
|[basis]||Optional. Defines the day count basis to be used in the calculation
The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page
Note: The settlement and maturity dates should be input as either:
- References to cells containing dates, or
- Dates returned from formulas
If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.
Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.
|10||=MDURATION(A2,A3,A4,A5,A6,A7)||5.736||The modified duration, for the bond with the terms specified above|
Common Function Error(s)
|Problem||What went wrong|
|#VALUE!||Occurs if either:
|#NUM!||Occurs if either:
|#NAME?||Occurs when Analysis ToolPak add-in is not enabled|
Modified duration is a measure of the price sensitivity of a bond to interest rate movements. It is calculated as shown below: