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 8.0%, and the bond pays coupons semiannually. The bond is selling at a bond-equivalent yield to maturity of 9.0%. Use Spreadsheet 16.2. (Do not round intermediate calculations. Round your answers to 4 decimal places.) Macaulay duration Modified duration B F 1 N D E Time until PV of CF Payment (Discount rate - (Years) Cash Flow 5% per period) 0.5 =D4/(1+$B$16)"B4 1 40 =D5/(1+$B$16) B5 1.5 =D6/(1+$B$16) B6 2 1040 =D7/(1+$B$16) B7 = SUM(E4:57) Period 1 2 3 4 40 Column (C) times Column (F) =F4C4 =F5'C5 =F6'C6 =F7'07 SUM(G4G7) Weight =E4/E$8 =E5/E$8 -E6/E$8 =E7/E$8 SUM(F4:F7) 40 3 4 A. 8% coupon bond 5 6 7 8 Sum: 9 10 B. Zero-coupon 11 12 13 14 Sum: 15 16 Semiannual Int rate: 1 2 3 4 0.5 1 1.5 2 OOO =D10/(1+$B$ 16/ B 10 =E10/E$14 =F10'C10 =D11/(1+$B$ 16/B11=E11/E$14 =F11'C11 =D12/(1+$B$ 16, B12=E12/E$14 =F12C 12 =D13/(1+$B$16/B13 =E13/E$14 =F13'C13 =SUMIE 10:E13) =SUM(F10:F13) =SUM(G10:13) 1000 0.05 Spreadsheet 16.2 Spreadsheet formulas for calculating duration