Question
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.
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
K 100 u
sigma 0.2 d
risk_free 0.06 disc_factor 1.0000
T 1 a 1.0000
n 3 q_up
q_down
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.0000 0.0000 0 time Parameter values are in grey and can be set independently of those in the 3-step example.
Try varying the number of time steps N to see how the results compare with the 3-step example.
0.00 S_now 100 Note: The full implementation permits a non-zero dividend
K 100 yield q on the stock. Leave this set equal to zero if you want
0.00 0.00 r 0.06 to compare results with the 3-step example, which does not
0.00 0.00 sigma 0.2 permit a non-zero dividend yield.
100.00 0.00 q 0
0.00 0.00 T 1 You may also wish to check that the results converge to the
0.00 0.00 Upper number is stock price. N 3 Black-Scholes value as the number of time steps N becomes large.
0.00 0.00 Lower number is option value.
0.00 European Call Binomial 11.55
0.00
0.00
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.0000 0.0000 0 time Parameter values are in grey and can be set independently of those in the 3-step example.
0.00 Try varying the number of time steps N to see how the results compare with the 3-step example.
100.00
0.00 S_now 100 Note: The full implementation permits a non-zero dividend
100.00 K 100 yield q on the stock. Leave this set equal to zero if you want
0.00 0.00 Upper number is stock price. r 0.06 to compare results with the 3-step example, which does not
100.00 100.00 Lower number is option value. sigma 0.2 permit a non-zero dividend yield.
100.00 0.00 q 0
0.00 100.00 T 1
0.00 0.00 N 3
100.00 100.00
0.00 American Put Binomial 6.10
0.00