Black/Scholes Option Pricing

BSOPM

BSOPMTEXT

See also BSDOPM

This function calculates the value of an option using the Black-Scholes Option Pricing Model.

It also calculates a variety of other characteristics of the option

This function requires the user to supply the necessary inputs to the model

There is a companion function that labels the output. (BSOPMTEXT)

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

If using it as an array formula highlight 11 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 BSOPTEXT behaves the same way. It returns a label for the output from the BSOPM function and is provided for convenience in labeling the output.

Here are the inputs and form of the function:

=BSOPM(S,E,T,STD,R, Optional Value,CC)

Example Inputs:

Row/Col

B

C

D

E

F

G

18

Inputs Required:

 

 

Example Values

 

19

S  is the current price of the stock

 

 

$14.00

 

20

E    is the call exercise price

 

 

$17.50

 

21

T   is time to expiration of the option in years

 

4

 

22

R    is the riskless, continuously compounded, interest rate

6.77%

 

23

STD is the Standard Deviation of the rate of return on which the option

30.83%

 

24

       is written

 

 

 

 

 

25

CC is optional and can be set to "yes" (Quotes required) will convert R to continuous

 

26

      rate of return, if it was entered as a discrete rate

 

 

27

 

 

 

 

 

 

28

To obtain:

Optional Value

 

 

 

29

Call Value

Default:  1

 

 

 

 

30

Put Value

2

 

31

Call Delta (Hedge Ratio)

3

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

 

 

 

32

Borrow/Lend $

4

 

 

 

33

Put Delta

5

 

 

 

34

Gamma

6

 

 

 

35

Vega

7

 

 

 

36

Call Theta

8

 

 

 

 

37

Put Theta

9

 

 

 

 

38

Call Rho

10

 

 

 

 

39

Put Rho

11

 

 

 

 

40

 

 

 

 

 

 

41

Delta: Is the impact on the option price of a change in the share price

 

 

42

Borrow/Lend: Amount necessary borrow/lend in replicating the option

 

 

43

Gamma: Is the impact on Delta of a price change in the share price

 

 

44

Vega: The impact on the option price for a change in the standard deviation

 

45

Theta: The impact on the option price for a small change in the time to maturity

 

46

Rho: The impact on the option price for a change in the risk free rate

 

 

47

 

 

 

 

 

 

48

Examples

 

 

 

 

 

49

Array function results:

Formula in cell to left

 

 

50

 

 

 

 

 

 

51

Call Value

3.646

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

52

Put Value

2.996

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

53

Call Delta (Hedge Ratio)

0.650

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

54

Borrow/Lend $

5.454

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

55

Put Delta

-0.350

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

56

Gamma

0.043

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

57

Vega

10.371

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

58

Call Theta

-0.769

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

59

Put Theta

0.135

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

60

Call Rho

21.817

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

61

Put Rho

-31.585

{=BSOPM(F19,F20,F21,F23,F22)}

 

 

62

 

 

 

 

 

63

The array formula in these cells is:

 

 

 

 

64

=bsopmtext()

 

 

 

 

 

65

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

 

 

 

 

 

66

 

 

 

 

 

67

 

 

 

 

 

68

 

 

 

 

 

69

 

 

 

 

 

 

70

 

Single Cell:

Formula in cell to left

 

 

71

 

 

 

 

 

 

72

Call Value

3.646

=BSOPM(F19,F20,F21,F23,F22)

 

 

73

Put Value

2.996

=BSOPM(F19,F20,F21,F23,F22,C30)

 

 

74

Call Delta (Hedge Ratio)

0.650

=BSOPM(F19,F20,F21,F23,F22,C31)

 

 

75

Borrow/Lend $

5.454

=BSOPM(F19,F20,F21,F23,F22,C32)

 

 

76

Put Delta

-0.350

=BSOPM(F19,F20,F21,F23,F22,C33)

 

 

77

Gamma

0.043

=BSOPM(F19,F20,F21,F23,F22,C34)

 

 

78

Vega

10.371

=BSOPM(F19,F20,F21,F23,F22,C35)

 

 

79

Call Theta

-0.769

=BSOPM(F19,F20,F21,F23,F22,C36)

 

 

80

Put Theta

0.135

=BSOPM(F19,F20,F21,F23,F22,C37)

 

 

81

Call Rho

21.817

=BSOPM(F19,F20,F21,F23,F22,C38)

 

 

82

Put Rho

-31.585

=BSOPM(F19,F20,F21,F23,F22,C39)

 

 

 

 

 

 

 

 

 

formula in cell b82 =bsopmtext(C39)

 

 

 

 

 

 

 

 

 

 

 

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

Copyright 2003 Pieter A. Vandenberg All rights reserved.