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 = Period (Years) Cash flow 5% per period) 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 =D5/(1+SBS16)^B5 =D6/(1+SBS16)^B6 =D7/(1+SBS16)^B7 1 40 =E5/ES8 -F5*C5 3 1.5 40 =E6/ES8 -F6*C6 1040 =E7/ES8 -F7*C7 Sum: =SUM(E4:E7) =SUM(F4:F7) -SUM(G4:G7) Semiannual int rate: 0.05