Bubblizer, a Request for Comments

Current Version:  1.2.4 (2006.04.25).  Release notes.
Download this version to be sure you have no errors.
Next Version Expected:  *none planned*
Password to unlock the spreadsheet:  cap20

9/25/2007: A number of folks have written to inform me that the spreadsheet does not operate properly in newer versions of Excel.  Rather than update the model right now I'm just making the password available to everyone (above).  I may get around to refactoring the model to operate properly sometime, but probably not very soon.

I offer the Bubblizer, an Excel spreadsheet model intended to help everyday folks rationally approach a home buying decision in today's uncertain bubble environment.  I am requesting comments, criticisms and improvements for this model.  Once this spreadsheet version of the Bubblizer is stable, I intend to create a web version (which does not require Excel, but is based upon the same model).

Please share the link to this article with your more financially and quantitatively minded colleagues and friends.

The Basis for this Model:

This model was inspired by the approach taken in the HSBC publication, A Froth-Finding Mission, Detecting US Housing Bubbles, and accompanying spreadsheet model.  HSBC forwarded a reasonable, consistent macroeconomic model for determining the existence and degree of US housing bubbles.  What I have done is distill their approach to analyze a specific, individual home buying decision.

This model attempts to take into account all relevant opportunity costs as well as expected inflation.  It then computes the price at which the home buyer would need to sell her home after her expected holding period (how long she'll live there) in order to break even in real terms.  The implied nominal return based on price appreciation and the implied annual return based upon the IRR are also computed.  Finally, some extra information is provided in order to help the concerned potential buyer figure out if she can afford the prospective home.

The Need for this Model:

This model is needed as a counterweight to the increasingly aggressive and often desperate advertising and advice being given to everyday home buyers by real-estate agents, mortgage brokers and various real-estate industry groups.  Worse, the popular media has failed to provide any critical analysis of the real costs faced by current potential home buyers in high-priced bubble markets.

This model attempts to take some basic inputs and give prospective home buyers a few simple numbers as outputs.  The home buyer should be able to look at these numbers and quickly be able to determine if she believes them reasonable.  This model makes no explicit value judgments.  Rather, it simply shows the user what price she will have to sell her home for in the future to justify the purchase on strictly financial terms.

The model also provides a number of optional inputs, allowing more sophisticated or exceptional users to express specific beliefs about the future (for example, future inflation) which differ from the HSBC findings.  Further, the user can capture the value of "intangibles" as a premium she would be willing to pay in excess of the strictly financial solution.  She can also plan for real appreciation (in excess of real break even), perhaps to plan for a home equity withdrawal or post-occupancy use of equity profits for consumption purposes (for example, college tuition for a child, retirement, etc.).

The inputs in the spreadsheet are fully annotated, and are intended to provide a clear explanation to the home buyer about how they work in the model.

The Current Limitations of this Model:

• Only fixed-rate, standard, US-style, mortgages are handled.
• The mortgage period must be between 10 and 30 years, inclusively.
• Holding periods beyond 30 years are not handled (because at this point the home would be fully owned, and this would require a two-step model).
• The buyer's income is assumed to increase at the inflation rate.  The model does not directly accommodate expected future income growth in excess of inflation.
• Rent is assumed to increase at the inflation rate.  Micro economic rent factors are not captured.

The Next Phase:

After this spreadsheet version of this model has stabilized and all acceptable comments incorporated, the next step will be to implement it as a web application.  The purpose of this will be to reach a wide audience with what should be a very useful tool.  Prospective home buyers can use this tool to educate themselves.  It should provide them some defense against falling prey to hyper-aggressive sales tactics being deployed against them in this uncertain, "frothy" housing market.

Contributions By:

Paul Ashby (pahby@media.mit.edu)

Comments

[randolfe]

The spreadsheet requires a couple of XLA (Excel add-ins) to run, which should be installed in your version of Excel by default. One is to allow VBA to call Solver functions programmatically (Solver.XLA), and the other is the basic VBA add-in.

If you have any other problems opening the sheet post them here or email me (email on the right side bar under "Online Status").

The file has been virus checked with Norton 2005.

[randolfe]

If you wish to directly link to the Excel file use the URL:

http://randolfe.typepad.com/Resources/Bubblizer.xls

I ask that you not copy it to your site until it is stable, so that you can be sure you always are linking to the most recent version.

If you wish to trackback to this article, use the trackback URL as listed above.

[randolfe]

Also, if you want the password to unprotect the sheets, email me so I know who you are (and so I can reasonably expect to get an email back with your error fixes, additions and such included).

I would be especially grateful for interface design tips. I am not an interface designer, as is easily seen by my color choices.

[tjrsfca]

Randolfe,

This is a thoughtful model! You take into account alternate uses of funds as well as other variables not usually mentioned in the sales-speak of mortgages and the like.

As someone who currently owns a home, I was able to figure out how to jive the inputs with my existing bank note and after I pressed the button I found out how much the house would have to sell for in order for me to make a certain profit.

This is helpful stuff, but to my rather simple mind this does not help me understand my investment and real estate bublles. I am downloading the HSBC paper, but for time-crunched people I wonder if there is an easier way to be able to see how unrealistic or realistic a future scenario is compared with historical trends.

I will allow that I might have completely missed something. What say you?

[HARM]

Bravo, Randy.

Quite a nice little tool --and the default AGI & down payment are exactly one HaHa! Sweeet!

[randolfe]

Definition: The "HaHa" unit metric

This nomenclature comes from the Patrick.net blog, where a regular poster's handle is "HaHa", and he continually reminds us that $150K per year is a regular salary in the Bay Area (which is probably not true, but we still chose this amount as a base unit of measure for discussing the housing bubble).

Here is the precise definition:

For current purposes, 1HaHa = USD 150,000.

The precise definition of 1HaHa is 3x National median family income*, for which HaHa (after which the unit of measure is named) stands as a reasonable substitute. Therefore, we may peg the unit of 1HaHa to HaHa himself so long as:

1) HaHa remains in the state of California; preferably in the San Francisco Bay Area;
2) HaHa’s family median income remains within alpha=.05 of the predicted Bay Area median income level;
3) HaHa is still living.

At such time as HaHa becomes disqualified, the base formula may be still used to determine the HaHa unit of measure or a more suitable peg may be established which captures the spirit and intent of the measure.**

*Note that 1HaHa is a relative measure vis-a-vis National median family income compared to SFBA median family income. Therefore, the 3x multiplier will also be a dynamic variable in this system of measure.

**It is for the reasons of this inherit complexity that we prefer to us the HaHa measure as shortcut nomenclature when discussing real-estate issues on this blog. It’s also funny. “

[HARM]

I played around with the Bubblizer a little, and noticed a couple of small bugs:

1. You can’t get an IAR figure if you hold property longer than 15 years (shows as “#DIV/0″)
2. The columns on the right don’t self adjust to show numbers greater than column width (shows “#######”)
3. It’s possible to enter a holding period longer than the mortgage itself (which produces a bunch of “#NUM!” errors).
4. The data on left only allows you to go up to year 30 (no 40 or 50-yr mortgages).

[randolfe]

Thanks HARM.

I intended to not allow mortgages longer than 30 years. They are just a bad idea, but it would be easy enough to expand to 50 years if people think that would be useful.

Holding period should never be longer than loan-term, because to do that I'd have to implement a two-phase model because the game changes after you own the home outright. (If anyone has any alternative suggestions which would handle this better, please chime in).

The IAR is actually a bit of a problem. I think I need to change it from an IRR calculation to a more explicit annual return computation, since IRR's can get goofy in situations like this model creates. I've seen some cases where IAR is higher than total return or negative even when total return is positive. Any suggestions welcomed (I'm hoping Fewlesh comes by and drops some nice linear optimization on us).

[randolfe]

tjrsfca brings up an interesting design challenge: "How do I know if the answer the model gives me is 'reasonable' or not?"

I am unable to think about this clearly because I've read the HSBC publication and stepped through their model in some depth. So, for me, I have all this ownership-to-rent, ex-post and ex-ante risk, rent-yield kind of stuff floating around in my head. But, for the average home buyer, how do they know if, say 100% appreciation in 5 years, is a "reasonable" expectation?

I'd like to discuss how to create a model which, while not giving the prospective home buyer a direct answer, really helps guide them into making the best decision. Perhaps we need some kind of a historical-trend kind of comparison.

I could see something like: you need 100% nominal appreciation to justify this purchase if you'll live there 5 years. For your area, prices have appreciated an average of 80% as a moving 5-year average, but only 20% over 5-years if you exclude the past X years (if you believe there is a bubble). Or maybe something like that.

But the problem is this: we cannot directly link to or use the HSBC model, legally. So doing this will be very prohibitive. I can envision taking a few of the worst bubble areas -- maybe 4 or 5 -- and replicating their work in aggregate for these, but not for an open-ended analysis.

[Girgl]

Randy,

fantastic model, and purty, too!

Re: "How do I know if the answer the model gives me is 'reasonable' or not?"

One potential solution is to show (alongside the dollar figure) what percentage of your income you had to spend on PITI if you bought the house, and what percentage of their income the prospective buyer has to spend to buy the house from you. The simplifying assumption is that the buyer has the same income (inflation-adjusted) as you have, which kinda makes sense - they'll be moving into the same neighborhood.

The unknown variable is what the interest rate is going to be at that time - maybe there could be three figures for three different interest rates:
- half of what you got
- the same you had
- twice yours.

If the resulting percentages are much higher than what you had to commit to even at half the interest rate, it follows that it may not work out that way.

[randolfe]

Girgl,

Thanks, that is a very good suggestion. Actually, I could assume that mortgage rates are based upon the CPI inflation assumptions in the year of expected sale + the mortgage rate "delta". I could compute this figure based upon whatever rate the user puts in, then let them change it if they want to.

Another approach would be to do a real monte carlo simulation, but in order for that to work one needs an add-in that isn't standard in Excel, so I didn't implement anything like that.

[randolfe]

Changes for 2006.04.12 (Version 1.1.3)

* The Alt Investment calculation has been changed to properly account for inflation. The effect is that alternative investment now produces less return, by the amount of nominal expected inflation.

* The worksheet (right side grid) now automatically resizes the columns after you press "Solve".

* An additional factor has been included: Bubble Correction Risk Premium. The user can set likelihoods for 5 scenarios, ranging from 'continued boom RE market' to 'hard-landing RE crash', along with the price changes (in percent) for each scenario. A risk premium is computed and added to the specific risk premium in the final NPV calculation.

* The user's average affordability is shown to them in a convenient format.

* A "Next Buyer" analysis has been added, showing what the person who will need to buy the user's home when they go to sell will face in terms of loan payments and affordability. This should help users to determine the big "is this reasonable" question.

[NapoV]

Randy,
Question on Column S (Nom Rent Cost) in the Model worksheet. Why do you add back tax deduction in that column? Isnt that double counting the benefit of tax deduction when you compare Rent to Own ratio ?

[randolfe]

NapoV,

Thanks for catching the tax-shield double count. I was so caught up in the binary decision (rent or own), that I failed to see I wasn't considering renting on its on basis. Funny, as this falls right into realtor(tm) selling tactics "if you rent you're losing the tax benefit". No, if you rent, you rent; if you own you get the tax benefit.

I'll have a new version soon. I'm also adding separate a scenario analyzer tab, which takes the five scenarios and shows how you'll come out under each circumstance.

[Girgl]

Randy,

great additions to the model!

Once you do the math, you can see clearly how insane it all is, even if you assume very favorable conditions. My "next buyer's PITI to income" ratio always comes in at >150% :-)

I was wondering about the calculation of the next buyer's interest rate. If I understand the formula correctly, you're inflating the current rate using the assumed CPI. Is that right?

[randolfe]

Girgl,

Yes, I'm inflating the user's selected mortgage rate at the CPI rate, which is a bit of a rough estimate, but probably conservative because of the way mortgage markets lag the yield curve.

[randolfe]

Changes for 2006.04.12 (Version 1.2.1)

* Fixed a problem in the rent calculations that was double-counting the value of the tax-shield for ownership. Rent costs and alternative investments cash flows are now calculate on their own basis. This results in an even wider gap, and thus even higher necessary appreciation to justify a high-priced home purchase (when rent is low).

* Added a new tab providing a scenario analysis. The goal here is to reflect back to the home buyer what they think about the future likelihood of boom-or-bust. I draw a kind of a rough distribution curve, then plot the various NPVs for them, so they can see the outcomes in each case.

The hope is that someone puts in all the inputs, hits "solve", then goes to the analysis and sees that maybe they really don't believe it's all that likely after all.

[Paul@oakland]

Randolfe,
I downloaded v1.2.1 In trying to figure out the different contingencies, I sent almost everything to zero: no inflation, no appreciation, no friction, no taxes, no interest, etc. In this scenario all house payment money goes back in your pcket when you sell so it as if rent is free so I also set rent to zero. Thus nominal alternative investment cash flow is the house payments. But in this ideal world money paid 15 years earlier is worth the same as at sale. However, the spreadsheet calculated the nominal alternative present value to values other than that year's cashflow. This does not make sense to me. Is it a mistake in the spreadsheet or am I missing something fundamental? If it is the later, could you either explain it or direct me to a place where I can learn about how to understand the mechanics of the calculation? Thank you.

[randolfe]

Paul,

I haven't tested the model under MM or boundary conditions. It is possible that there is an error, or that the model simply can't handle the boundary. I'll do some testing later today and try to get to the bottom of it.

Thanks for the testing. If you find an explicit error, please feel free to post it here or email it to me.

[randolfe]

Paul,

I figured out the problem: The model is a binary decision tree. You must either rent or own (no other options). If your rent is free, then you will always do better to rent (live for free), even if you set the discount rate to 0% and the mortgage rate to 0%. This makes sense, because in one case you are paying P, of PITI, when you could be investing it for free (rather saving it at 0% return, but in a 0% inflation environment).

So, if I can buy at price P, selling it for P in year n, but I could instead rent equivalent housing for R=0, then I'd have to sell for P'=P+(p*n) or I am worse off buying. (p*n) = the opportunity cost of your buying, because R=0.

Introduce taxes, and I'm better off buying due to tax shield. Start adding back in other realistic costs, and the linear system will optimize differently.

Perhaps what you meant to do was set R to exactly P, where you see that you need 0% appreciation to justify the purchase.

Please let me know if that helps or if I have mislabeled something that is causing confusion.

[randolfe]

Changes for 2006.04.14 (Version 1.2.2)

Thanks to Paul Ashby (mailto:pahby@media.mit.edu) for finding a critical error in the present-value of the alternative nominal investment calculation.

In previous versions, the alternative investment was compounding incorrectly, resulting in an overstatement of the present-value of value of renting. This brings down the implied home sale price and implied total and annual returns.

Although this gives more reasonable numbers, it still shows that house prices are higher than supportable on a purely financial basis. This is especially true when you look at the implication for the "next buyer". With Paul's fix, now the next buy just needs to be crazy, not criminally insane.

Please don't hesitate to email me or post here if you find any other errors of have any other comments on this model.

[randolfe]

I've created a link in the prominent upper-left nav area featuring "Models and Tools". In that area I've placed The Bubblizer, as well as an adaption by Paul Ashby and a scenario calculator (built from scratch) by Christian Kaiser.

Those links will always contain the most recent versions of those tools. We will continue to use this thread to post new info about any revisions to the models.

Thanks to everyone for the massive input and interest in these models. We've had thousands of downloads already, and we're still getting between 20-50 downloads per day.

[randolfe]

Changes for 2006.04.25 (Version 1.2.4)

* Added monthly affordability info to summary.
* Added an affordability tab with useful data and graphics.

[zeke]

I was fooling around with the bubbalizer. Shouldn't the "Implied Home Sale Price in Selling Year" be a formula? Mine is a constant "1351849.06575488" regardless of what I put in for purchase price or target net PV.

Disclaimer: I opened it with open office so maybe that is my problem.

[randolfe]

zeke,

C8 should be a reference to the named cell "selling_price", which is C53

C53 will appear to be a constant, the number you posted above is the "default".

This number is generated by a programmatic call to Solver, which solves for C7 (NPV) = C12 (target NPV) by changing C53.

You can set up an algebraic formula to skip the solver call easily enough. I used Solver so people could easily change their parameters or goals, or impose arbitrary constraints into the system.