1. (Excel) Use the IRR function in Excel to calculate the YTM on the following two bonds. a. 10 year 7% Annual Coupon bond with a price of 103.75 b. 15 year 6% Annual Coupon bond with a price of 92.25 2. (Excel) Value the following bonds given the Treasury Spot Curve below: a. 5 year 3% Annual Coupon Treasury Bond b. 5 year 10% Annual Coupon Treasury Bond 10.1 = 4.00% 102 = 4.75% 103 = 5.20% 104 = 5.65% To5 = 5.95% 3. (Excel) Calculate the YTM on each of the two bonds in question #2. Comment on the effect of the Coupon Rate on the YTM. 4. (Excel) Using the Spot rates from #2 above, calculate the annual coupon rate needed on a five year bond so that the bond would have a price of par. ($100). 5. (Excel) Using each of the 5 year Bonds in #2 above, assume that spot rates remain the same for the next year (next year the one year spot rate is 4.00% ...) Calculate the expected return you would earn from "Carry Roll" on each bonds using this assumption if you held them for a one year period (Do not forget to include the coupon you receive in one year.) 10.1 = 4.00% 10.2 = 4.75% To3 = 5.20% 104 = 5.65%6. (Excel) Again using each of the 5 year bonds in #2 above calculate the actual realized return on each of the bonds assuming that next year spot rates have changed to the rates given below. (Again do not forget to include the coupon you receive in one year.) rm= 4.50% r03: 5.00% 1'03: 5.40% n14= 535% Compare the actual realized return in this question to both the YTMs you calculated in #3 and to the expected return 'om "Carry Roll" you calculated in #5. Briey explain what caused the differences in Returns