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