A M N o P Q R S U 1 2 Requirements Consider the following bonds: 8 3 a 3 10 10 11 12 13 Bond 4 B c D Coupon Rate (annual payments) 0% 0% 4% 8% Maturity (years) 15 10 15 10 1. Start Excel - completed. 2. In cell F23, by using cell references, calculate the annual coupon 3. To calculate the annual coupon payment of bonds B thru D, copy 4. In cell G23, by using cell references and the function PV, calculate Note: The output of the expression or function you typed in this 3. To calculate the price of bonds B thru D before the fall in the yield 6. In cell H23, by using cell references and the function PV, calculate Note: The output of the expression or function you typed in this 7. To calculate the price of bonds B thru Dafter the fall in the yield to 8. In cell 123, by using cell references, the percentage change in Note: Use cell references to the price of bonds 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 Note: Use cell references to the price of bonds before and after the '10. In J23, by using cell references and the function RANKEQ, rank the Note: The function RANK.EQ returns the rank of a number in a list '11. To rank the percentage change in price due to the drop in the yield to '12. In cell D30, find the bond that is most sensitive to changes in the 13. In cell D31, find the bond that is least sensitive to changes in the 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? before and and after the 14 15 ta 16 17 18 19 20 21 Par value Old YTM New YTM $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 Old Price New Price Change Rank Bond A B 22 22 23 24 25 26 27 Coupon Rate 0% 0% 4% 8% Maturity 15 10 15 10 D 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? 28 29 30 31 32 Bond Bond is the most sensitive to changes in bond yields. is the least sensitive to changes in bond yields