Question

I need help ASAP! please show work on excel

Question 6. You observe the Treasury yield curve below (all yields are shown on a bond equivalent basis): Year Spot Rate Forward Rate 10.00 Yield to Maturity 10.00% 9.75 9.50 9.25 9.75 9.00 8.75 8.50 8.25 8.00 7.75 7.50 7.25 9.48 9.22 8.95 8.68 8.41 8.14 7.86 7.58 7.30 7.02 6.74 6.46 6.18 5.90 6.75 6.50 6.25 6.00 5.75 5.50 5.25 9.0 9.5 10.0 All the securities maturing from 1.5 years on are selling at par. The 0.5 year and one-year securities are zero-coupon instruments. a. Calculate the missing spot rates b. Calculate the missing forward rates

Answer 1

For a zero-coupon bond with 6 months maturity, it will receive only one payment that will be equal to YTM that is 10.00%

We need to know to calculate forward rate we have to use the following formula

(1 + ft.r)' = (1 +4+r)*+r (1 + y)*

so for 1-year forward rate-(1+f1)=(1+0.0975)/(1+0.1)power0.5=1.046425,so r=1.046425-1=0.0446425=4.6425%

bond trading at par menas cupon=YTM

now i have used excel to calculate the rest based on same equation

Years Forward rate % 1.5 4.4039 2 4.1650 3.9258 3.6863 3.5 3.4466 3.2066 2.9663 2.7258 2.4850 2.2439 2.0025 1.7608 7.5 1.5189 1.2767 1.0342 0.7914 9.5 0.5484 0.3050 9

now in last, we have to find spot rates (1+s)power 2=(1+f1,1) power1*(1+s1)power1

so to calculate 9 year spot rate (1+s9)power9=(1+f0.5)power0.5*1+s8.5)power 8.5

i am using excel to solve

9yr-1.6804

9.5-1.2519

10-3.619