2. Please refer to the spreadsheet titled ‘Ch15_Interest_Rate_Swap_Valuation.xlsm’ and the quotes in problem 1 above. Compute the fixed rate that equates the present value of floating payments and the fixed payments for a notional $50,000m.

profileAlexEspa1
Home>Homework Answsers>Business & Finance homework help>Financial markets homework help
fin

Just question number 2

  • 3 years ago
  • 20
Report issue
files (2)

Ch15_Interest_Rate_Swap_Valuation.xlsm

Spot rates

3 month Eurodollar futures contract
15-Mar-12 Futures Spot Discount factors using Discount factors using
Quarters Futures quotes Annuliazed rate Annualized rates spot rates futures rates
1 June expiry Mar-Jun-2012 99.5250 0.47% 0.47% 0.99881 0.99881
2 Sept expiry Jun-Sep-2012 99.4800 0.52% 0.50% 0.99752 0.99752
3 Sep-Dec-2012 99.4250 0.58% 0.52% 0.99609 0.99609
4 Dec-Mar-2013 99.3750 0.63% 0.55% 0.99453 0.99453
5 Mar-Jun-2013 99.3050 0.69% 0.58% 0.99281 0.99281
6 Jun-Sep-2013 99.2200 0.78% 0.61% 0.99087 0.99087
7 Sep-Dec-2013 99.1250 0.88% 0.65% 0.98871 0.98871
8 Dec-Mar-2014 99.0250 0.97% 0.69% 0.98631 0.98631
9 Mar-Jun-2014 98.9050 1.10% 0.73% 0.98361 0.98361
10 Jun-Sep-2014 98.7650 1.24% 0.78% 0.98059 0.98059
11 Sep-Dec-2014 98.5950 1.41% 0.84% 0.97715 0.97715
12 Dec-Mar-2015 98.4200 1.58% 0.90% 0.97331 0.97331
<-- =(100-D15)/100
<-- =((((1+F14/4)^$A14)*(1+E15/4))^(1/$A15)-1)*4
<-- =1/(1+F15/4)^A15
<-- =1/((1+E4/4)*(1+E5/4)*(1+E6/4)*(1+E7/4)*(1+E8/4)*(1+E9/4)*(1+E10/4)*(1+E11/4)*(1+E12/4)*(1+E13/4)*(1+E14/4)*(1+E15/4))

Valuation

3 month Eurodollar futures contract
15-Mar-12 Futures Spot fc 0.90% Discount factors using Discount factors using
Quarters Futures quotes Annuliazed rate Annualized rates Notional ($m) Interest payment fixed pv float pv fixed spot rates futures rates
1 June expiry Mar-Jun-2012 99.5250 0.475% 0.475% 50,000,000 $ 59,375.00 $ 112,517.54 $ 59,304.58 $ 112,384.08 0.99881 0.99881
2 Sept expiry Jun-Sep-2012 99.4800 0.520% 0.497% 50,000,000 $ 65,000.00 $ 112,517.54 $ 64,838.61 $ 112,238.17 0.99752 0.99752
3 Sep-Dec-2012 99.4250 0.575% 0.523% 50,000,000 $ 71,875.00 $ 112,517.54 $ 71,593.63 $ 112,077.06 0.99609 0.99609
4 Dec-Mar-2013 99.3750 0.625% 0.549% 50,000,000 $ 78,125.00 $ 112,517.54 $ 77,697.76 $ 111,902.22 0.99453 0.99453
5 Mar-Jun-2013 99.3050 0.695% 0.578% 50,000,000 $ 86,875.00 $ 112,517.54 $ 86,250.05 $ 111,708.12 0.99281 0.99281
6 Jun-Sep-2013 99.2200 0.780% 0.612% 50,000,000 $ 97,500.00 $ 112,517.54 $ 96,610.23 $ 111,490.72 0.99087 0.99087
7 Sep-Dec-2013 99.1250 0.875% 0.649% 50,000,000 $ 109,375.00 $ 112,517.54 $ 108,140.30 $ 111,247.36 0.98871 0.98871
8 Dec-Mar-2014 99.0250 0.975% 0.690% 50,000,000 $ 121,875.00 $ 112,517.54 $ 120,206.19 $ 110,976.86 0.98631 0.98631
9 Mar-Jun-2014 98.9050 1.095% 0.735% 50,000,000 $ 136,875.00 $ 112,517.54 $ 134,632.24 $ 110,673.89 0.98361 0.98361
10 Jun-Sep-2014 98.7650 1.235% 0.785% 50,000,000 $ 154,375.00 $ 112,517.54 $ 151,378.11 $ 110,333.23 0.98059 0.98059
11 Sep-Dec-2014 98.5950 1.405% 0.841% 50,000,000 $ 175,625.00 $ 112,517.54 $ 171,612.80 $ 109,947.04 0.97715 0.97715
12 Dec-Mar-2015 98.4200 1.580% 0.903% 50,000,000 $ 197,500.00 $ 112,517.54 $ 192,228.75 $ 109,514.46 0.97331 0.97331
$ 1,334,493.23 $ 1,334,493.23
Quote data (given)
Notional amount (given)
Estimated from quote data
Estimated from Futures annualized rate
PV of floating should equal PV of fixed (solver)
PV of floating interest payments using spot annualized rates
Estimated using solver
Verifying that PV factors using futures rates and annualized spot rates are same

Sheet3

image1.png