Answer
Multi-period binomial model (3-step example instructions) Black-Scholes model (VBA function for European call option)
Cells shaded grey are inputs or parameters that can be altered. 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 independently of those in the 3-step binomial example.
Cells shaded blue are cells in which you must enter an appropriate formula in which to calculate the value.
In each case, the required formula is shown next to the cell to which it applies. S_now 100 Note: The function permits a non-zero dividend yield q on the
K 100 stock. Leave this set equal to zero if you want to compare results
Complete the worksheet to calculate the value for a European call option and for an American put option. r 0.06 with the 3-step binomial example.
You can then experiment with the parameter values to see how changing them affects the option values. sigma 0.2
q 0 You may also wish to check that the results from the full binomial
See the Answer worksheet for the completed calculations. T 1 model in the box below converge to the Black=Scholes value as the
number of time steps N becomes large.
Multi-period binomial model (3-step example parameters) European Call Black-Scholes 10.99
S_now 100 delta_t 0.3333 <-- = T/n K 100 u 1.1224 <-- = exp(sigma*sqrt(delta_t)) sigma 0.2 d 0.8909 <-- = 1/u risk_free 0.06 disc_factor 0.9802 <-- = exp(-risk_free*delta_t) T 1 a 1.0202 <-- = exp(risk_free*delta_t) n 3 q_up 0.5584 <-- = (a - d)/(u - d) q_down 0.4416 <-- = 1 - q_up European call option (3-step example) Multi-period binomial model (full VBA implementation for European call option) 0 1 2 3 step This VBA function is a full implementation of the CRR algortihm for valuing a European call option. 0 0.3333 0.6667 1 time Parameter values are in grey and can be set independently of those in the 3-step example. 141.40 <-- = D34*u Try varying the number of time steps N to see how the results compare with the 3-step example. 41.40 <-- = max(E32 - K, 0) 125.98 S_now 100 Note: The full implementation permits a non-zero dividend 27.96 <-- = disc_factor*(q_up*E33 + q_down*E37) K 100 yield q on the stock. Leave this set equal to zero if you want 112.24 112.24 r 0.06 to compare results with the 3-step example, which does not 18.20 12.24 sigma 0.2 permit a non-zero dividend yield. 100.00 100.00 q 0 11.55 6.70 T 1 You may also wish to check that the results converge to the 89.09 89.09 Upper number is stock price. N 3 Black-Scholes value as the number of time steps N becomes large. 3.67 0.00 Lower number is option value. 79.38 European Call Binomial 11.55 0.00 70.72 0.00 American put option (3-step example) Multi-period binomial model (full VBA implementation for American put option) 0 1 2 3 step This VBA function is a full implementation of the CRR algortihm for valuing an American put option. 0 0.3333 0.6667 1 time Parameter values are in grey and can be set independently of those in the 3-step example. 141.40 Try varying the number of time steps N to see how the results compare with the 3-step example. 0.00 125.98 S_now 100 Note: The full implementation permits a non-zero dividend 0.00 K 100 yield q on the stock. Leave this set equal to zero if you want 112.24 112.24 Upper number is stock price. r 0.06 to compare results with the 3-step example, which does not 2.04 0.00 Lower number is option value. sigma 0.2 permit a non-zero dividend yield. 100.00 100.00 q 0 6.10 4.72 T 1 89.09 89.09 N 3 11.51 10.91 79.38 American Put Binomial 6.10 20.62 <-- = max(disc_factor*(q_up*E62 + q_down*E66, K - D63) 70.72 29.28 <-- = max(K - E65, 0)