Show work in Excel

Use the following information related to swaps to answer the questions that follow.

Swap Rate:
Quarter	Day Count (actual number of days in Qtr)	Futures Price (maturing at end of Quarter)	Forward Rate used to calculate Floating CF for the period	Expected Floating Cash Flow	Fixed Cash Flow	PV of Expected Floating Cash Flow	PV of Fixed Cash Flow
1	90	95.5
2	91	94.75
3	90	94
4	91	94.5

a. Find Swap Rate. Use the solver function in excel to complete the table above (fill in all the shaded cells) for a one year fixed for floating swap that makes quarterly payments with a notional value of $75,000,000. Assume that all payments are calculated using a (Actual/360) basis and that the current 3 mo LIBOR rate is 3.4%. (to complete this table you need to: 1) estimate the floating payments, 2) calculate the fixed payments based on an assumed swap rate 3) calculate the PV of each floating and fixed CF 4) use solver in excel to calculate the swap rate (the rate that would make the PV of the fixed CFs and Floating CFs equal).

b. Assume that at the end of the first quarter the futures prices have changed to those shown in the table below and the current LIBOR rate is 3%. Estimate new floating cash flows and then find the value of the swap for the party paying the fixed cash flows.

	Day Count (actual number of days in Qtr)	Futures Price (maturing at end of Quarter)	Expected Floating Cash Flows	Fixed Cash Flows	PV of Expected Floating Cash Flows	PV of Fixed Cash Flows
2	91	95.4
3	90	94.9
4	91	97.5