Excel Data Analysis 3.1 (Volatility Filtering)

So here we apply a realized volatility (RV) filter as a watered down, simple form of quantitative risk management, to hopefully lower the negative swings of an invested asset. Let’s use the REIT, IYR again for this example. 

Cumulative Returns

Let’s create a column in our spreadsheet for Cumulative Returns, Rcumu, where we add up all the day to day log returns. Excel formula:

To chart a buy and hold return of IYR without having to include quartile numbers, we can scroll all the way down, highlight cell “A10”, then hold Shift and left click cell “B3134”.  Then at the top go to Insert -> Line -> 2D lines. 

And we can see that return off a purely buy and hold scheme would have experienced extremely high volatility. 

Let’s see if we can reduce the risk with a RV filter. The idea is to make it so that no position is held if the RV becomes greater than a certain threshold. In the example worksheet, I’ve used the cell “I3” for the threshold, and used 50% as a starting point. Created a new column, Rcumu*, for the cumulative returns based on the volatility threshold. 

Excel formulas  

 

Now we can compare the cumulative returns between with and without the 50% volatility filter.

And we can see that for this asset, in the sample period, the volatility filter reduced the buy and hold unrealized swings (risk), and increased over all expected return over time.