Jump to content
House Price Crash Forum
DarkHorseWaits-NoMore

Buying Vs. Renting Calculator Experiment (Free Spreadsheet)

Recommended Posts

I have been trying to build a spreadsheet calculator/estimator to broadly compare the cost estimates for Renting vs. Buying a property over the past 12 months.

Lots of variables and assumptions to be careful about. Please have a look/play and point out any glaring discrepancies for the average buyer/renter to consider. There may be some double counting.

I have noticed some interesting observations.

-The debt erosion from interest is a significant factor in the calculation.

-The cost of buying at an LTV of 20% (2% IR) up to an LTV of 80% (5% IR) doesn’t change much even when compensating for high Interest Rates at higher LTVs (Loan To Value ratios).

Of course, if Inflation, Interest rates or House Price Inflation changes, the figures can be hugely affected.

Hope you and others find it useful and interesting also.

Sheet 1: Buying vs. Renting real terms annual cost comparisons with respect to inflating and deflating financial components (rougly).

Sheet 2: Wage Deflation Cost Calculator (may require another discussion thread).

Inflation-Visibility-Calculators-Buying vs. Renting.jpg

Inflation-Visibility-Calculators.zip

post-21626-0-90460600-1345271592_thumb.jpg

Inflation-Visibility-Calculators.zip

Edited by DarkHorseWaits

Share this post


Link to post
Share on other sites

How could you factor-in near zero costs and an asset to bequeath when the mortgage is paid off?

Yes I was aiming at the 'average' buyer or First Time Buyer but an inherited property you probably only need to consider the HPI and inflation effects vs. cash in the bank (or returns on other cash investments) after compensating for the cost of a property sale etc.

Share this post


Link to post
Share on other sites

I have been trying to build a spreadsheet calculator/estimator to broadly compare the cost estimates for Renting vs. Buying a property over the past 12 months.

Lots of variables and assumptions to be careful about. Please have a look/play and point out any glaring discrepancies for the average buyer/renter to consider. There may be some double counting.

I have noticed some interesting observations.

-The debt erosion from interest is a significant factor in the calculation.

-The cost of buying at an LTV of 20% (2% IR) up to an LTV of 80% (5% IR) doesn’t change much even when compensating for high Interest Rates at higher LTVs (Loan To Value ratios).

Of course, if Inflation, Interest rates or House Price Inflation changes, the figures can be hugely affected.

Hope you and others find it useful and interesting also.

Sheet 1: Buying vs. Renting

Sheet 2: Wage Deflation Cost Calculator (may require another discussion thread)

I likespreadsheets, I like numbers but I prefer living.

Suggested reading:

http://en.wikipedia.org/wiki/Zen_and_the_Art_of_Motorcycle_Maintenance

Get a life.

Share this post


Link to post
Share on other sites

...Get a life.

:lol: that's what the misses said! But when base rates and mortgage Interest rates are this far below Inflation, then add in a bit of negative HPI, I wanted to see the numbers more clearly with respect to buying a falling asset. When the purchase is as large as a house the information is relevant to me.

You often hear debates about such matters but here is a way to quantify more accurately a true(er) cost comparison, although perhaps limited to an example if played out last year. Many other scenarios can also be tested to suit personal situations. Eg. Tweak the red numbers to estimate the costs of 100% cash buying vs. 50% LTV mortgage or the Interest only option (with respect to rate premiums). Or juggle HPI, Inflation and IRs to estimate possible future scenarios, say if HPI went +/-10 in a year and how it affects the true(er) costs of holding a property asset.

Edited by DarkHorseWaits

Share this post


Link to post
Share on other sites

I've been making something similar too, except mine is designed for predicting your future costs based on assumptions you enter.

One thing I'd say that's hard to compensate for is inflation. If you take a figure of 5% for inflation, then £500 in rent today costs more than £500 in 6 months time. At least in theory. Actually it depends on when/if you get a pay rise. If that's in 3 months time, then the last 3 months rent of £500 are easier to handle than before the pay rise. I notice your spreadsheet doesn't factor inflation into the costs of renting, but does take it into account on the buying side.

Another factor pointed out to me on another thread is the ability to overpay a mortgage, which can mean sometimes it's better to buy now and overpay the mortgage than wait then buy.

Also it's interesting to note that if you assume 5% inflation and 4% mortgage interest, the mortgage company is actually losing money giving you a mortgage. Sure they're losing it slower than if they didn't give the mortgage, but they're still losing cash.

Share this post


Link to post
Share on other sites

Yes agreed, good point, the compound effects due to monthly inflation and possible loss of bank interest for paying the monthly rent or mortgage are omitted. I’ll calculate and see if they are beneficial or just add extra clutter. Inflation is variable month to month and would probably slightly affected by other annualised affects.

> ...if you assume 5% inflation and 4% mortgage interest,

> the mortgage company is actually losing money giving you a mortgage. ...

These sure are very strange times we are going through. I don't expect this situation to persist but not sure how long it can last.

> ...the ability to overpay a mortgage...

I might try and add an option to factor in monthly over payments but currently limited to a single years view so may need rethink the design to make the data more meaningful and useful. More thinking required...

Share this post


Link to post
Share on other sites

Currently house prices are falling so you need to factor in deflation, not inflation.

Hence HPI entered as a negative number and positive should HPI return.

I've used the one from excel experts previosuly , Buy Or Rent it seems to just boil down to your future predictions,

Thanks, not seen that before, I'll take some time to examine it. ;)

Edit: Future predictions are very difficult from here. My effort uses last years known values as a rough starting point for a 1st year snapshot only. It makes no predictions about next year or any other year, that's for the user to play out and estimate various future scenarios.

Edited by DarkHorseWaits

Share this post


Link to post
Share on other sites

http://www.nytimes.com/interactive/business/buy-rent-calculator.html

That is a good calculator from the NY Times, but obviously is applicable to the US, not the UK. Would love it if someone could make something similar for the UK.

Only the currency? Works pretty much the same for UK if you set annual taxes to 0 and enter values as if they were in UK GBP. Simple to use but there doesn't seem to be any compensations for inflation effects of the various financial components :( (which varies for year to year and cannot be predicted, only modelled). Otherwise interesting to play with, thanks.

Share this post


Link to post
Share on other sites

I have been trying to build a spreadsheet calculator/estimator to broadly compare the cost estimates for Renting vs. Buying a property over the past 12 months.

Why have you deducted -£2k renting as "deposit lost to inflation" when you have HPI as -2%?

If that deposit is earning interest in a bank it has increased not decreased. The only "inflation" that erodes it's value is HPI.

Also I suppose it depends where you rent but £10,800 rent for a £160,000 house seems a lot. That is a 6.75% yield. In many places you can rent a much better quality house than you could afford to buy which has "value" even if it isn't monetary that shows up in a spreadsheet.

Share this post


Link to post
Share on other sites

>...deducted -£2k renting as "deposit lost to inflation" when you have HPI as -2%?

> If that deposit is earning interest in a bank it has increased not decreased.

We are trying to attribute 'real' costs of both scenarios, like for like they are still costs. The renting example uses the money you would use as a deposit that must exist somewhere, for simplicity I assume as cash in the bank earning 3% (of 40K = +1.2K) whilst inflation over the year is at 5% (of 40K = -2K). So the net cost of holding your deposit whilst renting for a year is a loss of -800 (or 3%-5% = -2%). Of course you may have a better or worse performing return on the deposit but that is for the user to tweak.

Then on the other side HPI and inflation (see below).

> The only "inflation" that erodes it's value is HPI.

HPI and Inflation. HPI is the change in the asset price, Inflation is the effect of the change in the value of the price measure (currency value) of the asset price.

eg. HPI at -2% (deflation, sales prices falling) 160k asset deflates to 160K-3.2K =156.8K but inflation in the currency was at ~5% last year, so in real terms the loss in value is actually -7% or 160K-11.2K =148.8K. Is that correct and not talking absolutes here but is my theory broadly correct?

These are exactly the kind of costs that aren't obvious at face value, unless you can perform such complex calculations in your head and the reason for creating this spreadsheet in the first place. Highlighting the ways different financial components are affected in different ways, some more than you would expect or have even thought were a factor of weight in the first place, to estimate a true(er) cost comparison.

>... depends where you rent but £10,800 rent for a £160,000 house seems a lot. That is a 6.75% yield.

Agreed, just a generous example figure that varies according to area. Around here (in the south west/midlands) the rent charged is ~650 to 900 a month for a 3 bed semi worth ~160K. Change the values to suit your area.

Edited by DarkHorseWaits

Share this post


Link to post
Share on other sites

it seems to just boil down to your future predictions,

Exactly. There's no point trying to account for every detail when the biggie, will house prices fall*, is by far the main determinant of which is better. And for this you can only take a view, for if it were certain it would already have happened.

*specifically, is the house you want to eventually buy going to fall in price after you buy or is it priced correctly today.

All you can do in these scenarios is to reduce as much uncertainty as possible and make the best decision once you've mentally "made your bed":

For the buyer:

ensure you are not over-paying at the outset, getting a mortgage which allows over-payments, ensuring your house is in demand if you ever needed to sell it, that the house will meet your needs for several years (difficult for young people to know)...

For the renter:

make sure you are able to keep saving for a deposit while you keep your options open and are able to benefit should prices fall but do not fall behind if they don't. (If prices rise above your ability to save, then renting when you could have bought is clearly an error - no amount of back-rationalisation with a spreadsheet can change that.)

So it comes down to being pragmatic, reducing uncertainty where you can and keeping options open for as long as possible. (This is from someone who generally loves spreadsheets, but is aware of the danger of false precision.)

Share this post


Link to post
Share on other sites

*specifically, is the house you want to eventually buy going to fall in price after you buy or is it priced correctly today.

That's great advise. The spreadsheet only helps highlight the kind of ‘what if’ losses that could be in store should I be tempted to buy instead of changing rental. Say HPI went further negative next year or for some reason IRs spiked etc. Unless you are very cash/asset rich and are lucky enough to have high performing investments, at the moment renting or buying are both costing a lot of money.

I identified an error :( with the costing of ‘Bank Interest lost on the deposit now tied up in the house’ if HPI is entered as positive. The fix calculates Bank interest lost only if HPI is less than the potential rate for the Deposit (if not invested in the house). This error is more apparent when using 0% LTV (a 100% cash buy) and all the red variable figures are zero’d except bank interest, where a cost is calculated for the Deposit when there should not be one. Or just set HPI positive and Bank Interest on savings. In todays examples the error doesn’t show up as a problem because HPI is negative anyway.

Change formula in cell C16 from =-(C8/100*(D16))

to =IF(D16>=D15,-(C8/100*(D16))+((C8/100)*IF(D15>=0,D15,0)),0)

or download the latest version from this post (as I am unable to edit and update the first post anymore).

Inflation-Visibility-CalculatorsV2.zip

Inflation-Visibility-CalculatorsV2.zip

Edited by DarkHorseWaits

Share this post


Link to post
Share on other sites

That's great advise. The spreadsheet only helps highlight the kind of ‘what if’ losses that could be in store should I be tempted to buy instead of changing rental. Say HPI went further negative next year or for some reason IRs spiked etc. Unless you are very cash/asset rich and are lucky enough to have high performing investments, at the moment renting or buying are both costing a lot of money.

I'm glad you took it in the spirit intended. Going through the modeling exercise you are doing is excellent and will hi-light the key unknowns - but they will remain just that: unknown. BTW, when/if you are tempted to apply "probabilities" to certain scenarios, you know you are taking your model beyond its first-order usefulness.

Share this post


Link to post
Share on other sites

Thanks for this OP.

I have produced something similar, but rather less sophisticated, to identify the full costs of renting or buying for next year. From this I was able to see that for the sort of house I am renting and looking to buy, HPI of at least 1.8% was required before buying was cheaper than renting. (HPI for the year to date in my county was -3%).

In this era of low interest rates and a highly polarised housing market there is no single answer to the "rent or buy?" question. It all depends on where in the country you are and what you are buying, these sorts of spreasdsheets help clarify things. Most people don't understand the concept of "opportunity cost" and therefore overestimate the economic benefits of buying.

Flynn

Share this post


Link to post
Share on other sites

>...deducted -£2k renting as "deposit lost to inflation" when you have HPI as -2%?

> If that deposit is earning interest in a bank it has increased not decreased.

We are trying to attribute 'real' costs of both scenarios, like for like they are still costs. The renting example uses the money you would use as a deposit that must exist somewhere, for simplicity I assume as cash in the bank earning 3% (of 40K = +1.2K) whilst inflation over the year is at 5% (of 40K = -2K). So the net cost of holding your deposit whilst renting for a year is a loss of -800 (or 3%-5% = -2%). Of course you may have a better or worse performing return on the deposit but that is for the user to tweak.

Then on the other side HPI and inflation (see below).

> The only "inflation" that erodes it's value is HPI.

HPI and Inflation. HPI is the change in the asset price, Inflation is the effect of the change in the value of the price measure (currency value) of the asset price.

eg. HPI at -2% (deflation, sales prices falling) 160k asset deflates to 160K-3.2K =156.8K but inflation in the currency was at ~5% last year, so in real terms the loss in value is actually -7% or 160K-11.2K =148.8K. Is that correct and not talking absolutes here but is my theory broadly correct?

These are exactly the kind of costs that aren't obvious at face value, unless you can perform such complex calculations in your head and the reason for creating this spreadsheet in the first place. Highlighting the ways different financial components are affected in different ways, some more than you would expect or have even thought were a factor of weight in the first place, to estimate a true(er) cost comparison.

>... depends where you rent but £10,800 rent for a £160,000 house seems a lot. That is a 6.75% yield.

Agreed, just a generous example figure that varies according to area. Around here (in the south west/midlands) the rent charged is ~650 to 900 a month for a 3 bed semi worth ~160K. Change the values to suit your area.

I don't see why holding a deposit that is going to be used to buy a house that has dropped 2% in price is a "cost to renting". Surely the 5% general inflation is irrelevant unless the deposit is going to be used to purchase something other than a house that has dropped 2% in price e.g. a car that has gone up 5% like RPI.

Share this post


Link to post
Share on other sites

From this I was able to see that for the sort of house I am renting and looking to buy, HPI of at least 1.8% was required before buying was cheaper than renting.

...there is no single answer to the "rent or buy?" question. It all depends on where in the country you are and what you are buying, these sorts of spreasdsheets help clarify things. Most people don't understand the concept of "opportunity cost" and therefore overestimate the economic benefits of buying.

Flynn

Super post!

Share this post


Link to post
Share on other sites

Exactly. There's no point trying to account for every detail when the biggie, will house prices fall*, is by far the main determinant of which is better. And for this you can only take a view, for if it were certain it would already have happened.

*specifically, is the house you want to eventually buy going to fall in price after you buy or is it priced correctly today.

All you can do in these scenarios is to reduce as much uncertainty as possible and make the best decision once you've mentally "made your bed":

For the buyer:

ensure you are not over-paying at the outset, getting a mortgage which allows over-payments, ensuring your house is in demand if you ever needed to sell it, that the house will meet your needs for several years (difficult for young people to know)...

For the renter:

make sure you are able to keep saving for a deposit while you keep your options open and are able to benefit should prices fall but do not fall behind if they don't. (If prices rise above your ability to save, then renting when you could have bought is clearly an error - no amount of back-rationalisation with a spreadsheet can change that.)

So it comes down to being pragmatic, reducing uncertainty where you can and keeping options open for as long as possible. (This is from someone who generally loves spreadsheets, but is aware of the danger of false precision.)

For once you are completely wrong.

I can say that with absolute certainty because that is exactly the situation I have tried to put myself in and I am consistently 100% wrong.

Share this post


Link to post
Share on other sites

For once you are completely wrong.

I can say that with absolute certainty because that is exactly the situation I have tried to put myself in and I am consistently 100% wrong.

Then you know exactly what to do at all times. :lol:

(stay with it.)

Share this post


Link to post
Share on other sites

In fact, I know that doing the opposite is not the answer.

Trying to second guess your instincts on the notion of trying to avoid negative outcomes does nothing but take you to a dark place.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

  • Recently Browsing   0 members

    No registered users viewing this page.

  • 221 Brexit, House prices and Summer 2020

    1. 1. Including the effects Brexit, where do you think average UK house prices will be relative to now in June 2020?


      • down 5% +
      • down 2.5%
      • Even
      • up 2.5%
      • up 5%



×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.