-
Step 1
Gather the information needed to perform the calculation. You will need to know the stock price, exercise price, risk-free rate, time expiration (theta), volatility (vol) and dividend yield.
-
Step 2
Download the spreadsheet listed in Resources under Black-Scholes Merton Implied Volatility Function. You will need to open this spreadsheet in MS Excel. Do not be alarmed by all the variables. The following steps will walk you through the input screen.
-
Step 3
Open the spreadsheet you plan to use. That is, you should now have two spreadsheets open. In the new spreadsheet, open "Tools," then "Macros," then "Visual Basic Editor." Then choose "View" and then "Project Explorer." Drag and drop the item called Procedures (in Modules) in the folder of the project you are working on. The implied volatility function should now be accessible in your suite of Excel functions.
-
Step 4
Ascertain if you are looking for the volatility on a call or put option. A call option is the option to buy a stock, whereas a put option is the option to sell. You can choose the correct text from a pull-down menu.
-
Step 5
Input the information gathered in Step 1 into each of the functional areas, starting with Asset Price (stock price) and ending with Maximum error. In general, Maximum error should be no more than 1 percent.
-
Step 6
Check for accuracy by comparing the "Black-Scholes model price at this volatility" (Cell C16) to the "Market Price" (Cell B17). You can also check results with a non-Excel-based calculator. See Resources for a useful link.
