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

Returns the modified duration for a security that pays interest periodically.

settlementThe settlement date of the security.
maturityThe maturity date of the security.
couponThe annual coupon rate of the security.
yldThe annual yield of the security.
frequencyThe number of coupon payments per year:
1 = annual
2 = semi annual
4 = quarterly
basis(Optional) The type of day counting to use:
0 = US 30/360 (default)
1 = Actual/Actual
2 = Actual/360
3 = Actual/365
4 = European 30/360

Remarks

* This function assumes the par value is $100.
* This function calculates the annual modified duration for a security with regular interest payments adjusted for markey yield per number of coupon payments a year.
* Duration is the weighted average of the coupon payment cash flows and is a measure of the bond's interest rate risk and MDuration divides the duration by (1 + yield/frequency).
* Dates must be entered as text strings with quotation marks or as serial numbers.
* The "settlement" date is the date a buyer purchases a coupon, such as a bond.
* If "settlement" is not a valid date, then #NUM! is returned.
* If "settlement" >= "maturity", then #NUM! is returned.
* The "maturity" is the date when the security expires.
* If "maturity" is not an integer, it is truncated.
* If "maturity" is not a valid date, then #NUM! is returned.
* If "yld" < 0, then #NUM! is returned.
* If "coupon" < 0, then #NUM! is returned.
* If "frequency" is not an integer, it is truncated.
* If "frequency" is any number other than 1, 2, or 4, then #NUM! is returned.
* If "basis" is left blank, then 0 is used.
* If "basis" is not an integer, it is truncated.
* If "basis" < 0, then #NUM! is returned.
* If "basis" > 4, then #NUM! is returned.
* In Excel 2007 the accuracy of this function was improved.
* In Excel 2003 this function was only available when you had the Analysis ToolPak add-in loaded.
* For the Microsoft documentation refer to support.office.com

 A
1=MDURATION("1/1/1999","1/1/2007",0.08,0.09,2,1) = 5.736
2=MDURATION("1/1/1999","1/1/2007",0.08,0.09,2,2) = 5.736
3=MDURATION("1/1/1999","1/1/2007",0.08,0.09,2,3) = £5.74
4=MDURATION("1/1/1999","1/1/2007",0.12,0.15,2) = £4.78
5=MDURATION("1/1/1999","1/1/2007",0.08,0.09,2,-1) = #NUM!
6=MDURATION("1/1/1999","1/1/2007",0.08,0.09,2,5) = #NUM!


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top