Rent vs. Buy: The Investment Spreadsheet

December 21st, 2011 by Potato

TL;DR: Here is a spreadsheet based rent-vs-buy calculator I put together in Google Drive (or, download for Excel). You can save your own local copy to play with your own numbers. The long post below helps explain some of the calculations and what to consider when entering values. Thanks go out to Matthew Gordon who did a lot of work on my original to calculate out the full mortgage amortization and to really clean it up!





In a recent post I was trying to simply get across the concept that there is some point at which it makes sense to rent rather than buy, even if you don’t assume something like a crash in housing prices on the horizon. I used the extreme example of renting a million dollar house for just a dollar a month: it makes no sense to want to buy in that case, as it’s so much cheaper to rent. As the rent gets cheaper and the house more expensive, there will come a point where we cross over from what we’ve known most of our lives — that buying a house is a solid financial move — to a region where that is not true any more, and it’s wiser to rent.

Once you grasp that simple concept, then the question becomes where that cross-over point is. There was a long discussion in the comments about that, out of which came this Rent vs. Buy Investment Spreadsheet (it can also be downloaded for Excel).

About the analysis: there are many moving parts when trying to decide whether it is better to rent or buy in the long term, most of which require that you make estimates and assumptions about what the future will bring. At the end, I’ll go over some of the cases, and the difference between a long-term expectation and a short-term result.

Basically though, what this is showing is what would happen if you chose to rent a house and invest any extra money that you saved vs. buying that same house. The common refrain is that by buying you “build equity” as you pay off your mortgage (and if you’re lucky, the house appreciates), but as a renter you can also build equity by saving and investing. If house prices are high enough, and rents low enough, then you may indeed be better off by renting and saving the difference. The spreadsheet explicitly includes the buildup of equity for both parties: paying off the mortgage for the owner, and saving and investing for the renter. Both start with the same amount of money, both have the same monthly budget.

At the difference between rents and prices in Toronto right now, you’d be better off to the tune of hundreds of thousands of dollars to rent for the next 30 years — even if house prices don’t crash. If you really wanted to own your house going into retirement, well, you’d have enough money to buy one with cash at the end, with plenty left over.

Factors:

Annual figures: all numbers are for the year: the monthly rent is multiplied by 12 to get an annual figure, etc. For the investment portfolio and the owner’s equity, the value is for the beginning of the year.

Investment returns: I’ve assumed 7% nominal returns, which for a young investor (i.e.: someone in the age group where they would be about to buy their first home) with a long time horizon and risk tolerance to invest in a heavily equity-weighted portfolio should be very realistic. The spreadsheet takes the amount of money you would have at the beginning (in this case, your 10% down payment plus closing costs) and compounds that at 7%, adding in the savings vs. owning each year (which, for the year its added, is compounded at half the rate to reflect the fact that it’s not present all at the beginning).

Rent, rent inflation: In this case, I’ve used 2% rent inflation, which is approximately what Toronto has experienced over the last decade. The starting rent is the advertised rent from a townhouse in the neighbourhood I used to live in — this is a real example, and yes Virginia, you really can rent detached houses in Toronto: it’s not all highrises and basements.

Mortgage: I’ve taken the currently available fixed-rate mortgage rate of 3.49%, and used that for the first 5 years. Then, I assume that rates go up a bit, to 4.5% for the next 5 years, then up to 5.5% for the remainder. 25 year amortization. Interest rates are very difficult to forecast with any accuracy, but I would bet that this is being very generous to the case for owning. Thanks to Matthew for making the mortgage information auto-update, and removing the need for an external mortgage calculator.

Maintenance: I’ve used 1.1% as the figure for the maintenance/repairs budget. This comes from much reading around the web as to good rules-of-thumb to use, as well as back-of-the-envelope budgeting of how much a house costs to keep in good repair. The maintenance costs will not in real life be as smooth as this: you may have to pay for nothing for 5 years, only to get hit with a roof repair, new hot water tank, and a leak in the basement all at once. Nonetheless, it should average out to something approximately in this range. If you decide to re-run the numbers for your own situation and are using a condo, remember to add a bit to the listed maintenance fees for the condo to account for repairs not covered by the condo corporation, like replacing your units’ appliances, repainting the walls, replacing the flooring, and of course, the occasional dreaded special assessment. Also keep in mind that many new buildings have maintenance fees that are lower than might be sustainable — maintenance fees often spike after a few years. Note that this is being very fair to the buying case: for the real-world example I chose for rents, the condo fees alone came to 1.4% (which doesn’t include interior maintenance like replacing appliances or repainting).

Property Tax: The Toronto property tax rate. I’ve used an increase over time that is above inflation figures used elsewhere (property tax increases by 4%/year) as that is what I’ve been reading the rates have been rising each year. If you have a better estimate, feel free to use that instead.

Taxes: It’s a tough to avoid taxes as they can be a big factor, so now the spreadsheet also includes an estimate for taxes upon selling the investment portfolio at the year of comparison. One of the big problems with trying to build in taxes is that they can be so complicated on investments: some can be deferred, some can’t, and there are different tax rates for different investment income types (interest, dividends, capital gains). On top of that, the investments could potentially be sheltered in a TFSA or RRSP. For the default number I put in 10%, which is assuming that the couple is in the $40k-75k income range, which would be an Ontario marginal tax rate of ~32%. Then about half the investments are sheltered (the excess is ~$10k/year, which could be put into TFSAs with two people, and still allows us to assume both renters and owners are maxing their RRSPs on top of that). Then of the half that’s exposed to taxes, it’s about 30% more tax-efficient than regular employment income. Plus, 10% is a nice, round number. If you prefer, you can just set the tax rate to 0% and then use an after-tax rate of investment return (or, depending on your own situation, if all the investments could be sheltered in a TFSA it gets easier).

Insurance: This is a very loose approximation, but it’s also the smallest factor, so it doesn’t matter as much if I’m off by a few hundred dollars per year.

Transaction fees: I’ve assumed that the transaction fees to buy are paid out of cash right away (and that the renter will instead invest that money). Matthew has updated the spreadsheet to also reflect transaction fees to sell in column R.

CMHC fees: The CMHC fees are calculated according to the downpayment set and added to the mortgage. The other closing costs (LTT, legal, etc.) are paid as cash, so the renter has the downpayment and closing costs to invest initially.





House value: This is the big one. In the default case this is a listing for the exact same North York townhouse as the one that the rental figure was taken from. The 240X price-to-rent multiple was also typically of many properties I checked (last updated February 2014). Try to find a good comparison for your own situation: what can you rent, what would be the price of that place or its equivalent?

For appreciation, I’ve used 2%/year — that is, that the house appreciates in-line with inflation over the long-term. This is clearly not the case in the short term: houses were up something like 10% last year, but down something like 10% in the middle of 2009. It is critically important to have a fair long-term estimate here: if you assume house prices go up 10%/year forever, then of course it’s going to make more sense to buy in almost any scenario: why wouldn’t you buy a baker’s dozen houses in that case? Though for the short term, that has been the experience: Toronto (and Vancouver, etc.) have had a hell of a run in the last few years. But the long-term history suggests something much more muted: basically inflation plus a bit. If we’re already at the point where you need to assume high appreciation for buying to make sense though, how much further up can house prices realistically climb? What will be the average return when you look back 25 years from now? If you are a believer in continued high appreciation of house prices for the long-term, I invite you to play with this spreadsheet some more: fast-forward a number of years, and look at what it would be like going forward if rents were up 2%/yr but prices up another 10%/yr — what rate of appreciation do you need just to break even at that point? Even if you don’t assume a crash, at some point the rate of appreciation will settle back down to a more moderate low-single-digit value. And to be conservative, that’s what you should pick in this kind of model.

Customizing it: At this point, the spreadsheet isn’t set up with forms and that sort of thing to just start typing your own numbers in. What you have to do is go to file->download as and save a copy for yourself. You can download it to excel and use it offline, or use file->save a copy to put a copy in Google Docs under your own account to play with it.

Discussion points:

There’s a lot to talk about, and this post is already fairly long, so I’ve made a separate post to go over some of the discussion points, and to discuss various scenarios. Then we can stick to the details of the spreadsheet analysis itself here.

There are a lot of assumptions and estimates involved, a lot. The question is what should you do for your life? And importantly, what are the consequences of being wrong? Don’t use this tool with unrealistic estimates to try to justify a decision you want to make, but rather try to use it to help you come to the decision you should make — and to see what happens if you’re wrong. Shelter is the largest household budget item, buying a house is the largest purchase a young family will ever make. Please, treat the decision with the respect it deserves and go in with your eyes open, the risks assessed, the options weighed — more than the handwaving gesture towards long-term averaging out and comfort that many make today.

Also check out the post a bit later in the series on how this analysis can change with changes in the various factors. And up next, the case where you already have a paid-off house in this market: should you sell and rent? It’s not quite symmetric.

12 Responses to “Rent vs. Buy: The Investment Spreadsheet”

  1. Wayne Stuart Says:

    Love the spreadsheet. A lot of work and effort, nice presentation!

    As we know by changing a few factors, we can change any data.

    Make these changes: Buyer not in Toronto, First Time Home buyer, 5% down, Investment rates similar to interest rates on mortage.

    Changes the picture.

  2. Matthew Gordon Says:

    Thanks Wayne.

    I updated it to allow for a first time homebuyer – setting the flag to ‘yes’ causes the Ontario land transfer tax to shrink by (up to) 2000 and reduces the upfront cash costs by $750. If I’m missing any other first time homebuyer benefits, let me know.

    I also added capital gains into the investments in the rent+invest case.

    If anyone has other feedback on how this can be improved, please feel free to comment!

  3. Potato Says:

    Hi Matthew, I think the capital gains calculation is broken. For the first row, it shouldn’t be there, then for the rest of the rows you’re adding back the investment portfolio again (which you had to do to get the first row to make sense…). Plus then you’re charging investment taxes on accumulated contributions as well as gains.

    It may be a feature too far: it’s tough to avoid capital gains/interest/dividend taxes entirely (you could say “oh, this rate of return is after-tax” or “I’ll use my TFSA” but it is a realistic concern) but it’s a tough problem to model with all the different tax rates and ability to defer.

    But I’m not trying to sound too critical: you really have done an awesome job!

  4. Matthew Gordon Says:

    The formula for investment after capital gains is:

    (Current investment amount – Original investment amount) * (1-tax rate) + Original investment amount.

    Alternatively: Initial investment + ( Gain * (1-tax rate))

    I can’t see where it’s broken…

    Cheers,
    Matt

  5. Matthew Gordon Says:

    Oh, I just saw it – the “+ initial investment” at the end wasn’t locked to that cell. And it’s fixed.

  6. Matthew Gordon Says:

    I see your point about investment gains – but the amount saved here is much more than what can be put in a TFSA, and RRSPs ideally are maxed on top of your mortgage/rent.

    If people feel like their gains will be tax free, they can enter a rate of 0%, but removing taxes completely biases the analysis toward favoring renting.

  7. Matthew Gordon Says:

    If we state in the spreadsheet that the investment rates of return are after-tax, that’s fair, but it may be unfair toward buying, since you’re then assuming you are taxed every year (no tax deferral). What I’ve done is given the option for the comparison to be done on a tax-deferral basis. I’ll update the wording in the spreadsheet to make that more clear.

  8. Potato Says:

    That’s fair, though the amounts aren’t that big compared to sheltering room — in the example given here, the surplus is only about $10k/year, which a couple could shelter in the TFSA alone, still allowing both renters and owners to use RRSP room.

    Though yes, ideally, both RRSP and TFSA would be maxed in either case, leaving any surplus to non-registered.

  9. Potato’s ‘Rent vs Buy Investment Spreadsheet’ | Vancouver Real Estate Anecdote Archive Says:

    […] Take a look at this very elegant Rent vs Own comparison spreadsheet/calculator. Thanks to ‘Potato’ for telling us about the sheet and hinting that we should link it. Potato also has a page of discussion at their blog ‘Blessed by the Potato’. Wise-words excerpt- “There are a lot of assumptions and estimates involved, a lot. The question is what should you do for your life? And importantly, what are the consequences of being wrong? Don’t use this tool with unrealistic estimates to try to justify a decision you want to make, but rather try to use it to help you come to the decision you should make — and to see what happens if you’re wrong.” […]

  10. Matthew Gordon Says:

    I fixed two issues identified in the other forum:
    1) different costs of living between renting and buying
    2) living in the house for more than 30 years.

    Both have been addressed in the the version I have edit privdleges for (link):
    https://docs.google.com/spreadsheet/ccc?key=0AgF-ddPb2c6pdGZ2amdGWUtoNGw0aFZ4N0dzcHBKY3c

    To explain 1:
    The original spreadsheet assumes the person doing the analysis is choosing to buy versus rent the exact same house. Given that isn’t usually an option, and the person doing the analysis may be deciding between renting close to work and buying in the suburbs (or vice-versa) I’ve added an input for different costs of living.

    Some examples of why the cost of living could be higher in one scenario versus the other would be: food is more expensive in that part of town, the commute is longer, getting around by car vs. public transit vs. bike vs. walk, etc.

    And to explain 2: I added a LOT more rows, more comparisons and the option to choose your own comparison time-frame (up to 150 years).

  11. Ron Says:

    Hi,
    Great spreadsheet. I tried playing around with it but it seems the cost of living is a big determining factor as to which side wins. Right now, off the download, it says that owning cost 1500 above renting. If I adjusted that to be something slightly over renting, the scenario starts to favor owning. Is there something I am missing here?

  12. Potato Says:

    No Ron, you’ve got precisely the point there. When houses are not so expensive, it makes sense to buy: this is normally the case, and where most of our conventional wisdom comes from. So except for a few years in the late 80’s and the last few years, it’s been a good financial move to buy a house and pay it off. But as the price-to-rent ratio went higher and higher with rising prices (and stagnant rents) that situation changed, to the point where it was a wiser financial move to rent.

    If you go ahead a few posts in the series, you get to where I looked at some sensitivities, and changing the price-to-rent multiple was one of the biggest influences. For the base case in the default spreadsheet here, I used a 215X multiple which was on the low end for Toronto (thinking that if even the cheaper properties in Toronto didn’t make sense to buy, then the more expensive ones were hopeless, and that people would see that I was being conservative). For many areas in Toronto, using a higher multiple like 250X may have been more appropriate, with some as high as 275X, and even a few select examples higher than that. Vancouver can easily top 300X.

    On the other hand, if you’re not in Toronto then a lower multiple may be the case: in London, Ontario for example, the multiple was around 175X last I checked, which would indicate it might be better to buy rather than rent (but just barely).