# A. Modify the spreadsheet 3.1 of "Duration Spreadsheet" to

A. Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the duration of
the 4-year 8% semiannual coupon bond, YTM=10%.
B. Verify your solution of part A) using spreadsheet 3.3. Hint: You can put any
settlement date and maturity date as long as the maturity matches the question.
C. Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the duration of
the 4-year 8% annual coupon bond, YTM=10%.
D. Verify your solution of part C) using spreadsheet 3.3.
E. Assume you will be paying $10,000 a year in tuition expenses at the end of the next
2 years (i.e. $10,000 at end of the year 1, $10,000 at end of the year 2). Market rate
is 8%. Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the
duration of your obligation? Hint: you can treat this obligation as a bond.
Time until
PV of CF
Column (C)
Payment
(Discount rate =
5% per period)
Period
(Years)
Cash flow
Weight*
Column (F)
A. 8% coupon bond
1
0.5
40
38.095
0.0395
0.0197
2
1.0
40
36.281
0.0376
0.0376
3
1.5
40
34.554
0.0358
0.0537
4
2.0
1040
855.611
0.8871
1.7741
Sum:
964.540
1.0000
1.8852
Semiannual int rate:
0.05
*Weight = Present value of each payment (Column E) divided by the bond price
Time until
PV of CF
Column (C)
Payment
(Discount rate =
Period
(Years)
Cash flow
5% per period)
Weight*
Column (F)
A. 8% coupon bond
1
0.5
40
=D4/(1+SBS16)^B4
=E4/ES8
-F4*C4
1
40
=D5/(1+SBS16)^B5
=E5/ES8
-F5*C5
=D6/(1+SBS16)^B6
=D7/(1+SBS16)^B7
3
1.5
40
=E6/E$8
-F6*C6
1040
=E7/ES8
-F7*C7
Sum:
=SUM(E4:E7)
=SUM(F4:F7)
-SUM(G4:G7)
Semiannual int rate:
0.05