Report

Session 9b Overview Finance Simulation Models • Securities Pricing – Black-Scholes – Electricity Option • Miscellaneous – Monte Carlo vs. Latin Hypercube – Review of Binomial Decision Models -- Prof. Juran 2 Finance Example • A European call option on a stock earns the owner an amount equal to the price at expiration minus the exercise price, if the price of the stock on which the call is written exceeds the exercise price. Otherwise, the call pays nothing. • A European put option earns the owner an amount equal to the exercise price minus the price at expiration, if the price at expiration is less than the exercise price. Otherwise the put pays nothing. Decision Models -- Prof. Juran 3 Finance Example • The Black-Scholes formula calculates the price of a European options based on the following inputs: – – – – – today's stock price the duration of the option (in years) the option's exercise price the risk-free rate of interest (per year) the annual volatility (standard deviation) in stock price Decision Models -- Prof. Juran 4 The Black-Scholes model: C SN d 1 Ee rt N d 2 where: S E r σ2 t d1 d2 N(d) Decision Models -- Prof. Juran = current stock price = exercise price = risk-free rate of return = variance of the stock’s return = time to expiration 2 S ln r t E 2 = 2t d1 2 t = = probability that z < d 5 A Black-Scholes calculator: A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 B Inputs 1 35 40 0.5 0.05 0.4 Type of option (1 for call, 2 for put) Stock price Exercise price Duration (years) Riskfree interest rate Volatility C D E F G H =IF(B2=1,NORMSDIST(B10),NORMSDIST(-B10)) =(LN(B3/B4)+(B6+B7^2/2)*B5)/(B7*SQRT(B5)) Quantities for Black-Scholes formula d1 d2 Option price -0.242 -0.525 =B10-SQRT(B7^2*B5) 2.456 N(d1) N(d2) 0.404 0.300 =IF(B2=1,NORMSDIST(B11),NORMSDIST(-B11)) =IF(B2=1,B3*E10-B4*EXP(-B5*B6)*E11,-(B3*E10-B4*EXP(-B5*B6)*E11)) Notice the use of “if” statements in cells E10:E11 and B13, so that the same model can be used for both puts and calls. Decision Models -- Prof. Juran 6 Example: Diageo (DEO) Decision Models -- Prof. Juran 7 Assume today is the first trading day of October and that DEO is selling for $57.98 per share. What is a fair price for a six-month call option with a strike price of $60.00? Assume the risk-free rate is 10%. Two approaches: •Black-Scholes formula •Crystal Ball model Decision Models -- Prof. Juran 8 Data file: deo-data.xlsx A 1 Date 2 17-Dec-97 3 2-Jan-98 4 2-Feb-98 5 2-Mar-98 6 1-Apr-98 7 1-May-98 8 1-Jun-98 9 1-Jul-98 10 3-Aug-98 11 1-Sep-98 12 1-Oct-98 B C D E F G Open High Low Close Volume Adj. Close* 36.17 36.29 34.12 34.58 201080 25.53 34.12 35.37 32.81 33.26 254960 24.56 33.38 38.8 33.32 37.94 221678 28.02 38.74 45.42 38.4 44.33 181459 32.74 45.98 47.49 42.43 44.64 179619 33.6 44.87 46.44 41.91 42.03 206615 31.63 42.09 46.73 41.91 44.58 115309 33.55 44.41 48.07 43.19 44.93 136350 33.82 43.19 44.58 34.66 35.12 130133 26.43 37.62 39.47 30.65 33.67 160090 25.34 34.08 41.87 33.67 41.58 143327 31.92 Decision Models -- Prof. Juran 9 Remove unnecessary columns and calculate monthly returns: A 1 Date 2 17-Dec-97 3 2-Jan-98 4 2-Feb-98 5 2-Mar-98 6 1-Apr-98 7 1-May-98 8 1-Jun-98 A 1 Date 2 17-Dec-97 3 2-Jan-98 4 2-Feb-98 5 2-Mar-98 6 1-Apr-98 7 1-May-98 8 1-Jun-98 B C Adj. Close* Return 25.53 24.56 -0.0380 28.02 0.1409 32.74 0.1685 33.6 0.0263 31.63 -0.0586 33.55 0.0607 D =(B3-B2)/B2 B C D Adj. Close* Return 1+Return 25.53 24.56 -0.0380 0.9620 28.02 0.1409 1.1409 32.74 0.1685 1.1685 33.6 0.0263 1.0263 31.63 -0.0586 0.9414 33.55 0.0607 1.0607 Decision Models -- Prof. Juran E E F =C3+1 10 Black-Scholes assumes that the future price is the following random function of the current price: pt p0 exp 0.5 t Z t t p0 pt exp(x) e Z 2 Some specific time in the future Current price Price at time t ex Root of the natural logarithms (about 2.718282) Mean percentage growth rate of the stock Standard deviation of the growth rate (a.k.a. Volatility) Normal random variable with mean 0 and standard deviation 1 Decision Models -- Prof. Juran 11 Another way to look at it: ROI log Decision Models -- Prof. Juran pt ln p 0 12 A 1 Date 2 17-Dec-97 3 2-Jan-98 4 2-Feb-98 5 2-Mar-98 6 1-Apr-98 7 1-May-98 8 1-Jun-98 93 94 95 96 97 98 99 100 101 102 A 1-Jul-05 1-Aug-05 1-Sep-05 3-Oct-05 B C D E Adj. Close* Return 1+Return Ln(1+Return) 25.53 24.56 -0.0380 0.9620 -0.0387 28.02 0.1409 1.1409 0.1318 32.74 0.1685 1.1685 0.1557 33.6 0.0263 1.0263 0.0259 31.63 -0.0586 0.9414 -0.0604 33.55 0.0607 1.0607 0.0589 B C 54.44 -0.0612 56.43 0.0366 58.01 0.0280 57.98 -0.0005 D 0.9388 1.0366 1.0280 0.9995 E -0.0632 0.0359 0.0276 -0.0005 Monthly Mean StDev 0.0087 0.0653 Annual 0.1047 0.2261 Decision Models -- Prof. Juran Mean StDev F G =LN(D3) F G H =AVERAGE(E3:E96) =STDEV(E3:E96) =E98*12 =E99*SQRT(12) 13 A 1 2 3 4 5 6 7 8 9 10 11 12 13 Type of option (1 for call, 2 for put) Stock price Exercise price Duration (years) Riskfree interest rate Volatility B C Inputs 1 57.98 60 0.5 0.1 0.226148277 Quantities for Black-Scholes formula d1 d2 0.178 0.019 Option price 4.137 Decision Models -- Prof. Juran D E N(d1) 0.571 N(d2) 0.507 14 Now we create a new sheet that uses estimated parameters from the data to calculate the future price of DEO and the resulting cash flow from the option. The present value of the expected payout is $0.17, but Black-Scholes says $4.14. Why? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 A Inputs Current Price Strike Price Mean Annual Return StDev Annual Return Risk-Free Rate Duriation (Years) $ 57.98 $ 60.00 0.1047 0.2261 0.1 0.5 Simulation Z Stock Price (at Risk-Free Rate) Option Cash Flow 0.0000 $ 60.18 $ 0.18 Output Discounted Value $ Decision Models -- Prof. Juran B 0.17 C D E F G =data!E101 =data!E102 =B2*EXP((B6-0.5*B5^2)*B7+B5*B10*SQRT(B7)) =MAX(B11-B3,0) =EXP(-B7*B6)*B12 15 Notes on the formulas: B11: pt p0 exp 0.5 t Z t 2 (random future DEO price) B12: C MAXpt E,0 (payoff from the option) B15: PVC e trf (present value of the payoff) Decision Models -- Prof. Juran 16 A green cell: B10 is now a standard normal random variable A blue cell: B15 is now the present value of the random cash flow from the option A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 B Inputs Current Price Strike Price Mean Annual Return StDev Annual Return Risk-Free Rate Duriation (Years) $ 57.98 $ 60.00 0.1047 0.2261 0.1 0.5 Simulation Z Stock Price (at Risk-Free Rate) Option Cash Flow 0.0000 $ 60.18 $ 0.18 Output Discounted Value $ Decision Models -- Prof. Juran 0.17 C D E F G =data!E101 =data!E102 =B2*EXP((B6-0.5*B5^2)*B7+B5*B10*SQRT(B7)) =MAX(B11-B3,0) =EXP(-B7*B6)*B12 17 Simulation Results Decision Models -- Prof. Juran 18 OK, so simulation can do the same thing as Black-Scholes. Maybe Black-Scholes is easier and/or quicker than running a simulation. So, why do we need the simulation at all? Decision Models -- Prof. Juran 19 • Black-Scholes is an analytical result; if specific assumptions hold true, then we can calculate the expected value of the payout on an option. • Analytical solutions do not exist in general for all types of financial instruments. • In the absence of analytical results, Monte Carlo simulation offers an alternative approach. • Analytical solutions may exist for expected value, but not for other important parameters. Decision Models -- Prof. Juran 20 Example: Asian Option George Brickfield’s business is highly exposed to volatility in the cost of electricity. He has asked his investment banker, Lisa Siegel, to propose an option whereby he can hedge himself against changes in the cost of a kilowatt hour of electricity over the next twelve months. Decision Models -- Prof. Juran 21 Lisa thinks that an Asian option would work nicely for George’s situation. An Asian option is based on the average price of a kilowatt hour (or other underlying commodity) over a specified time period. Decision Models -- Prof. Juran 22 In this case, Lisa wants to offer George a one year Asian option with a target price of $0.059. •If the average price per kilowatt hour over the next twelve months is greater than this target price, then Lisa will pay George the difference. •If the average price per kilowatt hour over the next twelve months is less than this target price, then George loses the price he paid for the option (but he is happy, because he ends up buying relatively cheap electricity). Decision Models -- Prof. Juran 23 What is a fair price for Lisa to charge for 1 million kwh worth of these options? Use the historical data provided and Monte Carlo simulation to arrive at a fair price. Decision Models -- Prof. Juran 24 Analysis of historical data: Our model will be based not on the actual prices, but on monthly percent changes in price (a.k.a. returns): A B C 1 Month $/kwh Return 2 Jan $ 0.051 3 Feb $ 0.056 0.09804 4 Mar $ 0.054 -0.03571 5 Apr $ 0.052 -0.03704 6 May $ 0.052 0.00000 7 Jun $ 0.057 0.09615 8 Jul $ 0.067 0.17544 9 Aug $ 0.064 -0.04478 10 Sep $ 0.064 0.00000 11 Oct $ 0.058 -0.09375 12 Nov $ 0.054 -0.06897 13 Dec $ 0.057 0.05556 Decision Models -- Prof. Juran D E F mean stdev 0.1768% 7.3462% =(B3-B2)/B2 G H I =STDEV(C2:C121) =AVERAGE(C2:C121) 25 Histogram of Electricity Returns 30 25 Frequency 20 15 10 5 0 -0.200 -0.175 -0.150 -0.125 -0.100 -0.075 -0.050 -0.025 0.000 0.025 0.050 0.075 0.100 0.125 0.150 0.175 0.200 0.225 0.250 0.275 0.300 Monthly Price Change Decision Models -- Prof. Juran 26 • Returns are approximately normal. • We’ll use the sample mean and sample standard deviation from the data (0.001768 and 0.073462, respectively). Decision Models -- Prof. Juran 27 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 A Initial Electricity Price Target Price Mean monthly return Std dev monthly return # kwh per option Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec B Return 1.13% -0.21% 7.82% -5.77% 6.54% 8.31% -12.05% 9.71% 1.56% 6.84% 9.39% -0.50% Decision Models -- Prof. Juran D C $0.05684 $0.05900 0.18% 7.35% 1,000,000 Price 0.05748 0.05736 0.06185 0.05828 0.06209 0.06725 0.05915 0.06489 0.06590 0.07041 0.07703 0.07664 E F G Average Price 0.06486 =AVERAGE(C8:C26) Payout 5,861.89 $ =C5*MAX(E8-C2,0) H =C15*(1+B16) 28 In B8:B19 we have 12 Crystal Ball assumption cells, normally distributed with the mean and standard deviation from our sample data (C3 and C4). In C8:C19 we use the random percent returns to calculate monthly prices, which are averaged in E8 for the whole year. E11 calculates the payout on the option (a Crystal Ball forecast cell). The average value of E11 over many trials will be a reasonable estimate of the fair price for this option. Decision Models -- Prof. Juran 29 We’ll add a graph, to show the change in electricity prices over the course of each simulated year: F G H I J K L Price 0.12 0.11 0.09 0.08 0.07 0.06 0.05 0.04 ar Ap r M ay Ju n Ju l Au g Se p O ct N ov D ec Payout $ 5,861.89 0.10 b Average Price 0.06486 M Price 0.05748 0.05736 0.06185 0.05828 0.06209 0.06725 0.05915 0.06489 0.06590 0.07041 0.07703 0.07664 E n Return 1.13% -0.21% 7.82% -5.77% 6.54% 8.31% -12.05% 9.71% 1.56% 6.84% 9.39% -0.50% C D $0.05684 $0.05900 0.18% 7.35% 1,000,000 Fe Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec B Ja A Initial Electricity Price Target Price Mean monthly return Std dev monthly return # kwh per option Price ($/kwh) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Month Decision Models -- Prof. Juran 30 Decision Models -- Prof. Juran 31 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 A B Statistics Payout Trials 1000000 Base Case $5,861.89 Mean $2,927.16 Median $0.00 Mode $0.00 Standard Deviation $5,249.37 Variance $27,555,886.95 Skewness 2.31 Kurtosis 9.16 Coeff. of Variation 1.79 Minimum $0.00 Maximum $63,307.77 Range Width $63,307.77 Mean Std. Error $5.25 C D E alpha z-value UCL LCL X 1.96 $2,927.16 1.96 0.05 1.959964 $2,937.45 $2,916.87 F G H =-NORMSINV(E2/2) =$B$4+$E$3*$B$15 =$B$4-$E$3*$B$15 s n $5,249.67 1,000,000 1.96$5.25 $10 .29 95% confident that the true fair price is between $2,916.87 and $2,937.45. Could narrow the interval by running a longer simulation. Decision Models -- Prof. Juran 32 Monte Carlo vs. Latin Hypercube Decision Models -- Prof. Juran 33 Example: Standard normal distribution (mean = 0, standard deviation = 1) Divided into 8 equal-probability ranges -4 -3 -2 Decision Models -- Prof. Juran -1 0 1 2 3 4 34 Probability that the first random independent observation falls into any one range is 0.125. -4 -3 -2 Decision Models -- Prof. Juran -1 0 * 1 2 3 4 35 Probability that the first two observations fall into any one range is 0.1252 = 0.01563. -4 -3 -2 Decision Models -- Prof. Juran -1 0 * * 1 2 3 4 36 Probability that the first three observations fall into any one range is 0.1253 = 0.001953. A small (but not zero!) chance of an unrepresentative sample. -4 -3 -2 Decision Models -- Prof. Juran -1 0 * * * 1 2 3 4 37 Latin Hypercube ensures that each range gets one observation before any range gets a second observation (but with more than 8 ranges). -4 -3 -2 * * * ** * * * Decision Models -- Prof. Juran -1 0 1 2 3 4 38 Monte Carlo vs. Latin Hypercube A 1 2 3 4 5 Decision Models -- Prof. Juran B C 0 0 =B2 39 Decision Models -- Prof. Juran 40 Decision Models -- Prof. Juran 41 Decision Models -- Prof. Juran 42 Decision Models -- Prof. Juran 43 Decision Models -- Prof. Juran 44 Binomial Random Variable Decision Models -- Prof. Juran 45 The number of successes in a Bernoulli process is a binomial random variable. The probability that a binomial variable X will take on any particular value x is given by the binomial formula: P X x n , p n p x 1 p n x x x n! p 1 p n x x! n x ! If X is a Binomial(n, p) random variable, then Expected Value: Variance: Standard Deviation: Decision Models -- Prof. Juran E(X) = np Var(X) = np(1 - p) (X) = np( 1 p ) 46 A Bernoulli process is a sequence of n identical trials of a random experiment such that each trial: (1) produces one of two possible complimentary outcomes that are conventionally called success and failure and (2) is independent of any other trial so that the probability of success or failure is constant from trial to trial. Note that the success and failure probabilities are assumed to be constant from trial to trial, but they are not necessarily equal to each other. Decision Models -- Prof. Juran 47 1 2 3 4 X 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 B n p 0 1 2 3 4 5 6 7 8 9 10 C D E F G H I J 10 0.44 0.0030 0.0238 0.0843 0.1765 0.2427 0.2289 0.1499 0.0673 0.0198 0.0035 0.0003 =BINOMDIST(A5,$C$1,$C$2,0) Binomial Distribution 0.3 0.3 Probability A 0.2 0.2 0.1 0.1 0.0 Decision Models -- Prof. Juran 0 1 2 3 4 5 6 7 8 9 10 Number of "Successes" 48 Summary Finance Simulation Models • Securities Pricing – Black-Scholes – Electricity Option • Miscellaneous – Monte Carlo vs. Latin Hypercube – Review of Binomial Decision Models -- Prof. Juran 49