Implied volatility is the most crucial component on the Black Scholes options pricing model. It is also the most subjective and therefore the area where we are most able to gain an edge. But how do you calculate implied volatility? Thankfully I came across a really cool excel file the other day that calculates this for you…. You know you’re a nerd when you get excited about an excel file….

The spreadsheet was created by Samir from Investexcel.net. I’m looking forward to delving into some of the other offerings he has on his site such as this one.

With the spreadsheet you can alter the volatility rate, and then calculate the new call and puts values. As I said, very cool….

*Note that the Excel file must be used as a 97-2003 workbook.*

You can adjust any variable in the parameters section. For example, your scenario might be that you expect volatility to rise from 0.20 to 0.23 over the next 5 days. You would change the volatility value and also the expiry time to take into account the passage of 5 days, then using the Goal Seek function in excel, calculate the option values. Note that this designed for European options, not American options.

Here are the instructions for using the spreadsheet as provided by Samir. It might seem complicated at first glance, but really it is very simple, so even if you are not an excel whiz, you should still be able to figure it out.

Step 1. In the spreadsheet, enter the Spot (stock) price, Strike price, risk free rate and Expiry time. Also, enter an initial guess value for the volatility (this will give you an initial Call price that is refined in the next step)

Step 2. Go to Data>What If Analysis>Goal Seek. Set the Call value to 30 (cell E5 in the spreadsheet) by changing the volatility (cell B8 in the spreadsheet).

Step 3. Click OK.

You should find that volatility has been updated to 0.32 to reflect the desired Call price of 30.

The spreadsheet also gives you other cool data such as the change in greeks for a given change in volatility, time to expiry, stock price etc. Simply, awesome stuff, tip of the cap to you Samir!

To instantly receive a copy of the spreadsheet, just put in your email below and it will be automatically sent to you.

## Leave A Reply (1 comment So Far)

dw

7 months ago

The spreadsheet was not sent automatically.

Please send it to me manually…

Thanks!