|
|
 |
|
|
|
 |
|
|
|
|
|
|
This function calculates the value of an option using the Black-Scholes Option Pricing Model on a dividend paying stock.
|
|
|
|
|
|
|
|
|
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. (BSDOPMTEXT)
|
|
|
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 BSDOPM function and is provided for convenience in labeling the output.
|
|
|
Here are the inputs and form of the function:
|
|
|
|
=BSDOPM(S,E,T,STD,R,DIV,Optional Value,CC)
|
|
|
|
Example Inputs:
|
|
|
|
|
|
|
Row/Col
|
B
|
C
|
D
|
E
|
F
|
G
|
|
18
|
Inputs Required:
|
|
|
Example Values
|
|
|
19
|
DIV is the Dividend Yield of the stock ($Dividend/$Stock Price)**
|
2.86%
|
|
|
20
|
S is the current price of the stock
|
|
|
$14.00
|
|
|
21
|
E is the call exercise price
|
|
|
$17.50
|
|
|
22
|
T is time to expiration of the option in years
|
|
4
|
|
|
23
|
R is the riskless, continuously compounded, interest rate
|
6.77%
|
|
|
24
|
STD is the Standard Deviation of the rate of return on which the option
|
30.83%
|
|
|
25
|
is written
|
|
|
|
|
|
|
26
|
CC is optional and can be set to "yes" (Quotes required) and will convert R to continuous
|
|
|
27
|
rate of return, if it was entered as a discrete rate
|
|
|
|
28
|
(**for convenience the function will convert Dividend yield to a continuous yield automatically)
|
|
|
29
|
|
|
|
|
|
|
|
30
|
To obtain:
|
Optional Value
|
|
|
|
|
31
|
Call Value
|
Default: 1
|
|
|
|
|
|
32
|
Put Value
|
2
|
|
|
33
|
Call Delta (Hedge Ratio)
|
3
|
There is no need to use these in the array form of the formula
|
|
|
|
|
34
|
Borrow/Lend $
|
4
|
|
|
|
|
35
|
Put Delta
|
5
|
|
|
|
|
36
|
Gamma
|
6
|
|
|
|
|
37
|
Vega
|
7
|
|
|
|
|
|
38
|
Call Theta
|
8
|
|
|
|
|
|
39
|
Put Theta
|
9
|
|
|
|
|
|
40
|
Call Rho
|
10
|
|
|
|
|
|
41
|
Put Rho
|
11
|
|
|
|
|
|
42
|
|
|
|
|
|
|
|
43
|
Delta: Is the impact on the option price of a change in the share price
|
|
|
|
44
|
Borrow/Lend: Amount necessary borrow/lend in replicating the option
|
|
|
|
45
|
Gamma: Is the impact on Delta of a price change in the share price
|
|
|
|
46
|
Vega: The impact on the option price for a change in the standard deviation
|
|
|
|
47
|
Theta: The impact on the option price for a small change in the time to maturity
|
|
|
48
|
Rho: The impact on the option price for a change in the risk free rate
|
|
|
|
49
|
|
|
|
|
|
|
|
50
|
Examples
|
|
|
|
|
|
|
51
|
Array function results:
|
Formula in cell to left
|
|
|
|
52
|
|
|
|
|
|
|
|
53
|
Call Value
|
2.715
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
54
|
Put Value
|
3.577
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
55
|
Call Delta (Hedge Ratio)
|
0.517
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
56
|
Borrow/Lend $
|
4.519
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
57
|
Put Delta
|
-0.375
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
58
|
Gamma
|
0.051
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
59
|
Vega
|
9.767
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
60
|
Call Theta
|
-0.475
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
61
|
Put Theta
|
0.071
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
62
|
Call Rho
|
18.075
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
63
|
Put Rho
|
-35.327
|
{=BSDOPM(F20,F21,F22,F24,F23,F19)}
|
|
|
64
|
↑
|
|
|
|
|
|
|
65
|
The array formula in these cells is:
|
|
|
|
|
|
66
|
=BSDOPMtext()
|
|
|
|
|
|
|
67
|
|
|
|
|
68
|
You can choose to use the non-array form with same options as BSDOPM
|
|
|
|
|
|
|
69
|
|
|
|
|
|
|
70
|
↓
|
|
|
|
|
|
|
71
|
|
|
|
|
|
|
|
72
|
Single Cell:
|
Formula in cell to left
|
|
|
|
73
|
|
|
|
|
|
|
|
74
|
Call Value
|
2.715
|
=BSDOPM(F20,F21,F22,F24,F23,F19)
|
|
|
75
|
Put Value
|
3.577
|
=BSDOPM(F20,F21,F22,F24,F23,F19,C32)
|
|
|
76
|
Call Delta (Hedge Ratio)
|
0.517
|
=BSDOPM(F20,F21,F22,F24,F23,F19,C33)
|
|
|
77
|
Borrow/Lend $
|
4.519
|
=BSDOPM(F20,F21,F22,F24,F23,F19,C34)
|
|
|
78
|
Put Delta
|
-0.375
|
=BSDOPM(F20,F21,F22,F24,F23,F19,C35)
|
|
|
79
|
Gamma
|
0.051
|
=BSDOPM(F20,F21,F22,F24,F23,F19,C36)
|
|
|
80
|
Vega
|
9.767
|
=BSDOPM(F20,F21,F22,F24,F23,F19,C37)
|
|
|
81
|
Call Theta
|
-0.475
|
=BSDOPM(F20,F21,F22,F24,F23,F19,C38)
|
|
|
82
|
Put Theta
|
0.071
|
=BSDOPM(F20,F21,F22,F24,F23,F19,C39)
|
|
|
83
|
Call Rho
|
18.075
|
=BSDOPM(F20,F21,F22,F24,F23,F19,C40)
|
|
|
84
|
Put Rho
|
-35.327
|
=BSDOPM(F20,F21,F22,F24,F23,F19,C41)
|
|
|
|
↑
|
|
|
|
|
|
|
|
formula in cell b84 =bsdopmtext(C41)
|
|
|
|
|
|
|
