BIAOPM

BIAOPM

BIAOPMTEXT

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. (BIAOPMTEXT)

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

This function differs from BYIOPM in that it allows the user to enter the data on an annual basis and to enter the Standard Deviation from which the function will estimate the UP and DOWN movements consistent with that standard deviation and to enter a single total number of jumps.

UP =exp(Standard Deviation*(Years/Jumps)^.5)

DOWN =exp(-Standard Deviation*(Years/Jumps)^.5)

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 jumps 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

Here are the inputs and form of the function:

=BIAOPM(S,X,T,STD,Jumps,R,Optional Value)

Row/Col

B

C

D

E

F

G

32

Inputs Required:

 

 

 

Example Values

33

STD is the annual Standard Deviation of the stock return

30.83%

 

34

Years  is the number of years (can be a fraction) 

 

0.5

 

35

Jumps is the total number of jumps over the life of the option

100

 

36

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

$14.00

 

37

R is the Interest rate per year

 

 

7.00%

 

38

E is the Exercise price

 

 

 

$17.50

 

39

 

 

 

 

 

 

40

To obtain:

Optional Value

 

 

 

41

Call Value

Default:  1

 

42

Put Value

2

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

 

 

 

43

Up state Price

3

 

 

 

44

Down State Price

4

 

 

 

45

Up Movement

5

 

 

 

46

Down Movement

6

 

 

 

47

Number of Paths

7

 

 

 

48

 

 

 

 

 

 

49

 

 

 

 

 

 

50

Examples

 

 

 

 

 

51

 

Array function results:

Formula in cell to the left

 

 

52

Call Value

0.3573

{=BIAOPM(F36,F38,F34,F33,F35,F37)}

 

53

Put Value

3.2554

{=BIAOPM(F36,F38,F34,F33,F35,F37)}

 

54

Up state Price

0.5024

{=BIAOPM(F36,F38,F34,F33,F35,F37)}

 

55

Down State Price

0.4972

{=BIAOPM(F36,F38,F34,F33,F35,F37)}

 

56

Up Movement

0.0220

{=BIAOPM(F36,F38,F34,F33,F35,F37)}

 

57

Down Movement

-0.0216

{=BIAOPM(F36,F38,F34,F33,F35,F37)}

 

58

Number of Paths

1.26765E+30

{=BIAOPM(F36,F38,F34,F33,F35,F37)}

 

59

 

 

 

 

 

60

The array formula in these cells is:

 

 

 

 

61

=BIAOPMtext()

 

 

 

 

 

62

You can choose to use the non-array form with same options as BIAOPM

 

 

63

 

 

 

 

 

 

64

 

 

 

 

 

 

65

 

 

 

 

 

66

 

Single Cell:

Formula in cell to the left

 

 

67

Call Value

0.3573

=BIAOPM($F$36,$F$38,$F$34,$F$33,$F$35,$F$37)

68

Put Value

3.2554

=BIAOPM($F$36,$F$38,$F$34,$F$33,$F$35,$F$37,C42)

69

Up state Price

0.5024

=BIAOPM($F$36,$F$38,$F$34,$F$33,$F$35,$F$37,C43)

70

Down State Price

0.4972

=BIAOPM($F$36,$F$38,$F$34,$F$33,$F$35,$F$37,C44)

71

Up Movement

0.0220

=BIAOPM($F$36,$F$38,$F$34,$F$33,$F$35,$F$37,C45)

72

Down Movement

-0.0216

=BIAOPM($F$36,$F$38,$F$34,$F$33,$F$35,$F$37,C46)

73

Number of Paths

1.26765E+30

=BIAOPM($F$36,$F$38,$F$34,$F$33,$F$35,$F$37,C47)

74

 

 

 

 

 

 

[FinPak] [Getting FinPak] [Installation] [Help] [Fin Functions] [Simulation] [The Greeks] [Array Functions] [Root Finding]

Copyright 2003 Pieter A. Vandenberg All rights reserved.