










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








