Can you please solve with the equations on excel! Thank you!!

Consider the following coupon bond:

with maturity of N years, annual $CPN coupon payments, a face value of $FV, and a yield-to-maturity of y. Using the annuity formula, the price of this bond, P, is:

1. Based on the above formula, what is the fair market price for a 10-year, $1000 coupon bond with a 5% coupon rate, paid annually, and 4% yield-to-maturity? Create an input cell for each of N, y, and CPN in Excel. Calculate the bond price using both the above formula and the corresponding Excel function.

2. Create a column for yield-to-maturity (y) ranging from 0% to 30%. Calculate the bond price for each of the yield values in a new column. Graph bond prices vs. ys. Explain the pattern you observe.

3. As we saw in Chapter 2, interest rate risk is one of the key risks for fixed income securities. What is the graphical representation for price sensitivity (a.k.a duration) to changes in interest rate risk, y? [Hint: Pick any y level and using Excel drawing tools draw the tangency line to the curve to find the slope of the curve, dP/dy.]

4. Current Yield Effect: Create a new column for %Price = (1/p)(dP/dy)=(1/P1) (P2- P1)/(y2-y1) for each of two rows y1 and y2. Calculate the %Price for all the ys values and graph the %Price column vs. ys column. Explain the pattern you observe.

5. Compare %Price for a low yield rate, say 5%, to a high yield rate, say 20%. Which one has a higher interest rate risk or sensitivity? What is the intuition here?

6. Coupon Rate Effect: Create a second price-yield curve for a similar bond but with a higher coupon rate, say 20% vs. original 5%. Lets call this bond B and the original bond A. Repeat steps 2 and 4 for this bond to calculate %Price for each bond A and B. How does the interest rate risk change for an increase in coupon rate? What is the intuition here? [Hint: pick a y=y* and compare %PriceA vs. %PriceB at y*]

7. Maturity Effect: Repeat (6) for two similar bonds this time with the same coupon rates, 5%, but different maturities, say 10 and 30 years.

8. How do you explain the intersection between the two curves in part (7)? Explain the intuition for how bond prices and price sensitivities for bond A and B differ on each side of the intersection point for higher vs. lower yield rates. [Hint: Longer maturity creates a trade-off: you will receive more coupon payments but you will receive the principal at a later point of time in the future)

9. Moving to Maturity: For the bond in part (A), assuming a 4% yield-to-maturity, calculate and graph the transition in bond prices for years 0, 1, 2, 3... 10. That is, calculate the bond price for year 0 using all 10 future coupons and the principle, for year 1 using the remaining 9 coupons and the principle, for year 2 using the remaining 8 coupons and the principle, and so on. Repeat this exercise using a 6% yield-to-maturity. Explain the pattern you observe.

10. Extra Credit: Derive the mathematical formula for price sensitivity to interest rate for a zero coupon bond. [Hint: differentiate P with respect to y for the special case of CPN=0, to calculate dP/dy and then calculate (1/p)(dP/dy)]

P=PresentValueofalloftheperiodiccouponpaymentsCPNy1(1(1+y)N1)AnnuityFactorusingtheYTM(y)+PresentValueoftheFaceValuerepaymentusingtheYTM(y)(1+y)NFV