a. Consider two bonds A and B with payments C,, where t = 1,2, ...,10. Bond A has just been issued. Its face value is $1,000, it bears coupon rate of 7%, and it will mature in 10 years. Bond B was issued 5 years ago. This bond has $1,000 face value and bears a 13% coupon rate. When issued, this bond had a 15-year maturity, so its remaining maturity is 10 years. The yield to maturity for both bonds is 8% (see Cell B2 in the spreadsheet below). Using the Excel spreadsheet below write and explain the Excel formula used to calculate bond A and bond B prices in Cells B17 and E17, respectively: A Yield to Maturity 2 (YTM) 3 4 Year 5 1 6 2 if 3 8 4 9 5 10 6 11 7 12 8 13 9 14 10 15 16 17 Bond price 8% Cia 70 70 70 70 70 70 70 70 70 1,070 932.90 ( D E F BOND DURATION CALCULATION Time-Weighted Average Maturity of the Payments Received From the Bond A 0.0695 0.1287 0.1787 0.2206 0.2553 0.2837 0.3065 0.3243 0.3378 5.3127 130 130 130 130 130 130 130 130 130 1,130 1,335.50 Time-Weighted Average Maturity of the Payments Received From the Bond B 0.0901 0.1669 0.2318 0.2862 0.3312 0.3681 0.3976 0.4207 0.4383 3.9192 Columns C and F in the Excel spreadsheet below report the time-weighted average maturity of the payments received from the Bond A and B, respectively. Write and explain the Excel formula used to calculate the values in Cells C5:C6 and F5:F6, respectively. Interpret those numbers. A B c D E F G 1 BOND DURATION CALCULATION Yield to Maturity 2 (YTM) 8% 4 Time-Weighted Time-Weighted Average Maturity of Average Maturity of the Payments the Payments Received From the Received From the 4 Year Cia Bond A Cig Bond B 5 1 70 0.0695 130 0.0901 ? 6 2 70 0.1287 130 0.1669 ? i 3 70 0.1787 130 0.2318 8 | 4 70 0.2206 130 0.2862 9 | 5 70 0.2553 130 0.3312 10 6 70 0.2837 130 0.3681 11 7 70 0.3065 130 0.3976 12 8 70 0.3243 130 0.4207 13 9 70 0.3378 130 0.4383 14 10 1,070 5.3127 1,130 3.9192 15 16 17 |Bond price 932.90 1,335.50 ii. Using the Excel spreadsheet below estimate the duration of each of the two bonds A (Cell B20) and B (Cell E20) using the Macaulay duration measure. Show your calculations. Which bond has the longest duration? A B C D E F 1 BOND DURATION CALCULATION Yield to Maturity 2 YTM) 8% 3 Time-Weighted Time-Weighted Average Maturity of Average Maturity of the Payments the Payments Received From the Received From the 4 Year Cia Bond A Cis Bond B 5 1 70 0.0695 130 0.0901 6 2 70 0.1287 130 0.1669 7 3 70 0.1787 130 0.2318 8 4 70 0.2206 130 0.2862 9 5 70 0.2553 130 0.3312 10 6 70 0.2837 130 0.3681 11 7 70 0.3065 130 0.3976 12 8 70 0.3243 130 0.4207 13 9 70 0.3378 130 0.4383 14 10 1,070 5.3127 1,130 3.9192 15 16 17 Bond price 932.90 1,335.50 18 19 Estimation ofthe Macaulay duration using the mathematical formula 20 Duration