Problem 6-23 Consider the bonds listed below. What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%? Note: Assume $100 face value. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Coupon Rate Maturity Bond (years) 15 D (annual) 0% 0% 4% 8% YTM (1) YTM (2) Face value 6% 5% $ 100 10 Price bond A (1) Price bond A (2) Change in price bond AL Price bond B (1) Price bond B (2) Change in price bond B Coupon bond C Price bond C (1) Price bond C (2) Change in price bond In den in Coupon bond D Price bond D (1) Price bond D (2) Change in price bond D quirements 1 In cell E13, by using cell references, calculate the price of bond A under YTM 1 (1 pt.). Note: (1) The output of the expression or function you typed in this cell is expected as a positive number. (2) Use 0 (zero) for the PMT argument of the PV function. 2 In cell E14, by using cell references, calculate the price of bond A under YTM 2 (1 pt.). Note: (1) The output of the expression or function you typed in this cell is expected as a positive number. (2) Use 0 (zero) for the PMT argument of the PV function. 3 In cell E15, by using cell references, calculate the percentage change in the price of bond A as the yield to maturity falls from YTM1 to YTM 2 (1 pt.). 4 In cell E17, by using cell references, calculate the price of bond B under YTM 1 (1 pt.). Note: (1) The output of the expression or function you typed in this cell is expected as a positive number. (2) Use 0 (zero) for the PMT argument of the PV function. 5 In cell E18, by using cell references, calculate the price of bond B under YTM 2 (1 pt.). Note: (1) The output of the expression or function you typed in this cell is expected as a positive number. (2) Use 0 (zero) for the PMT argument of the PV function. 6 In cell E19, by using cell references, calculate the percentage change in the price of bond B as the yield to maturity falls from YTM1 to YTM 2 (1 pt.). 7 In cell E21, by using cell references, calculate the coupon payment of bond C (1 pt.). In cell E22, by using cell references, calculate the price of bond C under YTM 1 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. In cell E23, by using cell references, calculate the price of bond C under YTM 2 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. In cell E24, by using cell references, calculate the percentage change in the price of bond C as the yield to maturity falls from YTM1 to YTM 2 (1 pt.). 11 In cell E26, by using cell references, calculate the coupon payment of bond D (1 pt.). 12 In cell E27, by using cell references, calculate the price of bond D under YTM 1 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 13 In cell E28, by using cell references, calculate the price of bond D under YTM 2 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. In cell E29, by using cell references, calculate the percentage change in the price of bond D as the yield to maturity falls from YTM1 to YTM 2 (1 pt.)