|
|
 |
|
|
 |
|
|
|
|
|
|
This function calculates the implied variance of a stock's return given a Call Option price and the other parameters of the Black Scholes Option Pricing Model.
|
|
|
|
|
|
|
If you have the Put Price convert it to a Call Price by using Put/Call Parity
|
|
|
|
Call Price = PUT Price + Stock Price - Present Value (Exercise Price)
|
|
|
|
This function requires the user to supply the necessary inputs to the model
|
|
|
|
The function returns a single value, the estimate of the standard deviation. (as a decimal)
|
|
|
|
Since the function uses a numerical search method there is some chance that the function will not return a value. This may be caused by the fact that there is not an answer or that limit on attempts has been reached. You can try to increase the attempt limit by setting tries to higher number. The program actually uses two limits to try to find an answer. In general the over-all limit is 50 tries. To increase this limit you set Tries to a number higher than this.
|
|
|
|
In most cases the computations will cease when the estimate of the error is less than or equal to .00001. Increasing the number of tries does not affect this limit.
|
|
|
|
If the function is unable to find a solution in the given tries then one of the following error message may appear and the function will return #Value (See Search Algorithms)
|
|
|
|
(Value for X has no meaning for the user.)
|
|
|
Error X Error Code 1 Does not converge
|
|
|
Error X Error Code 2 Does not converge
|
|
|
Error 0 Error Code 3 Does not converge.
|
|
|
|
|
This means that code was not able to bracket the values, most likely caused by unreasonable inputs
|
|
|
This means that code was not able to reduce the error to the limit after the value was bracketed, this error is most likely to correct if you increase the number of tries
|
|
|
This error is generated when the CallPrice exceeds the Stock Price which is not a reasonable situation.
|
|
|
|
|
|
|
Row/Col
|
B
|
C
|
D
|
E
|
F
|
G
|
|
39
|
Inputs Required:
|
|
|
|
Example Values
|
|
40
|
DIV is the Dividend Yield of the stock ($Dividend/$Stock Price)**
|
2.86%
|
|
|
41
|
S is the current price of the stock
|
|
|
$14.00
|
|
|
42
|
E is the call exercise price
|
|
|
|
$17.50
|
|
|
43
|
T is time to expiration of the option in years
|
|
4
|
|
|
44
|
r is the riskless, continuously compounded, interest rate
|
6.77%
|
|
|
45
|
CallPrice is the market price of the call option
|
|
$2.71
|
|
|
46
|
CC is optional and can be set to "yes" (Quotes required) and will convert R to continuous
|
|
47
|
rate of return, if it was entered as a discrete rate
|
|
|
|
|
48
|
Tries use optional this parameter to increase the number of attempts to find an answer
|
|
49
|
(**for convenience the function will convert Dividend yield to a continuous yield automatically)
|
|
50
|
|
|
|
|
|
|
|
51
|
Examples
|
|
|
|
|
|
|
52
|
Single Cell:
|
Formula in cell to left
|
|
|
|
|
53
|
30.83%
|
=BSVOPM($F$41,$F$42,$F$43,$F$45,$F$44,$F$40)
|
|
|
54
|
30.83%
|
=BSVOPM($F$41,$F$42,$F$43,$F$45,$F$44,$F$40,,50)
|
|
|
55
|
34.50%
|
=BSVOPM($F$41,$F$42,$F$43,$F$45,$F$44,$F$40,"Yes",50)
|
|
56
|
|
|
|
|
|
|
|
|
