Mean Reversion Calculation

[Starman55]

Hello,

First off, appreciate any help as I a have been banging my head on this for some time. I want to see if there is any guidance on how to do a mean reversion forecast in excel. The data points are very simple and are linked below for your reference.

https://docs.google.com/spreadsheets/d/1mOFOyuJOxz4UGIw0Gl4gCEu0JYGVrYR8N3S1OOEcFuc/edit?usp=sharing

If someone would like to sell a home in 10 years, is it possible to forecast a growth percentage that factors in recent market movement in relation to the whole? For example, over the entire sample (1975 - 2018) the IRR is 3.28%. However, over the last 10 years, the growth rate has been 0.8%. So, over the next 10 years, is there a way to forecast a rate of return that will "gravitate" back towards the mean over that period of time?

Thanks and please let me know if there are any other questions.

Montgomery

 

[Starman55]

Re: Mean Reversion Calculation Help Request

My apologies! Saw there were some permission issues with the link. It looks to be accessible now.

 

[joeu2004]

Re: Mean Reversion Calculation Help Request

I'd like to help with any Excel usage questions that you might have. But I do not do other people's homework. Can you demonstrate a modicum of understanding of the problem?

In particular, can you present the mathematical (or Excel) formula that you want to implement in Excel, explaining its terms?

Aside.... Some observations:

1. Technically, you want to "gravitate" toward the CAGR for some period, not an IRR [sic]. You should be using Excel GEOMEAN or the equivalent, not Excel IRR or XIRR.

2. In your Excel file, the CAGR for 1975-2018 should be calculated using the array-entered formula { =GEOMEAN(1+C3:C45)-1 } . Do not type the curly brackets. Press ctrl+shift+Enter instead of just Enter. That results in about 3.36%. I suspect that you array-entered
{ = GEOMEAN(1+C2:C45)-1 } or the equivalent. That results in the incorrect CAGR of about 3.28% [sic] because it interprets empty C2 as zero.

3. This is an Excel forum, not a Google Sheets forum. Some formulas that work in Excel might not work verbatim in Google Sheets. I am not familiar with Google Sheets. But in particular, if ctrl+shift+Enter does not work for you, I believe you can wrap the ARRAYFORMULA function around the Excel formula.

 

[joeu2004]

Re: Mean Reversion Calculation Help Request

On second thought, a google search turned up the following reference, which explains everything that I would have (you need MS Word): http://www2.gsu.edu/~fncdcn/Spring 2005/Estimating and Simulating a Model for Mean Reversion.doc [1]. The document seems to have been written by an instructor; so it seems authoritative. I have not "back tested" or otherwise applied the algorithm to your data. If this is not a class assignment, and if you are not doing this as a personal exercise to study mean reverting, I would suggest that you "chart" the data and consider alternative methods of forecasting. Good luck!


-----
[1] http://www2.gsu.edu/~fncdcn/Spring 2005/Estimating and Simulating a Model for Mean Reversion.doc