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. Consider the following bonds: b. Bond A B D Par value Old YTM New YTM F a. What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%? b. Which of the bonds A-D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive? $1,000 6% 5% a. What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%? Annual Coupon Bond A B D Coupon Rate (annual payments) 0% 0% 4% 8% Bond Bond Coupon Rate 0% 0% 4% 8% Maturity 15 Maturity (years) 15 10 15 10 10 15 10 Old Price New Price Change Rank Which of the bonds A-D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive? Iis the most sensitive to changes in bond yields. is the least sensitive to changes in bond yields. Requirements 1. Start Excel - completed. 2. In cell F23, by using cell references, calculate the annual coupon payment of bond A. 3. To calculate the annual coupon payment of bonds B thru D, copy cell F23 and paste it onto cells F24:F26. 4. In cell G23, by using cell references and the function PV, calculate the price of bond A before the fall in the yield to maturity. Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell reference to the annual coupon payment from Step 2 in your calculations. 5. To calculate the price of bonds B thru D before the fall in the yield to maturity, copy cell G23 and paste it onto cells G24:G26. 6. In cell H23, by using cell references and the function PV, calculate the price of bond A after the fall in the yield to maturity. Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell reference to the annual coupon payment from Step 2 in your calculations. 7. To calculate the price of bonds B thru D after the fall in the yield to maturity, copy cell H23 and paste it onto cells H24:H26. 8. In cell 123, by using cell references, calculate the percentage change in price due to the drop in the yield to maturity of bond A. Note: Use cell references to the price of bonds before and after the fall in the yield to maturity from Steps 4 and 6 in your calculations. 9. To calculate the percentage change in price due to the drop in the yield to maturity of bonds B thru D, copy cell 123 and paste it onto cells 124:126 Note: Use cell references to the price of bonds before and after the fall in the yield to maturity from Steps 5 and 7 in your calculations. 10. In J23, by using cell references and the function RANK.EQ, rank the change in price due to the drop in the yield to maturity of bond A. Note: The function RANK.EQ returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. 11. To rank the percentage change in price due to the drop in the yield to maturity of bonds B thru D, copy cell J23 and paste it onto cell J24:J26. 12. In cell D30, find the bond that is most sensitive to changes in the bond yields. Select A, B, C, or D.. 13. In cell D31, find the bond that is least sensitive to changes in the bond yields. Select A, B, C, or D.. 14. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.