Problem #7 (Excel Required)

Pretend that today is January 1, 2004: The University of Alabama is anticipating significant capital expenditures over the next 10 years and decides to enter into an interest rate swap in order to hedge its future borrowing costs. At the time that UA enters into the swap (January 1, 2004) the 10 year swap rate (fixed rate) is 5.29% and 3-month Libor is 1.132%. As per the swap agreement, UA agrees to pay the fixed rate of interest and receive the floating rate. Payments are on a notional amount of $500 million and are scheduled to be paid every six months.

Using the below schedule (for changes in interest rates) construct an excel spreadsheet that details the value of the swap for UA each quarter (Remember, payments are only made every 6 months!). Also include a graph of the value for the time period from January 1, 2004 to July 1, 2011.

Interest rate schedule for problem #7

Date	Fixed	Libor
1/1/2004	5.29	1.132
4/1/2004	5.47	1.1764
7/1/2004	5.58	1.6945
10/1/2004	5.20	2.1582
1/1/2005	5.12	2.7439
4/1/2005	5.14	3.2107
7/1/2005	4.87	3.6948
10/1/2005	5.15	4.2523
1/1/2006	5.08	4.6795
4/1/2006	5.65	5.1479
7/1/2006	5.76	5.4889
10/1/2006	5.40	5.3729
1/1/2007	5.37	5.3601
4/1/2007	5.42	5.3555
7/1/2007	5.84	5.3597
10/1/2007	5.38	5.1574
1/1/2008	4.82	3.9945
4/1/2008	4.80	2.7873
7/1/2008	5.02	2.792
10/1/2008	4.30	4.1032
1/1/2009	2.96	1.2223
4/1/2009	3.41	1.1165
7/1/2009	4.21	0.5178
10/1/2009	4.08	0.2818
1/1/2010	4.48	0.2501
4/1/2010	4.50	0.3073
7/1/2010	3.76	0.514
10/1/2010	3.49	0.289
1/1/2011	4.26	0.3033
4/1/2011	4.27	0.2817
7/1/2011	3.96	0.2496