Please answer in a clear and easy to read manner using formulas within EXCEL. Thank you so much!

Problem 8.6 O'Reilly and CB Solutions Heather O'Reilly, the treasurer of CB Solutions, believes interest rates are going to rise, so she wants to swap her future floating rate interest payments for fixed rates. At present she is paying LIBOR + 2% per annum on $5,000,000 of debt for the next two years, with payments due semiannually. LIBOR is currently 4.00% per annum. Heather has just made an interest payment today, so the next payment is due six months from today Heather finds that she can swap her current floating rate payments for fixed payments of 7.00% per annum. (CB Solutions's weighted average cost of capital is 12%, which Heather calculates to be 6% per six month period, compounded semiannually). 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 a. If LIBOR rises at the rate of 50 basis points per six month period, starting tomorrow, how much does Heather save or cost her company by making this swap? b. If LIBOR falls at the rate of 25 basis points per six month period, starting tomorrow, how much does Heather save or cost her company by making this swap? s Assumptions Notional principal LIBOR, per annum Spread paid over LIBOR, per annum Swap rate, to pay fixed, per annum Values 5,000,000 4.000% 2.000% 7.000% Make sure that the swap is between LIBOR and 7% fixed, not between LIBOR+2% and 7%. First 6-months Second 6 months Third 6-months Fourth 6-months Interest & Swap Payments a. LIBOR increases 50 basis pts/6 months Expected LIBOR 0.500% 4.500% 5.000% Current loan agreement: Expected LIBOR (for 6 months) Spread (for 6 months) Expected interest payment -2.250% -1.000% -2.500% -1.000% -3.500% -3.250% 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 Swap Agreement: Pay fixed (for 6-months) Receive floating (LIBOR for 6 months) -3.500% -3.500% 2.500% 2.250% Net interest (loan + swap) ) -4.500% -4.500% s Swap savings? Net interest after swap Loan agreement interest Swap savings (swap cost) (225,000) (162,500) $ (225,000) (175,000) b. LIBOR decreases 25 basis pts/6 months Expected LIBOR -0.250% 3.750% 49 3.500% Current loan agreement: Expected LIBOR (for 6 months) Spread (for 6 months) Expected interest payment -1.875% -1.000% -2.875% -1.750% -1.000% -2.750% -3.500% 1.875% -3.500% 1.750% 50 51 52 02 53 54 55 56 57 58 FO 59 60 61 0 62 63 64 65 66 67 69 -4.500% -4.500% Swap Agreement: Pay fixed (for 6-months) Receive floating (LIBOR for 6 months) Make sure that the swap Net interest (loan + swap) is between LIBOR and 7% fixed, not between LIBOR+2% and 7%. Swap savings? Net interest after swap $ Loan agreement interest Swap savings (swap cost) (225,000) (143,750) (81,250) $ (225,000) (137,500) (87,500) In both cases Canon is suffering higher total interest costs as a result of the swap