Black-Scholes Implied Volatility Function
Asset price (S0) 125.9375 Implied volatility #NAME?
Exercise price (X) 125
Risk-free rate (rc) 4.46%
Time to Expiration (T) 0.0959
Dividend yield (dc) 0.00% Check result:
Select call or put call Black-Scholes model price at this volatility:
Market price 13.5 #NAME?
Maximum error 0.01
This program is an Excel function that calculates the implied volatility.
It is used as follows:
where the arguments are references to cells or range names. Enter the stock price, exercise
price, risk-free rate, time to expiration, volatility, and dividend yield. The "choice" variable is a
text variable that can be only the word "call" or "put", which can be selected by using the pull
down menu as indicated. The maximum error you specify determines how close the option price
computed using the implied volatility must be to the actual option price.
The procedure uses an algorithm presented in S. Manaster and G. Koehler, "The Calculation of
Implied Variances from the Black-Scholes Model: A Note," The Journal of Finance 37 (March,
This program also uses another function called BlackScholesFunction, which calculates the
Black-Scholes value directly in a single cell, although this is provided only as a check and is not
necessary for calculating the implied volatility.
Because this procedure is a function, you can drag and drop it into any spreadsheet and make it
available as one of your standard Excel functions. Open this spreadsheet as well as the
spreadsheet in which you wish to use this procedure. Select Tools/Macro/Visual Basic Editor.
Then choose View/Project Explorer. You will see a folder called Modules. Drag and drop the
item called Procedures into the folder of the project you are working on. This will make the
implied volatility procedure available as an Excel User-Defined Function.
Written by Don M. Chance, email@example.com
For use with An Introduction to Derivatives and Risk
Management, 6th ed.
(Mason, Ohio: South-Western Thomson, Inc., 2004)
Last updated: 7/7/03