Find the duration of a bond with a settlement date of May 27, 2023, and maturity date November 15, 2034. The coupon rate of the bond is 5.0%, and the bond pays coupons semiannually. The bond is selling at a bond-equivalent yield to maturity of 6.0%. Use Spreadsheet 16.2 (Do not round intermediate calculations. Round your answers to 4 decimal places.) Macaulay duration Modified duration A B F G 1 2 3 4 5 D Time until Payment (Years) Cash Flow 0.5 40 1 40 1.5 40 2 1040 Period 1 2 3 A. 8% coupon bond E PV of CF (Discount rate = 5% per period) =D4/(1+$B$16) B4 =D5/(1+$B$16)^B5 =D6/(1+$B$16)^B6 =D7/(1+$B$16)^B7 =SUM(E4:E7) Weight =E4/E$8 =E5/E$8 =E6/E$8 =E7/E$8 =SUM(F4:F7) Column (C) times Column (F) =F4C4 =F5*C5 =F6*C6 =F7*C7 =SUM(G4:47) 6 7 4 8 Sum: 0 9 10 B. Zero-coupon 11 12 1 2 3 4 0.5 1 1.5 2 0 o 1000 =D10/(1+$B$16)^B10 =E10/E$14 =F10*C10 =D11/(1+$B$16) B11=E1 1/E$14 =F11'C11 =D12/(1+$B$16) B12 =E12/E$14 =F12 C12 =D13/(1+$B$16) B13 =E13/E$14 =F13*C13 =SUM(E 10:E13) =SUM(F10:F13) =SUM(G10:13) 13 14 Sum: 15 16 Semiannual int rate: 0.05 Spreadsheet 16.2 Spreadsheet formulas for calculating duration Find the duration of a bond with a settlement date of May 27, 2023, and maturity date November 15, 2034. The coupon rate of the bond is 5.0%, and the bond pays coupons semiannually. The bond is selling at a bond-equivalent yield to maturity of 6.0%. Use Spreadsheet 16.2 (Do not round intermediate calculations. Round your answers to 4 decimal places.) Macaulay duration Modified duration A B F G 1 2 3 4 5 D Time until Payment (Years) Cash Flow 0.5 40 1 40 1.5 40 2 1040 Period 1 2 3 A. 8% coupon bond E PV of CF (Discount rate = 5% per period) =D4/(1+$B$16) B4 =D5/(1+$B$16)^B5 =D6/(1+$B$16)^B6 =D7/(1+$B$16)^B7 =SUM(E4:E7) Weight =E4/E$8 =E5/E$8 =E6/E$8 =E7/E$8 =SUM(F4:F7) Column (C) times Column (F) =F4C4 =F5*C5 =F6*C6 =F7*C7 =SUM(G4:47) 6 7 4 8 Sum: 0 9 10 B. Zero-coupon 11 12 1 2 3 4 0.5 1 1.5 2 0 o 1000 =D10/(1+$B$16)^B10 =E10/E$14 =F10*C10 =D11/(1+$B$16) B11=E1 1/E$14 =F11'C11 =D12/(1+$B$16) B12 =E12/E$14 =F12 C12 =D13/(1+$B$16) B13 =E13/E$14 =F13*C13 =SUM(E 10:E13) =SUM(F10:F13) =SUM(G10:13) 13 14 Sum: 15 16 Semiannual int rate: 0.05 Spreadsheet 16.2 Spreadsheet formulas for calculating duration