How to calculate implied volatility with excel

First we go and acquire de data.

On S we put the place for the stock or index or whatever we search for. In this case for example the price of S&P 500 it was 3009.05 today.

Then we select a1 month option with price close to ours, for example 3010

After that we select the interest rate for our risk free .

Finally we have to search for a sigma that makes our observed price igual t the black scholes option price.

When we finish with all then we can calculate the deviation and see how much it can go down and up our stock.

35KB

implied-volatility.xls

Download - Example of implied volatility