Excel data analysis 5.0 (Robust Realized Variance)

Here I look at a couple of concepts around the S&P500 Robust Realized Variance (RRV), and its behavior with respect to the S&P500 index.

Why is variance important?

• Implied variance of all strikes gives a much more complete feel for a product's option value, whether it's relatively cheap/expensive. 
• Profit from delta hedging (long gamma) are proportional to returns squared, i.e.                            Delta Hedged P&L = dX^2 * gamma / 2 - transaction costs; where dX = change in underlying
• Variance Swaps, Variance Futures have become quite popular, and these new product valuations are still relatively inefficient compared to the more developed products.

Data set up
I got the daily closing values of S&P500 off yahoo finance (symbol: ^gspc). The data is sorted with the basic stats at the top as mentioned in Excel data analysis 1.0. So in column C, "dX", we have the day to day log returns; 100*ln[X(t)/X(t-1)], where X = latest value, t = latest time step, in this case the day.

Robust Realized Variance (RRV)

Variance for a normal distribution is the average squared differences from the mean. Since we know that returns on stock indices are not represented by normal distributions, robust statistics is more practical. As Median Absolute Deviation is to Standard Deviation, our RRV utilizes median values instead of mean.

Here we can see that I took the 60 day RVV, annualized.

RVV stats

Here we can get a feel for S&P500 with respect to RVV.

It's pretty obvious that the index is more attractive for buyers when RVV is relatively high.

In the next post I will look at using RVV to analyze expected future S&P500 returns.