Suppose that the term structure of interest rates for the next five years is as given in the chart and table below

Use Excel to determine the swap rate for a five-year swap where payments are made every six months. In order to do this, you should follow the steps given below:

1. Calculate the continuously compounded forward rates for each six-month period in the five-year term structure.
2. Convert the continuously compounded forward rates to semi-annually compounded rates, and use these to find the floating-rate cash flows to be received at the end of each six-month period.
3. Assume that the fixed-rate cash flow to be paid is a constant percentage of the face value of some notional amount, and assume that all these payments will remain the same throughout the tenor of the swap.
4. Calculate the net cash flow for each payment period, and discount these using the appropriate continuously compounded zero rate.
5. Use Goal Seek or Solver in Excel to set the initial value of the swap to zero, by changing the swap rate in your spreadsheet.

(PLEASE SHOW THE FORMULA TEXT)

Zero Rates (cc) Time 0.0 0.5 1.0 1.5 2.0 2.5 3.0 2.50% 3.35% 3.90% 4.35% 4.70% 5.00% 5.25% 5.45% 5.60% 5.75% 3.5 4.0 4.5 5.0 Zero Rates (cc) Time 0.0 0.5 1.0 1.5 2.0 2.5 3.0 2.50% 3.35% 3.90% 4.35% 4.70% 5.00% 5.25% 5.45% 5.60% 5.75% 3.5 4.0 4.5 5.0