Wednesday, January 2, 2013

Excel data analysis 7.0 Realized Vol Smile

Here we look at estimating the volatility smile (AKA Vol Skew) off realized vol. This gives the trader a point of reference while initiating a trade based on the current implied vol smile.


Underlying: NDX (Nasdaq100 Index)
I am using the end of day adjusted values from Yahoo Finance.

On deriving realized vol, see this earlier post (Excel Data Analysis 3.0)

Moving Realized Vol Smile

For this example I've used the last 6 months of realized vol against NDX values in a Scatter Plot.
X- Axis: NDX values
Y- Axis: 20-day Realized Volatility

Quadratic Regression gave the best fit/model, having the highest R-squared at 0.53

There it is.

On running a Quadratic Regression in Excel


0 Reflections: