Question
Monte Carlo valuation of European call option (instructions)
Cells shaded grey are parameter values that can be altered.
1. In cell B25, generate a random number from the standard normal distribution by entering the formula =NORM.S.INV(RAND()).
2. In cell C25, calculate the simulated stock price at date T for the standard normal random number in B25 by entering the formula =S_0*EXP((riskfree-sigma*sigma/2)*T+sigma*B25*SQRT(T)).
3. In cell D25, calculate the present value of the payoff to the European call option for the simulated stock price in cell C25 by entering the formula =EXP(-riskfree*T)*MAX(C25- K, 0).
4. Select and drag the formulas in cells B25:D25 down to B1024:D1024. You should now have 1000 simulated terminal stock prices and option payoff present values.
5. In cell F17, calculate the average of the simulated option payoff present values by entering the formula =average(D25:D1024). This is an estimate of the option price. The standard deviation of the estimate is reported in cell F18.
S_0 100 Mean (Call Price) Monte Carlo Valuation of European Call Option (full implementation in VBA)
K 100 SD ERROR:#DIV/0!
r 0.06 This VBA function calculates the price of a European call option by Monte Carlo simulation with antithetic variables.
sigma 0.2 Parameter values are in grey and can be set independently of those in the illustration at left.
T 1
S_0 100 Note: The function permits a non-zero dividend yield q on the stock.
Standard Normal Simulated Option Payoff K 100 Leave this set equal to zero to compare results with the illutration at left.
Random Numbers Stock Price Present Value r 0.06
sigma 0.2 Note: M is the number of trials in the Monte Carlo simulation. You may want to see how
q 0 changing the value of M affects the estimated option price.
T 1
M 10000
Euorpean Call Monte Carlo 10.91
Black-Scholes model (VBA function for European call option)
This VBA function uses the Black-Scholes formula to calculate the value of a European call option.
Parameter values are in grey and can be set indendently of those in the 3-step binomial example.
S_0 100 Note: The function permits a non-zero dividend yield q on the stock.
K 100 Leave this set equal to zero to compare results with the illutration at left.
r 0.06
sigma 0.2 You may also wish to check how the results from the Monte Carlo simulation in the box above
q 0 compare to the values from the Black-Scholes formula for various numbers of trials.
T 1 number of time steps N becomes large.
European Call Black-Scholes 10.99