Duration can be calculated using spreadsheet formulas. Data must be entered as follows: Settlement date Date is
Question:
Duration can be calculated using spreadsheet formulas. Data must be entered as follows:
Settlement date Date is entered as DATE (year, month, day)
Maturity date Date is entered as DATE (year, month, day)
Coupon as a decimal required yield as a decimal Frequency of Payments
Use the formula = DURATION (Al,A2,A3,A4,A5) for duration and = MDURATION(Al,A2, A3,A4,A5) for modified duration. Example—calculates the duration and modified duration for a 6 percent, seven-year bond with a required yield of 5 percent. This is done as follows:
2/16/2006 ……………………….. Settlement date = DATE (year, m, d)
7116/2013 ……………………….. Maturity date = DATE (year, m, d)
0.06 ……………………………… coupon rate as decimal
0.05 ……………………………… required yield as decimal
2 …………………………………. frequency of coupons
5.86 ……………………………… Macaulay Duration
5.71 ……………………………… Modified Duration
Step by Step Answer: