Forward Start Interest Rate Swap Excel Project - Create the dates for a 3 vear swap (3m Libor versus 6 month fixed) swap starting on December 15, 2023 - Access the Eurodollar futures page to download the relevant future rates: December 15, 2023, March 15, 2024, June 15, 2024 etc. - Calculate the actual days between payment dates - For $100 million, calculate the Libor payment amounts (ex. the Euro rate on March 15 is used to set the payment on June 15 , using the Actual /360 dav count convention) - Make an initial guess of the fixed rate and calculate payments using the 30/360 convention - Create Wealth factors: WF(0)=1;WF(3M)=WF(0)(1+ Libor 0,3MAct0,3M/360); etc. - Create Discount Factors: DF(t)=1/WF(t) - Net cash flows at payment dates and then discount these net flows to the start using DF's - Use Goal Seek to find that fixed rate which sets the sum of present values to zero. FORWARD STARTING INTEREST RATE SWAP 12/15/2023 3/15/2024 6/14/2024 9/13/2024 12/13/2024 3/14/2025 6/13/2025 9/15/2025 12/15/2025 3/15/2026 6/15/2026 9/15/2026 12/15/2026 3 years, semi-annual fixed versus 3 month Libor receiving floating $100,000,000 Eurodollar Future XX.XXX Notional Amount Libor rate Day count Libor payment 91 91 91 91 91 91 94 91 90 92 92 91 Fixed rate Fixed payment $X3 \$XX4 \$XX5 \$XX6 $X7 $ XX8 \$XX9 \$XX10 \$XX11 \$XX12 xx.xx% Net payment WF \$YY \$YY \$YY \$YY \$YY \$YY preceding business day convention Net payment DF PV Notes: Get the Libor rates from the Eurodollar futures prices: Libor rate =(100 - Eurodollar Price )/100 Calculate the day counts for each Libor quartely period. Calculate the Libor payments: Notional Amount * Libor rate * Actual Days in Quarterly Period/360, paid at the end of each quarter Calculate the fixed payment: Notional Amount * Fixed rate * 0.5, paid at the end of every 6 months Calculate the net payments Calculate Wealth Factors Wealth Factor (1)=1(1+ Libor (0,1) Actual days/360) Wealth Factor (2)= Wealth Factor (1)(1+ Libor (1,2) Actual days /360) Wealth Factor (T)= Wealth Factor (T1)(1+ Libor (T1,T) Actual days/360) (Check: If the Libor rates are in the 4% range, the terminal Wealth Factor will be in the area of 1.125) Calculate the Discount Factors : DF(1)=1/(WF(1) DF(T)=1/WF(T) Calculate the PV's: CF(t)DF(t) Calculate the NPV Use GoalSeek to set the NPV to zero by changing the fixed rate Forward Start Interest Rate Swap Excel Project - Create the dates for a 3 vear swap (3m Libor versus 6 month fixed) swap starting on December 15, 2023 - Access the Eurodollar futures page to download the relevant future rates: December 15, 2023, March 15, 2024, June 15, 2024 etc. - Calculate the actual days between payment dates - For $100 million, calculate the Libor payment amounts (ex. the Euro rate on March 15 is used to set the payment on June 15 , using the Actual /360 dav count convention) - Make an initial guess of the fixed rate and calculate payments using the 30/360 convention - Create Wealth factors: WF(0)=1;WF(3M)=WF(0)(1+ Libor 0,3MAct0,3M/360); etc. - Create Discount Factors: DF(t)=1/WF(t) - Net cash flows at payment dates and then discount these net flows to the start using DF's - Use Goal Seek to find that fixed rate which sets the sum of present values to zero. FORWARD STARTING INTEREST RATE SWAP 12/15/2023 3/15/2024 6/14/2024 9/13/2024 12/13/2024 3/14/2025 6/13/2025 9/15/2025 12/15/2025 3/15/2026 6/15/2026 9/15/2026 12/15/2026 3 years, semi-annual fixed versus 3 month Libor receiving floating $100,000,000 Eurodollar Future XX.XXX Notional Amount Libor rate Day count Libor payment 91 91 91 91 91 91 94 91 90 92 92 91 Fixed rate Fixed payment $X3 \$XX4 \$XX5 \$XX6 $X7 $ XX8 \$XX9 \$XX10 \$XX11 \$XX12 xx.xx% Net payment WF \$YY \$YY \$YY \$YY \$YY \$YY preceding business day convention Net payment DF PV Notes: Get the Libor rates from the Eurodollar futures prices: Libor rate =(100 - Eurodollar Price )/100 Calculate the day counts for each Libor quartely period. Calculate the Libor payments: Notional Amount * Libor rate * Actual Days in Quarterly Period/360, paid at the end of each quarter Calculate the fixed payment: Notional Amount * Fixed rate * 0.5, paid at the end of every 6 months Calculate the net payments Calculate Wealth Factors Wealth Factor (1)=1(1+ Libor (0,1) Actual days/360) Wealth Factor (2)= Wealth Factor (1)(1+ Libor (1,2) Actual days /360) Wealth Factor (T)= Wealth Factor (T1)(1+ Libor (T1,T) Actual days/360) (Check: If the Libor rates are in the 4% range, the terminal Wealth Factor will be in the area of 1.125) Calculate the Discount Factors : DF(1)=1/(WF(1) DF(T)=1/WF(T) Calculate the PV's: CF(t)DF(t) Calculate the NPV Use GoalSeek to set the NPV to zero by changing the fixed rate