PLEASE PROVIDE ANSWER IN EXEL PROGRAM

Problem. Consider a semi-annual coupon bond. Its face value is $1,000, it bears a 6 percent coupon rate per year and will mature in 2 years. Maturity Spot Rates Forward Rates 0.5 0.9 1.0 1.3 = f1= f(0,0.5) f2 = f(0.5,1) f3 = f(1,1.5) f4 = f(1.5, 2) 1.5 1.8 2.0 2.3 = 1) Compute the bond price using the sport rates. The bond equation is: 4 p=t=1 -E- ct (1+2+) where Zt represent a zero rate at time t and Ct is a cash flow at time t. 2) Calculate the respective implied forward rates from the corresponding spot rates. 3) Calculate the bond price using the implied forward rates using the following equation. C2 C C4 P= + + (1+f) . 1 (1 +f1)(1+f2) '(1+f;)(1+f2)(1+f3)(1+f1)(1+f2)(1+f3)(1+fd) 4) Compare all the bond prices and provide findings. Problem. Consider a semi-annual coupon bond. Its face value is $1,000, it bears a 6 percent coupon rate per year and will mature in 2 years. Maturity Spot Rates Forward Rates 0.5 0.9 1.0 1.3 = f1= f(0,0.5) f2 = f(0.5,1) f3 = f(1,1.5) f4 = f(1.5, 2) 1.5 1.8 2.0 2.3 = 1) Compute the bond price using the sport rates. The bond equation is: 4 p=t=1 -E- ct (1+2+) where Zt represent a zero rate at time t and Ct is a cash flow at time t. 2) Calculate the respective implied forward rates from the corresponding spot rates. 3) Calculate the bond price using the implied forward rates using the following equation. C2 C C4 P= + + (1+f) . 1 (1 +f1)(1+f2) '(1+f;)(1+f2)(1+f3)(1+f1)(1+f2)(1+f3)(1+fd) 4) Compare all the bond prices and provide findings