In: Finance
Calculate the Macaulay duration for a nine year, 5.75% semiannual coupon bond priced to yield 4%.
Can someone calculate this using Excel? Please show the variables.
Maculay duration in excel has been demonstrated below
Coupon = | 5.75%*1000/2 | 28.75 | |||
Semiannual YTM | 2% | ||||
Time(n) | Cash flow | PV of Cash flow=(Cash flow)/(1+4%/2)^n | PV*Time | ||
1 | 28.75 | 28.19 | 28.1862745 | ||
2 | 28.75 | 27.63 | 55.2672049 | ||
3 | 28.75 | 27.09 | 81.2753014 | ||
4 | 28.75 | 26.56 | 106.242224 | ||
5 | 28.75 | 26.04 | 130.198804 | ||
6 | 28.75 | 25.53 | 153.175063 | ||
7 | 28.75 | 25.03 | 175.200236 | ||
8 | 28.75 | 24.54 | 196.302785 | ||
9 | 28.75 | 24.06 | 216.510425 | ||
10 | 28.75 | 23.59 | 235.850136 | ||
11 | 28.75 | 23.12 | 254.348186 | ||
12 | 28.75 | 22.67 | 272.030146 | ||
13 | 28.75 | 22.22 | 288.920906 | ||
14 | 28.75 | 21.79 | 305.044697 | ||
15 | 28.75 | 21.36 | 320.425102 | ||
16 | 28.75 | 20.94 | 335.085074 | ||
17 | 28.75 | 20.53 | 349.046952 | ||
18 | 1028.75 | 720.29 | 12965.2012 | ||
Total | 1131.1803 | 16468.3107 | |||
Maculay Duration | 7.2793 | (=16468.3107/1131.1803)/2 | |||
Modified Duration | 7.1365 | (=Maculay Duration/(1+YTM/2) |
Please Discuss in case of Doubt
Best of Luck. God Bless
Please Rate Well