$\backslash$table$[[\backslash$table$[[$Swap$],[$Rate$]],,,,,],[$Quarter$,\backslash$table$[[$Day Count$],[($actual$],[$number$],[$of days in$],[$Qtr$)]],\backslash$table$[[$Futures$],[$Price$],[($maturing$],[$at end of$],[$Quarter$)]],\backslash$table$[[$Forward$],[$Rate used$],[$to calculate$],[$Floating CF$],[$for the$],[$period$]],\backslash$table$[[$Expected$],[$Floating$],[$Cash Flow$]],\backslash$table$[[$Fixed Cash$],[$Flow$]],\backslash$table$[[$PV of$],[$Expected$],[$Floating$],[$Cash Flow$]],\backslash$table$[[$PV of$],[$Fixed$],[$Cash Flow$]]],[1,90,95\mathrm{.}5,,,,,],[2,91,94\mathrm{.}75,,,,,],[3,90,94,,,,,],[4,91,94\mathrm{.}5,,,,,]]$

a$.$ 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\mathrm{.}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$).$ Cut and paste a table like the one above into your homework document and Email me your excel spreadsheet showing how you set up the problem, so if there is a mistake I can help you understand what went wrong. $($ you will be required to do parts of this on the next test by hand$)(10$ points$)$

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. $(5$points$)$

$\backslash$table$[[,\backslash$table$[[$Day Count$],[($actual$],[$number of$],[$days in$],[$Qtr$)]],\backslash$table$[[$Futures Price$],[($maturing at$],[$end of$],[$Quarter$)]],\backslash$table$[[$Expected$],[$Floating$],[$Cash Flows$]],\backslash$table$[[$Fixed Cash$],[$Flows$]],\backslash$table$[[$PV of$],[$Expected$],[$Floating$],[$Cash Flows$]],\backslash$table$[[$PV of Fixed$],[$Cash Flows$]]],[2,91,95\mathrm{.}4,,,,],[3,90,94\mathrm{.}9,,,,],[4,91,97\mathrm{.}5,,,,]]$