Excel Spreadsheet

Financial Engineering Derivatives and Risk Management 4

You must be logged in to download this document
Reviews
Shared by: mythri k
Tags
Stats
views:
119
downloads:
4
rating:
not rated
reviews:
0
posted:
1/23/2008
language:
English
pages:
0
Swap is to be received : '(return on S&P500 -0.5% p.a. spread)' and pay LIBOR Notional principal 1,000,000 Swap spread -0.005 (or -0.5 %) Days in year 360 Date Days LIBOR Equity Index Return LIBOR S&P -Spread Swap 10-Jan-01 5.00% 1,500.00 10-Apr-01 90 5.35% 1,530.00 1.9803% 12,500 18,553 6,053 10-Jul-01 91 5.50% 1,510.00 -1.3158% 13,524 -14,422 -27,946 10-Oct-01 92 4.95% 1,538.00 1.8373% 14,056 17,095 3,040 10-Jan-02 92 1,630.00 5.8097% 12,650 56,819 44,169 S&P 500 Table 16.2 : Payments in an Equity Swap PaymentTreecontains three nodes (n=3) q is the risk neutral probability q = (R-D)/(U-D) or q = (exp(-rt) -D)/(U -D) for continuously compounded rates, or use exp(-rt) for continously compounded rates and t = 1 here. R = 1+r and r is the periodic rate S(0) 100 Time 0 Time 1 Time 2 Time 3 K 100 U 1.15 152.09 D 0.8 132.25 r 0.1 115 105.8 R 1.1 100 92 80 73.6 q 0.857143 64 (1-q) 0.142857 51.2 Path Number Probability S (0) S (1) S (2) S (3) S (average) S (Max) S (Min) Number of u's Call Put Price Call Price Put Strike Call Strike Put Payoff Payoff Payoff Payoff Payoff Payoff 1 u u u 3 0.6297 100 115.00 132.25 152.09 124.83 152.09 100 52.09 0.00 24.83 0.00 27.25 0.00 2 u u d 2 0.1050 100 115.00 132.25 105.80 113.26 132.25 100 5.80 0.00 13.26 0.00 0.00 7.46 3 u d u 2 0.1050 100 115.00 92.00 105.80 103.20 115.00 92 5.80 0.00 3.20 0.00 2.60 0.00 4 d u u 2 0.1050 100 80.00 92.00 105.80 94.45 105.80 80 5.80 0.00 0.00 5.55 11.35 0.00 5 u d d 1 0.0175 100 115.00 92.00 73.60 95.15 115.00 73.6 0.00 26.40 0.00 4.85 0.00 21.55 6 d u d 1 0.0175 100 80.00 92.00 73.60 86.40 100.00 73.6 0.00 26.40 0.00 13.60 0.00 12.80 7 d d u 1 0.0175 100 80.00 64.00 73.60 79.40 100.00 64 0.00 26.40 0.00 20.60 0.00 5.80 8 d d d 0 0.0029 100 80.00 64.00 51.20 73.80 100.00 51.2 0.00 48.80 0.00 26.20 0.00 22.60 Value of Options 26.02 1.15 13.05 1.01 13.99 1.17 Notes i) S(1), S(3), S(3) are the stock prices at each node for a specific path through the lattice ii) S(average) is the average value of the stock price for a specific path, including S(0) in the average ii) S(Max) and S(Min) are the maximum and minimum values of the stock price for a specific path ii) European call and put payoffs are based solely on S(3) the stock price at maturity ii) Asian average price options payoffs are, for a call, max{S(average) -K,0} and for a put max{K -S(average),0} ii) Asian average strike options payoffs are, for a call, max{S(3) -S(average),0} and for a put max{S(average)-S(3) , 0} Path European Options Average (Price or Strike) Asian Options Table 16.3 : Pricing an Asian Call Option-BOPMShare price, S 100 Strike Price, K 110 Interest rate, r 0.1 Drift of stock price, m 0.15 (Not used, since in a risk neutral world S grows at the risk free rate , µ Volatility, s 0.4 Time to maturity, T 1 Timesteps, Dt 0.01 Time Sim 1 Sim 2 Sim 3 Sim 4 Sim 5 Sim 6 Sim 7 Sim 8 0 100 100 100 100 100 100 100 100 0.01 102.8423 94.30967 96.3762 102.9574 95.25502 99.52829 105.234 107.6835 0.02 109.1265 89.83397 90.97818 101.4581 93.55689 100.171 104.4086 107.4582 0.03 107.6888 90.86094 94.96471 103.1789 90.20224 94.23641 101.2504 101.6358 0.04 111.5041 94.27257 96.86531 103.9894 96.3784 94.89705 102.7229 101.8148 0.05 105.5125 89.00162 101.6352 98.86783 103.0423 99.47556 99.60681 99.77242 … … … … … … … … … 0.98 103.932 59.36588 161.8954 108.0626 45.00737 97.2644 57.22612 41.80161 0.99 103.2412 62.1673 158.9093 100.7344 46.04716 93.86506 57.07112 40.45232 1 108.2955 64.03757 146.4788 96.66184 46.78858 96.89266 56.62576 38.94615 Average stock price 97.80 65.77 121.37 104.84 78.62 115.33 84.17 63.40 Asian payoff 0.00 0.00 11.37 0.00 0.00 5.33 0.00 0.00 Asian Call Option "Payoff at T" = max[S(average) -K, 0] Asian Call Premium = exp(-rT) x "mean of payoffs at T" Mean payoff is 1.67 Call premium is 1.51 Table 16.4 : Monte Carlo Simulation for Pricing Asian Optionsµ= r ) Sim 9 Sim 10 100 100 106.6371 99.7442 102.9564 104.8304 104.911 101.139 104.2574 97.00682 98.14451 101.4949 … … 107.8738 101.7982 106.0776 103.9824 100.273 102.7801 106.47 109.50 0.00 0.00 Pricing Asian OptionsTree contains three 3 nodes (n = 3). q is the risk neutral probability q = (R-D)/(U-D) R = 1+r and r is the periodic rate for timestep dt=1 (here) S(0) 100 Time 0 Time 1 Time 2 Time 3 K 100 U 1.15 152.09 D 0.8 132.25 r 0.1 115.00 105.80 R 1.1 100 92.00 80.00 73.60 q 0.857143 64.00 (1-q) 0.142857 51.20 Barrier 90 90 85 85 110 110 110 110 Path Number Probability S (0) S (1) S (2) S (3) Number of u's Out-Call In-Call Out-Put In-Put Out-Call In-Call Out-Put In-Put 1 u u u 3 0.6297 100 115.00 132.25 152.09 52.09 knock 0.00 knock knock 52.09 knock 0.00 2 u u d 2 0.1050 100 115.00 132.25 105.80 5.80 knock 0.00 knock knock 5.80 knock 0.00 3 u d u 2 0.1050 100 115.00 92.00 105.80 5.80 knock 0.00 knock knock 5.80 knock 0.00 4 d u u 2 0.1050 100 80.00 92.00 105.80 knock 5.80 knock 0.00 5.80 knock 0.00 knock 5 u d d 1 0.0175 100 115.00 92.00 73.60 0.00 knock 26.40 knock knock 0.00 knock 26.40 6 d u d 1 0.0175 100 80.00 92.00 73.60 knock 0.00 knock 26.40 0.00 knock 26.40 knock 7 d d u 1 0.0175 100 80.00 64.00 73.60 knock 0.00 knock 26.40 0.00 knock 26.40 knock 8 d d d 0 0.0029 100 80.00 64.00 51.20 knock 0.00 knock 48.80 0.00 knock 48.80 knock Value of Option 25.56 0.46 0.35 0.80 0.46 25.56 0.80 0.35 Table 16.5 : Pricing a Barrier Option Path Down -out and Down -in Options Payoff Up -out and Up -in Options Payoff

0
Related docs
Other docs by mythri k