In this example, we derived call and put option
price using the binomial model, also known as the Cox-Ross-Rubinstein
option model. The outcomes are shown in a format similar to that used
for example 6. Note that binomial distribution
will become normal when the number of steps (n) becomes large. Hence,
when n increases, both of the call and put option prices estimated from
the binomial model come close to the prices estimated from the
Black-Scholes model. This phenomenon is shown on Figure 1. For example,
the option prices estimated using the binomial model with 1,000 steps
(in cells K13..K14) are equivalent (to 3 decimal places) to the prices
estimated from the Black-Scholes model in cells H23..H24.
'************************************************************************
'*
Binomial European Call Price
*
'************************************************************************
Function Bi_Call_Eur(s, x, t, r,
sd, n As Integer)
Dim sdd As
Single
Dim j As
Integer
Dim rr As
Single
Dim q As
Single
Dim u As
Single
Dim d As
Single
Dim bicomp As
Single
Dim sumbi As
Single
Dim nj As
Double
Dim
firstBicomp As Single
rr = Exp(r *
(t / n)) - 1
sdd = sd *
Sqr(t / n)
u = Exp(rr +
sdd)
d = Exp(rr -
sdd)
q = (1 + rr -
d) / (u - d)
For j = 0 To n
nj = binoCoeff(n, j)
bicomp = nj * (q ^ j) * ((1 - q) ^ (n - j)) * (s * (u ^ j) * (d ^ (n -
j)) - x)
If bicomp < 0 Then bicomp = 0
sumbi = sumbi + bicomp
Next j
Bi_Call_Eur =
sumbi / ((1 + rr) ^ n)
End Function
'*********************************************************************
'*
Binomial
European Put Price
*
'*********************************************************************
Function Bi_Put_Eur(s, x, t, r,
sd, n As Integer)
Dim sdd As
Single
Dim j As
Integer
Dim rr As
Single
Dim q As
Single
Dim u As
Single
Dim d As
Single
Dim bicomp As
Single
Dim sumbi As
Single
Dim nj As
Double
Dim
firstBicomp As Single
rr = Exp(r *
(t / n)) - 1
sdd = sd *
Sqr(t / n)
u = Exp(rr +
sdd)
d = Exp(rr -
sdd)
q = (1 + rr -
d) / (u - d)
For j = 0 To n
nj = binoCoeff(n, j)
bicomp = nj * (q ^ j) * ((1 - q) ^ (n - j)) * (x - (s * (u ^ j) * (d ^
(n - j))))
If bicomp < 0 Then bicomp = 0
sumbi = sumbi + bicomp
Next j
Bi_Put_Eur =
sumbi / ((1 + rr) ^ n)
End Function
'************************************************************************
'*
Compute
Binomial Coefficient
*
'* (this
function provides similar result as COMBIN( ) on Excel) *
'************************************************************************
Function binoCoeff(n, j)
Dim i As Integer
Dim b As Double
b = 1
For i = 0 To j - 1
b = b * (n - i) / (j - i)
Next i
binoCoeff = b
End Function
