a. Calculate the durations of the two bonds in Spreadsheet 11.1 if the market interest rate increases to 12%. (Do not round intermediate calculations. Round your answers to 4 decimal places.) year Duration of 8% coupon bond Duration of zero-coupon bond year b. Use Spreadsheet 11.1 to calculate the duration of the coupon bond if the coupon were 12% instead of 8%. The interest rate remains 12%. (Do not round intermediate calculations. Round your answer to 4 decimal places.) Duration of 12% coupon bond year SPREADSHEET 11.1 Calculation of the duration of two bonds using Excel spreadsheet x Spreadsheets are available in Connect Column (B) A B D 1 Interest rate: 10% 2 3 Time until Payment 4 Payment Discounted 5 (Years) Payment at 10% Weight 6 A. 8% coupon bond 1 80 72.727 0.0765 7 2. 80 66.116 0.0696 8 3 1080 811.420 0.8539 9 Sum: 950.263 1.0000 10 11 B. Zero-coupon bond 1 0 0.000 0.0000 12 2 0 0.000 0.0000 13 3 1000 751.315 1.0000 14 Sum: 751.315 1.0000 15 16 Welcht=Present value of each payment (column D divided by bond price Column (E) 0.0765 0.1392 2.56 17 2.7774 0.0000 0.0000 3.0000 3.0000 B D E F 0.1 Time until Payment (Years) 1 A 1 Interest rate: 2 3 4 5 6 A. 8% coupon bond 7 8 9 Sum: 10 11 B. Zero-coupon 12 13 14 Sum: Payment Discounted Payment at 10% 80 =C6/(1+$B$ 1)AB6 80/=C7/(1+$B$ 1)AB7 1080 C8/1-$B$ DAB8 =SUMD6:08) Column (B) Weight Column (E) =D6/D$9 =E6'B6 =D7/D$9 =E7B7 ED8/DS9 =E8"B8 =D9/D$9 =SUM(F6:F8) 2 3 1 Ol=C11/1+$B$1AB11 O=C12/1+$B$1AB12 1000)=C13/1+$B$1AB13 =SUMD11:D 13) =D11/D$ 14 =D12/D$ 14 =D13/D$ 14 =D14/D$ 14 =E11'811 =E12"B12 =E13'B13 =SUM/F11 F131 3