













See also BIYOPM



and BIAOPM






This function calculates an option value based on the Binomial Option Pricing Model. Like BlackScholes 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. (BIOPMTEXT)




The function can be used as either an array function or a single cell function




If using it as an array formula highlight 5 cell (either column or row) click on the formula bar and type the formula, and then press ShiftControlEnter




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 BIOPTEXT behaves the same way. It returns a label for the output from the BIOPM function and is provided for convenience in labeling the output.




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 100 periods 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 100 periods returns essentially the same value for the option as Black Scholes. Which is what we expect since in the limit the two would produce the same answers.



Here are the inputs and form of the function:




=biopm(S,E,Periods,UP,Down,R,Optional Value)












Row/Col

B

C

D

E

F

G

33

Inputs Required:




Example Values

34

U is the possible up movement in any period (decimal value)

6.3603%


35

D is the possible down movement in any period (decimal value)

5.9799%


36

Periods is the number of periods



100


37

S is the initial stock price on which the option is written

$14.00


38

R is the Interest rate and must be for the length of the period *

0.2800%


39

E is the Exercise price




$17.50


40







41

To obtain:

Optional Value




42

Call Value

Default: 1


43

Put Value

2

There is no need to use these in the array form of the formula




44

Up state Price

3




45

Down State Price

4




46

Number of Paths

5




47







48

Examples






49

Array function results:

Formula in cell to the left



50

Call Value

3.6935

{=BIOPM(F37,F39,F36,F34,F35,F38)}


51

Put Value

2.9249

{=BIOPM(F37,F39,F36,F34,F35,F38)}


52

Up state Price

0.5059

{=BIOPM(F37,F39,F36,F34,F35,F38)}


53

Down State Price

0.4913

{=BIOPM(F37,F39,F36,F34,F35,F38)}


54

Number of Paths

1.2677E+30

{=BIOPM(F37,F39,F36,F34,F35,F38)}


55

↑






56

The array formula in these cells is:





57

=biopmtext()






58

You can choose to use the nonarray form with same options as BIOPM


59







60







61

↓






62

Single Cell:

Formula in cell to the left



63

Call Value

3.6935

=BIOPM($F$37,$F$39,$F$36,$F$34,$F$35,$F$38)

64

Put Value

2.9249

=BIOPM($F$37,$F$39,$F$36,$F$34,$F$35,$F$38,C43)

65

Up state Price

0.5059

=BIOPM($F$37,$F$39,$F$36,$F$34,$F$35,$F$38,C44)

66

Down State Price

0.4913

=BIOPM($F$37,$F$39,$F$36,$F$34,$F$35,$F$38,C45)

67

Number of Paths

1.2677E+30

=BIOPM($F$37,$F$39,$F$36,$F$34,$F$35,$F$38,C46)

68







69







70

* Computation of interest rate





71







72

To make this example consistent with the example used for Black Scholes the interest rate used was calculated assuming a four year time period and 25 jumps per year (total of 100).

73

Interest Rate

Formula in cell to the left




74

0.0028

=0.07/25





75







76

If the interest rate used is continuous then the following form should be used


77







78

0.002803924

=EXP(0.07/25)1




79







80

In this case the differences are very small and changes the option value by less than .003 cents


