Building a Model for Real Estate



In my last post I discussed the most crucial, most criticized and most remembered part of putting together a mathematical model: the assumptions. Today we are going to open up Excel (or some other spreadsheet program) and begin the fun work of making the model work.

But first I want to go back really quickly and respond to a comment made in my last post. While I supported most of my assumptions with historical evidence, I didn't provide links to one crucial assumption, the rate I expect house prices to appreciate. I claimed that historically housing prices were relatively close (within a percent or two) of inflation. The simplest evidence of home prices against inflation can be found in the US. Census Bureau:

Median US Home Price: Adjusted to 2000 Dollars
1940195019601970198019902000
$30,600$44,600$58,600$65,300$93,400$101,100$119,600

Extracting from that data you can determine that the annualized rate of return (adjusted for inflation) on a house bought in 1950 (and sold in 2000) is 1.99%, the annual return for a house bought in 1960 was 1.8%, for a house bought in 1970 is 2.04%. Most calculations regarding home data tends to omit the 1940's because of the effect World War 2 had on skewing housing statistics. So you can see that it's safe bet that housing since World War 2 has has a somewhat stable return over long periods of time of around 2%.

We're going to forget the original scenario of Jim and Pablo for a minute (which is quite complicated) and instead take a look at this scenario. When I quoted my number of 3% I hadn't really done any calculations to justify it, other than looking at a chart or two. But it's a great example of how we could use a model to figure out where housing currently is (according to historical trends) and what will happen to it over the next 20 years.

So all of the data that we extract came from 2000 or before (which makes sense because it came from the Census, who won't collect more data until 2010). So the current bubble isn't part of our records. Let's see where it fits, and how to do an extremely simple model of home prices. Since it's currently the year 2006, doing calculations using 2000 dollars doesn't make much sense, so first let's update our figures to 2006 dollars. The formula to do this is simple:

2006 value = 2000 value * (2001 inflation) * (2002 inflation) * (2003 inflation)....

We can grab the inflation numbers from the US Bureau of Labor Statistics (the inflation rate is the % change, located in the far right column). In other words, the median home price in 2000, adjusted for 2002 dollars is equal to:

$119,700 * 1.028 (inflation in 2001 was 2.8%) * 1.016 (the rate of inflation in 2002 was 1.6%) = $125,020

Extrapolating this up to 2006 dollars means that adjusted to today's dollars, the median home in 2000 was worth $135,700. If you are still trying to understand why we are adjusting for inflation, just remember that the value of a dollar changes over time. Changing from 2000 dollars to 2006 dollars may not seem significant, but a movie ticket that cost $0.25 in 1924 would cost $6.40 in 2006 (it must be a matinee). The bottom line is that your dollar isn't worth as much today as it was 5 years ago, so hopefully you have more dollars lying around.

Open up a spread sheet and choose a random square over to the right side of the sheet and insert a 1.02 there. That's our rate of return (adjusted for inflation). Then starting a column on the left side put our number of $135,700 (you can also put the year, 2000, to make it easier to keep track).

Right below the home value, we want to create a little formula that takes our 2000 value, and multiplies it by 1.02 (to account for our 2% appreciation, adjusted for inflation). If you do this right, you should see a value very close to $138,414 in that box. Then for 2002, we want to multiply our 2001 figure by 1.02 again. And so on, until we get to 2006.

In the above photo you can see in the fx box (at the top) the formula I have for square B4. What this tells us is that according to the model we just made, the median US home right now should be worth about $152,820. With the current median price of a US home at about $217,900, we can see that our model thinks that the US home market is currently over-priced by about 27%. (In other words, if the median home fell by 27% tomorrow, the market would be extremely close to our model.

Now it's time to point out the potential flaw in our model. The first was discussed at great length in our last post, the assumptions. Because this model is extremely simple, we only have one real assumption, that US home prices will appreciate at about 2% over inflation. If that's wrong, the entire basis of our model is off. Whether or not it's a reasonable assumption to make is left in your hands to decide.

So where is it going in the future? We can mock up a simple model for this as well. Let's assume that the average rate of inflation from 2000 to 2020 in 3% and that homes will average a 2% gain over that. Therefore homes will increase in value by 5% a year. We can go back to our spreadsheet, update the percent gain and then extrapolate the model to the year 2020:

In addition we can make ourselves the nice little chart that we saw up at the top of the post. So using our model we can predict the following:

Median US Home Price: Modelled
20002006201020152020
$119,600$160,275$194,815$248,639$317,334

With the current median price of a US home at about $217,900, we can see that to reach a predicted value of about $317,600 in 2020, we'll be looking at growth of 2.77% each year (that's 2.77% flat out, not on top of inflation). That's actually pretty damn close to my original estimate of 3% a year.

Now about annualized returns, this doesn't mean that housing will go about by close to 2.77% this year and close to 2.77% next year and so on. The yearly fluctuations can be tremendous. Let's pretend that you hold a stock that you bought for $100. It gets some good press and gains 100% that year (ending at $200). The following year one of their products goes bad and they lose 25% (bringing it back to $150). Over the two year you held the stock, you gain 50%, which is an annual gain of about 22.5%. (note that it's not 25%. Due to the effects of compounding interest you can't just divide the total gain by the number of years you held it).

Housing is the same way as stocks. One year it may do well, the next it may not. We can get to our annualized gain of 2.77% over the next 14 years in many ways. Maybe it will climb 8% in 2007, maybe it will fall 30%. The model we constructed simply suggests that if you buy a house today and sell it in 2020 it will have appreciated by just less than 3% a year.

One final flaw to point out, this model uses national data which is nearly useless in real estate investing. Every local real estate market is different and every one could have it's own model made. While I would expect the US average to somewhat follow this model, it's practically impossible to invest in the US real estate average. If I think that stocks as a whole are going up by 20% in the next 2 years, I can invest on that theory by buying shares of an S&P 500
index fund. I cannot do the same with real estate.

That's both good and bad news. Some markets, like Phoenix, Washington DC and most of California are probably going to perform far worse than this model over the next14 years because they were pushing the national median up. Other places, such as most of the South and Midwest, could very likely outperform this model.

You could make a similar model yourself by looking up sale histories or appraisal histories in your town (both are public knowledge, and usually on the web at the county assessor's office) and determine if your market is currently above the predicted values or below.

So that's an extremely simple model. The next post I'm am probably going to talk about bit about marriage and money (since I am getting married a week from Saturday), and then I'll get back to dealing with our original question of who performs better, Jim or Pablo?

Ciao,
The Landlord

4 comments:

rentboy said...

I can answer the topic of marriage and money. If you want any money DO NOT get married. Simple as that.

Anonymous said...

酒店經紀 酒店打工 酒店工作 酒店上班 酒店兼差 酒店兼職 打工兼差 打工兼職 台北酒店 酒店應徵 禮服酒店 酒店 經紀 打工 兼差

Land Surveyor Alabama said...

Your screen shots of excel in your blog is looking nice.Your blog content also good.Thanks to post great article.

Ryan Ace said...

Your screen shots of excel in your blog is looking nice.Your blog content also good.Thanks to post great article.
Land surveyor alabama