|
|
 |
|
|
|
 |
|
|
|
|
|
|
|
|
|
See also BIOPM
|
|
|
and BIAOPM
|
|
|
|
|
|
This function calculates an option value based on the Binomial Option Pricing Model. Like Black-Scholes it assumes a European Option.
|
|
|
|
This function requires the user to supply the necessary inputs to the model
|
|
|
There is a companion function that labels the output. (BIYOPMTEXT)
|
|
|
The function can be used as either an array function or a single cell function
|
|
|
|
If using it as an array formula highlight 7 cell (either column or row) click on the formula bar and type the formula, and then press Shift-Control-Enter
|
|
|
|
Excel will supply the brackets around the formula.
|
|
|
|
If it is used as a single cell formula the user can enter an optional value which returns a particular value for the option
|
|
|
|
The companion formula BIYOPMTEXT behaves the same way. It returns a label for the output from the BIYOPM function and is provided for convenience in labeling the output.
|
|
|
|
This function differs from BIOPM in that it allows the user to enter the data on an annual basis and the function will adjust data for the user. It also returns an estimate of the Standard Deviation that is implied in the UP and DOWN movements.
|
|
|
|
UP =exp(Standard Deviation*(Years/Jumps)^.5)
|
|
|
DOWN =exp(-Standard Deviation*(Years/Jumps)^.5)
|
|
|
|
This function works backwards, given the Up and Down movements it solves for the Standard Deviation. Since the Up and Down movements can be any number the user believes to accurate there is no requirement that the Standard Deviation estimates will be consistent. Hence the function returns both an Up Standard Deviation and a Down Standard Deviation.
|
|
|
|
The computation mythology relies on the fact that state prices (Qu and Qd) are constant for any given interest rate ®, Up movement (U) and Down (D) movement. Thus the function computes the paths and multiples by the state prices to compute the payoffs in each state and then adds up the payoff to obtain the option value
|
|
|
|
State prices can be computed by the following formulas:
|
|
|
|
Qu
|
|
|
Qd
|
|
|
|
|
= ( R - D )/((1 + R )*( U - D ))
|
|
|
= ( U - R )/((1 + R )*( U - D ))
|
|
|
|
|
The number of paths gets very large quickly. For 4 years and 25 jumps per year the number of possible paths is:
|
|
|
|
1,267,650,600,228,230,000,000,000,000,000
|
|
|
Fortunately the computations can be reasonable efficiently and routine is reasonably fast. However, you might want to consider this in your choice of parameters
|
|
|
In the example below the results using 4 years and 25 jumps returns essentially the same value for the option as BIOPM. Which is what we expect since the inputs are essentially the same.
|
|
|
Here are the inputs and form of the function:
|
|
|
=BIYOPM(S,E,YEARS, JUMPS, UP,DOWN,R,OPTIONAL VALUE)
|
|
|
|
|
|
Row/Col
|
B
|
C
|
D
|
E
|
F
|
G
|
|
|
35
|
Inputs Required:
|
|
|
|
Example Values
|
|
|
36
|
U is the annual possible up movement (decimal value)
|
0.3611
|
|
|
|
37
|
D is the annual down movement (decimal value)
|
|
-0.2653
|
|
|
|
38
|
Years is the number of years (can be a fraction)
|
|
4
|
|
|
|
39
|
Jumps is the number of jumps per year.
|
|
25
|
|
|
|
40
|
S is the initial stock price on which the option is written
|
|
$14.00
|
|
|
|
41
|
R is the Interest rate per year
|
|
|
7.00%
|
|
|
|
42
|
E is the Exercise price
|
|
|
|
$17.50
|
|
|
|
43
|
|
|
|
|
|
|
|
|
44
|
To obtain:
|
Optional Value
|
|
|
|
|
|
45
|
Call Value
|
Default: 1
|
There is no need to use these in the array form of the formula
|
|
46
|
Put Value
|
2
|
|
|
|
|
|
|
47
|
Up state Price
|
3
|
|
|
|
|
|
|
48
|
Down State Price
|
4
|
|
|
|
|
|
|
49
|
Number of Paths
|
5
|
|
|
|
|
|
|
50
|
Up Standard Deviation
|
6
|
These are estimates of the standard deviation given the up and down movement.
|
|
51
|
Down Standard Deviation
|
7
|
|
|
|
|
|
|
52
|
|
|
|
|
|
|
|
|
53
|
|
|
|
|
|
|
|
|
54
|
Examples
|
|
|
|
|
|
|
|
55
|
|
Array function results:
|
Formula in cell to the left
|
|
|
|
|
56
|
Call Value
|
3.6956
|
{=BIYOPM(F40,F42,F38,F39,F36,F37,F41)}
|
|
|
|
57
|
Put Value
|
2.9218
|
{=BIYOPM(F40,F42,F38,F39,F36,F37,F41)}
|
|
|
|
58
|
Up state Price
|
0.5059
|
{=BIYOPM(F40,F42,F38,F39,F36,F37,F41)}
|
|
|
|
59
|
Down State Price
|
0.4913
|
{=BIYOPM(F40,F42,F38,F39,F36,F37,F41)}
|
|
|
|
60
|
Number of Paths
|
1.26765E+30
|
{=BIYOPM(F40,F42,F38,F39,F36,F37,F41)}
|
|
|
|
61
|
Estimated Up STD DEV
|
0.30831
|
{=BIYOPM(F40,F42,F38,F39,F36,F37,F41)}
|
|
|
|
62
|
Estimated Down STD DEV
|
0.30831
|
{=BIYOPM(F40,F42,F38,F39,F36,F37,F41)}
|
|
|
|
63
|
↑
|
|
|
|
|
|
|
|
64
|
The array formula in these cells is:
|
|
|
|
|
|
|
65
|
=BIYOPMtext()
|
|
|
|
|
|
|
|
66
|
You can choose to use the non-array form with same options as BIYOPM
|
|
|
|
|
67
|
|
|
|
|
|
|
|
|
68
|
|
|
|
|
|
|
|
|
69
|
↓
|
|
|
|
|
|
|
|
70
|
|
Single Cell:
|
Formula in cell to the left
|
|
|
|
|
71
|
Call Value
|
3.6956
|
=BIYOPM($F$40,$F$42,$F$38,$F$39,$F$36,$F$37,$F$41)
|
|
72
|
Put Value
|
2.9218
|
=BIYOPM($F$40,$F$42,$F$38,$F$39,$F$36,$F$37,$F$41,C46)
|
|
73
|
Up state Price
|
0.5059
|
=BIYOPM($F$40,$F$42,$F$38,$F$39,$F$36,$F$37,$F$41,C47)
|
|
74
|
Down State Price
|
0.4913
|
=BIYOPM($F$40,$F$42,$F$38,$F$39,$F$36,$F$37,$F$41,C48)
|
|
75
|
Number of Paths
|
1.26765E+30
|
=BIYOPM($F$40,$F$42,$F$38,$F$39,$F$36,$F$37,$F$41,C49)
|
|
76
|
Estimated Up STD DEV
|
0.30831
|
=BIYOPM($F$40,$F$42,$F$38,$F$39,$F$36,$F$37,$F$41,C50)
|
|
77
|
Estimated Down STD DEV
|
0.30831
|
=BIYOPM($F$40,$F$42,$F$38,$F$39,$F$36,$F$37,$F$41,C51)
|
|
78
|
|
|
|
|
|
|
|
|
79
|
|
|
|
|
|
|
|
|
|
