Manuals/calci/MDURATION
MDURATION(SettlementDate, MaturityDate, Coupon,Yield, Frequency, Basis)
where,
SettlementDate - represents the settlement date.
MaturityDate - represents the maturity date.
Coupon - the annual coupon rate.
Yield - the annual yield.
Frequency - the number of coupon payment per year.
Payments frequency
annual 1
semiannual 2
quarterly 4
Basis - type of day count basis
Basis Day count basis
0 or omitted US 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360
It returns the Macauley duration for a security with an assumed par value of $100.
Formula:-
MDURATION = duration /(1+(Market yield/coupon payemnt per year))
If Basis < 0 or Basis > 4 , MDURATION returns the #ERROR.
If Yield < 0 or Coupon < 0, it returns the #ERROR.
If SettlementDate >= MaturityDate, MDURATION returns #ERRROR.
If Frequency other than 0 , 1 or 4 returns the #ERROR.
MDURATION
Lets see an example in (Column2, Row1)
=MDURATION(R1C1, R2C1, R3C1, R4C1, R5C1,R6C1)
MDURATION returns 0.726144.
Consider another example
=MDURATON(date(1/1/2007),date(2/2/2008),"5%","4%",5,1)
It returns #ERROR.
| Column1 | Column2 | Column3 | Column4 | |
| Row1 | 2/2/2008 | 0.726144 | ||
| Row2 | 11/2/2008 | |||
| Row3 | 5% | |||
| Row4 | 8% | |||
| Row5 | 4 | |||
| Row6 | 1 |
