PLEASE POST IN EXCEL ALL PARTS (using formulas) Consider a Merck 5.75% coupon bond, which has semiannual payments and matures on todays date in 2036. The bonds current price is 120.43. a. List the bonds expected coupon and principal payments, and calculate the bonds yield to maturity (YTM) using IRR in cell A1. Do not use the Excel yield-related functions, except if you wish to check your numbers. b. Calculate the Macaulay duration for the bond (in A2). Do not use the Excel duration-related functions, except if you wish to check your numbers. c. Assume that the investor holds the bond to maturity. In the designated cells, compute the horizon return for the bond, under the following reinvestment rates which are observed immediately after purchase. Reinvestment Rate 3% 7% Horizon return A3 A4 d. Now assume that the investor holds the bond to Macaulay duration. In the designated cells, compute horizon return for the bond, under the following reinvestment rates. Reinvestment Rate 3% 7% Horizon return A5 A6 e. In no more than one paragraph inside a text box, describe what is happening to the investors horizon return in part (c). Be specific about why the numbers are as they are. f. In a second paragraph in the same text box, describe what is happening to the horizon returns in part (d). Be specific about why the numbers are as they are.

3. Answer this question in a sheet that you name Duration. Consider a Merck 5.75% coupon bond, which has semiannual payments and matures on today's date in 2036. The bond's current price is 120.43. a. List the bond's expected coupon and principal payments, and calculate the bond's yield to maturity (YTM) using IRR in cell A1. Do not use the Excel yield-related functions, except if you wish to check your numbers. b. Calculate the Macaulay duration for the bond (in A2). Do not use the Excel duration- related functions, except if you wish to check your numbers. c. Assume that the investor holds the bond to maturity. In the designated cells, compute the horizon return for the bond, under the following reinvestment rates which are observed immediately after purchase. Reinvestment Rate 3% 7% Horizon return A3 A4 d. Now assume that the investor holds the bond to Macaulay duration. In the designated cells, compute horizon return for the bond, under the following reinvestment rates. Reinvestment Rate 3% 7% Horizon return A5 A6 e. In no more than one paragraph inside a text box, describe what is happening to the investor's horizon return in part (C). Be specific about why the numbers are as they are. f. In a second paragraph in the same text box, describe what is happening to the horizon returns in part (d). Be specific about why the numbers are as they are. 3. Answer this question in a sheet that you name Duration. Consider a Merck 5.75% coupon bond, which has semiannual payments and matures on today's date in 2036. The bond's current price is 120.43. a. List the bond's expected coupon and principal payments, and calculate the bond's yield to maturity (YTM) using IRR in cell A1. Do not use the Excel yield-related functions, except if you wish to check your numbers. b. Calculate the Macaulay duration for the bond (in A2). Do not use the Excel duration- related functions, except if you wish to check your numbers. c. Assume that the investor holds the bond to maturity. In the designated cells, compute the horizon return for the bond, under the following reinvestment rates which are observed immediately after purchase. Reinvestment Rate 3% 7% Horizon return A3 A4 d. Now assume that the investor holds the bond to Macaulay duration. In the designated cells, compute horizon return for the bond, under the following reinvestment rates. Reinvestment Rate 3% 7% Horizon return A5 A6 e. In no more than one paragraph inside a text box, describe what is happening to the investor's horizon return in part (C). Be specific about why the numbers are as they are. f. In a second paragraph in the same text box, describe what is happening to the horizon returns in part (d). Be specific about why the numbers are as they are