Saturday, May 24, 2014

Super-fancy and overly complicated house cost calculator

This tool originated from a conversation about the idea of buying a new house and living there for five years. My reaction to the idea was mild (or maybe moderate) horror at the enormous transaction cost. It's easy to do the math on that part, but it got me wondering what the actual all-in cost of such a decision would be. Which meant I needed to figure out the all-in cost of buying, owning, and then selling a new house vs. the cost of continuing to own the one we have.

This house looks nice. Maybe I'll buy continue to own it!
So I made a fairly complicated Google spreadsheet to figure it out. (The answer I got was suitably striking: $50,000. But I've since discovered several bugs in that original implementation that caused it to overstate the difference.) Then I wanted to share my beautiful creation, but linking to a Google spreadsheet seemed dumpy. A javascript calculator would be much nicer. Too bad I didn't know javascript. Fast forward a while to when I decided it was time to learn javascript, and I knew just the project to use for practice.

The idea of this calculator is to figure the total cost of owning a house—mortgage interest, property tax, insurance, and, importantly, the opportunity costs of having a bunch of money tied up in home equity.  When I say it includes insurance, what I actually mean is that it lets you enter a fixed amount and/or a fixed percentage of home value to add in, so it doesn't do anything very smart to figure out insurance cost, but it does provide a place to account for it. Also utilities, if you want to try to estimate them. Different houses will definitely result in different utility bills, but it's hard to predict what the actual relationship will be.

The calculator is on a page of its own here. You can go there now, or stick around for what's probably a lot more explanation and comment about the assumptions, parameters, and output values than is really necessary or useful.


Inflation: One of the biggest pitfalls with long-term calculations like this is dealing with inflation. For example, it would be easy to fall into the trap of thinking that you made a nice profit on a house if, say, you bought it for $150k and sold it ten years later for $200k.  Actually, if inflation averaged 3% over that time, your real return would have been -$2,403. Before transaction costs. So it's really important to account for inflation in these types of models. The calculator attempts to provide all values in current dollars. So, for instance, after a while the sum of "Principal paid" and "Remaining principal" won't add up to the purchase price, because both will have been discounted for inflation.

Real opportunity cost, real appreciation rate: This is a continuation of the above, but I wanted to emphasize it. The opportunity cost rate is meant to be a real (i.e. inflation-adjusted) rate. So if you think you can average a nominal 7% return on your investments but that inflation will be 3%, your opportunity cost rate should be 4%. And the appreciation rate should be not how much you think the house's value will grow, but how much it will grow above the rate of inflation. General rule of thumb: it won't. The long-term average behavior of house prices is that they keep up with but don't outpace inflation.

Selling: Since I was focused on a "buy then sell" vs. "do nothing" scenario, I set this up so that it requires a sale date and the overall bottom line is post-transaction-costs. I don't think that's necessarily a bad thing, since transaction costs are ginormous and it's easy to forget about them when doing rough estimation. But if you want to do a run for "keep indefinitely" you can put in a big number for "Years before selling" and set the "Selling cost" to zero.


Hiding columns: There is an excess of columns in the output table. If you click on a column header, that column will disappear (and you'll get a button to put it back if you want).  Your choice of what columns to show and hide should be persistent (unless you reject the cookie. Or there's a bug).

Calculating the status quo: To figure out the current and future costs of your current situation, just set the purchase price to what you think the current value of your house is, the down payment percentage to the number that makes "Purchase price" minus "Down payment amount" equal to your current remaining mortgage principal, and the mortgage term to the amount of time left on your mortgage (use decimal years if needed).

Saving your input values: The results page includes a link back to the calculator with the values you chose encoded in the URL, so that they'll override the defaults. I.e. it's suitable for bookmarking or for opening multiple copies of to try different variations.

Input parameters

There's really no point in commenting on all of them, is there? But here are some notes.

Purchase price: Starting off easy.  It's exactly what it sounds like.  Though it's treated as the value of the house, so if there are shenanigans like a "seller assist" involved, it would probably make sense to back those out and adjust the down payment to make the initial mortgage amount match.

Down payment amount: Not editable or used directly, but it updates based on purchase price and down payment percentage to show the actual dollar amount that they imply.

Mortgage rate: The built-in default is 4%, which is in the ballpark for a 30-year fixed loan at the moment. The default is the most recent 30-year fixed rate from Freddie Mac's Primary Mortgage Market Survey (loaded from Quandl. Do people know about Quandl? I just found out about it. It is awesome).

Years before selling: As mentioned above, you can ignore the selling aspect by setting this high and "Selling cost" to zero.

Show every X months: This controls how many rows the output table will have. It's not smart enough to always show the last month if it's not divisible by this number, but fortunately multiples of 12 are very divisible numbers.

Real opportunity cost rate: This should be the average inflation-adjusted return you expect from money you have invested. I.e. if the money tied up in the house were in a retirement or investment account instead, how much would you expect it to earn above inflation. The default, 5%, is on the conservative side of fair for a long-time-horizon diversified portfolio. At least historically. If you think the future is not so bright, adjust this down. But don't succumb to knee-jerk pessimism, either.

Note that this assumes that if the money you have invested in a house were available it would be invested in a diversified portfolio. If you believe yourself to be the sort of person who benefits from the forced savings aspect of having a mortgage, and who wouldn't manage to keep that money invested if it were in a more liquid form, you should adjust your opportunity cost rate down. In the extreme example, I guess if you thought you would spend every penny that didn't go toward the mortgage, you could make the opportunity cost rate zero. Or even negative. I would need to give more thought to whether that would produce sensible results.

Inflation rate: There are a lot of different ways this could go. The average since the mid-80s is something like 2.8%, but since we don't seem like we're that close to getting out from under the Great Recession, I made the default 2.5%. That's leaving aside the secular stagnation hypothesis, which could mean it'll be much lower or could mean—if the idea gains wide acceptance and the Fed acts to counteract it—that it it'll be significantly higher. So yeah, 2.5% seems a decent guess to me.

Buying cost: As I understand it, sellers and buyers usually split transfer taxes. In Philadelphia, those are 4%, so I estimated 1% for mortgage fees, title insurance, etc., and made it default to 3%.

Selling cost: Defaults to 8%. Hopefully it's lower for some people, but in Philadelphia it's probably slightly higher, given the traditional 6% agent commission and the seller's half of the 4% transfer tax.

Real appreciation rate: As noted above, the long term average is for houses to keep up with inflation. Obviously the short-term behavior can deviate a lot from that. But I don't know of a reason to predict something different happening in the future.

Property tax rate: Philly now uses actual value with a homestead exclusion. If your taxes are based on some other calculation, you'll have to do some math. It should be possible to use this and the "Property tax exclusion amount" field to get to the right answer, though.

Property tax exclusion amount: Subtracted off of the current home value before it's multiplied by the property tax rate.

Misc expenses (% of home value): What it sounds like. For each month, it's multiplied by the appreciated home value for that month.

Misc expenses (dollar amount): Unlike most of the amounts in the table, this is assumed to increase with inflation, so will not be discounted by the inflation rate.

Reuse results window?: If this is unchecked, it will pop up a new window every time you hit the button. If checked, it will make a window and then reuse that window for subsequent runs.

Results table columns

Wow, this is getting long. Hopefully people bailed out and followed the link above.  Here it is again if you hung in this far but have had enough.

But for those who are ready to stay with me to the bitter end, because there's something they're procrastinating that they really don't want to go do, I shall press on with a few notes on the output values.

Payment: This is the amount your mortgage company will charge you for principal and interest, based on the amortization calculation. It might seem weird that this amount changes every month for a fixed-rate loan, but that's because the bank is charging you based on the nominal balance of your loan but the table is showing current dollars. Every month the amount you have to pay is actually, adjusted for inflation, a little lower.

Interest: The portion of the month's payment that goes to interest.

Total principal paid, Remaining principal: These move in the direction you would expect, but the total paid grows a little slower than you would expect and the remaining principal shrinks a little faster, again due to inflation.

Current value, Equity: Current value is the purchase price times the compounded real rate of return. Which, by default, is zero, so this column is pretty boring. Equity is that value minus the remaining principal. When you've borrowed money to buy an appreciating asset, inflation makes you slightly richer.

Opportunity cost: This is how much you're losing during the month due to a) the money that's tied up in the house not being invested more productively and b) the money you've spent on interest, taxes, and opportunity cost in prior months being gone instead of being invested and earning money for you.

Monthly bill: Not directly relevant to cost, but since I had the quantities available I figured it would be nice to see the actual monthly cash flow implications. This is your monthly principal and interest payment plus monthly misc expenses and property tax.

Monthly all costs: This is what it actually costs you to own the house for the month. Compared to "Monthly bill" it adds opportunity cost but subtracts the amount that goes to principal.

Totals: Then we have some running totals of the quantities described above. Discounted for inflation, of course.

Sales cost per month: The last few columns are concerned with the effects of selling. This is the total cost to sell the house in the given month divided by the number of months since you bought it.  I.e. if you sold this month, how much would you have paid per month just in transaction costs.

Total appreciation: This is equity minus total principal paid.

Note: My default scenario has home value matching but not beating inflation. So if you're not making money (in real terms) on the house, where is all this appreciation coming from? One possibility is that it's a bug. But I think it's not—I think it works out that way because the amount you actually paid for the house is constantly shrinking due to inflation, but that doesn't have an effect on your monthly costs. You only profit from that difference when you sell. Anyone who can actually explain this so it makes sense (either an understandable way of saying why the calculator is right or an explanation of why it's wrong), please comment.

Final monthly cost: The sum of "Total all costs" and total sales cost minus total appreciation, divided by months of ownership.

Final total cost: The total amount the whole thing cost you, post-sale.


At last, the end!  If you haven't already, it's time to go to the calculator!

Postscript: The New York Times and I have so much in common

I wrote this post (all but a couple paragraphs) two nights ago, on May 21. The code wasn't quite ready, but I hoped to have everything done and posted in a day or three. Then what should I find in my Facebook feed the next morning? This! A new tool by the New York Times's "The Upshot" blog that's remarkably similar to mine, except for being way nicer looking, more usable, and generally great.

I must admit to having felt some chagrin. It's true that I'm not in competition with the Times. And what we've both done is presented a solution to a particular math problem that affects lots of people's lives, so you can't really consider that creative work that you would expect to be unique. But still, I built this tool because, until yesterday, I hadn't seen anything like it. So it took the wind out of my sails a little to have this thing I've been thinking about since last July (that's when I made the spreadsheet. The javascript part is more recent) so precisely clobbered.

The one revision I've made to my tool based on looking at NYT's is that I had put all the transaction costs on the selling side, not realizing that transfer taxes are usually split and thinking the other fees are small enough to ignore, when in fact they can add up to real money.

I would say the one thing I like better about my tool is that the table lets you see intermediate amounts and makes it a bit easier to see how things add up and fit together. I also think their opportunity cost default is too low (though some might argue that I've gone too far in the other direction), and while it's cool that they try to account for taxes, I suspect they treat all property tax and mortgage interest as deductible against the entered marginal rate, and I think in a lot of cases—maybe more often than not—that would overestimate people's tax savings. And I provide a bookmarkable link. I don't see a way to save your numbers in their tool.

On the other hand... there are too many things to mention. But I will say that the comparison to renting, which is somewhat central to the Time's tool and totally absent from mine, was not a priority for me because I don't feel like I could find a suitable rental in my neighborhood at any price. In New York there's a pretty diverse and comprehensive rental market, but I think in a lot of places, there just aren't good rental options to be weighed against buying.

So that's the story about that. My little tool might be 99% superfluous now, but I like it all the same.

No comments:

Post a Comment