Mean Reversion Calculation

Starman55

New Member
Joined
Feb 16, 2012
Messages
19
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Re: Mean Reversion Calculation Help Request

My apologies! Saw there were some permission issues with the link. It looks to be accessible now.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top