Jump to content
House Price Crash Forum
dan_w

Spreadsheet To Calculate Mortgage Costs

Recommended Posts

Hi folks,

Every now and then I look at the cost of mortgage (for various interest rates) against the cost of renting. I also try and calculate the benefit or loss of purchasing at various levels of HPI (including negative).

Mortgage Sums - Open Office Spreadsheet

At the above link is an Open Office spreadsheet. In the top left enter the property value and deposit amount. In the lower left enter the number of years the mortgage should run for.

There are 3 buttons to set interest rates, HPI and Rental inflation over a certain period of months. Interest rates are annual figures, HPI and RI are monthly figures and can be applied across a set range of months (1 to 360).

I would appreciate anyone taking a look to see if I have my sums correct or has any improvements.

Cheers,

Dan.

Share this post


Link to post
Share on other sites

You're asking me to open an "ods" file straight into Excel ... how do I know what that file will do to my pc? Probably nothing, is the answer ... but I really don't fancy trying to explain to my boss why a work PC is knackered and this being the reason.

So maybe you could put some of your calcs in the posts here or something so people can see your work without having to download files.

Share this post


Link to post
Share on other sites

You're asking me to open an "ods" file straight into Excel

Nope. Absolutely nowhere does the word Excel appear. But it does say - I thought clearly - that it was an Open Office spreadsheet. :unsure:

... how do I know what that file will do to my pc? Probably nothing, is the answer ... but I really don't fancy trying to explain to my boss why a work PC is knackered and this being the reason.

Excel won't be able to open it. The end. As for "knackered" - well, you're equating trying his file with catching a virus, which is totally unfair IMHO and besides, it's your (or your employer's) responsibility to run virus checking software anyway. You don't know what the file is - fine. You're pre-accusing him - not fine.

If it was an Excel file, you STILL shouldn't try it on your employer's PC - as you clearly weren't going to take full responsibility yourself! Ie you shouldn't expect to come back here complaining later if anything went wrong. Perhaps it's just as well that it's an ODS file so that you won't try it. :angry:

Edited by mrtickle

Share this post


Link to post
Share on other sites

Absolutely nowhere does the word Excel appear.

Yeah, it does ... I clicked the link, and a window popped up asking me if I wanted to open it in Excel.

As for "knackered" - well, you're equating trying his file with catching a virus, which is totally unfair IMHO.

You're pre-accusing him.

You've missed the point. What I was getting at is that the need to download a non-text file is a barrier to people looking at the OP's work and giving feedback. Therefore I suggested that the OP just put details in directly in posts so they can be read and commented on.

Share this post


Link to post
Share on other sites

Yeah, it does ... I clicked the link, and a window popped up asking me if I wanted to open it in Excel.

Oh come on. He did not ask you to open the file in Excel in his posting and it was wrong to claim it did.

You've missed the point. What I was getting at is that the need to download a non-text file is a barrier to people looking at the OP's work and giving feedback. Therefore I suggested that the OP just put details in directly in posts so they can be read and commented on.

Accusing someone of potentially damaging your PC is out of order and a rather rude way of making that "suggestion" IMHO.

Share this post


Link to post
Share on other sites

Hi folks,

A couple of screen shots are attached.

The first uses HPI at -0.4% per month over the next 24 months.

The second has HPI at 0%.

I was trying to measure the 'moving benefit' in terms of buying rather than renting. There are buttons to set mortgage interest rates, HPI and rent cost inflation.

The images shown use a 25 year mortgage.

Cheers,

Dan.

mortgage_sums_-04hpi.png

mortgage_sums_0hpi.png

post-26686-12858829915124_thumb.png

post-26686-12858830659961_thumb.png

Share this post


Link to post
Share on other sites

Accusing someone of potentially damaging your PC is out of order and a rather rude way of making that "suggestion" IMHO.

Not in the least. You don't go around opening random files from the 'net unless you want a virus, or unless you know what you're doing. Doubly so when you have no idea of the identity of the poster.

If it was something important, I'd open it in a sandbox. Since it isn't, I CBA.

Share this post


Link to post
Share on other sites

That's an interesting table of data you've got there.

You can calibrate the accuracy of your formulae by putting in the same figures using one of the online loan/repayment calculators, such as this BBC one. This calculator shows an interest only payment of £372 (the same as your month 1 figure, and a total payment for the same period as £595.31 (a little higher than your £589.07). I have found that the Beeb's calculator does tend to add a few quid on to the equivalent formula in Excel.

Another facet of comparison that you may find useful is comparing to rental yields, which I did a bit of when looking around to buy; the forum post for that one is here.

Share this post


Link to post
Share on other sites

Hi folks,

I have not seen this "type of analysis" but imho it is the way to look at the question as it starts to look at thw time value of money

I wonder if you might structure it differently

In a nutshell

I think you should look at the present value of all costs associated with buying and holding a property for a period of n months discounted at an appropriate interest rate less the Present Value of the house n months into the future

PV buying

Similarly you should look at all costs associated with renting the same property over a period of n months.

PV renting

Not looking at time value or allowing for inflation seems invalid to me

Depending on the result of the calculation you have an answer as to whether to buy or rent.

It would also make it easyier to audit

Things like the PV of a house in 25 years would be easy (1+HPI)^25-(1+discount rate)^-25

I assume in the long term you may wish to use different assumptions for the interest rate over the period you are looking at but to test initially you could use actuarial formulas

So for example if you assume rents grow by 4.0% per annum and your discount rate is 5.0% your model should say the NPV of a monthly rental of £1,000 pcm over 300 months is

£266,919.40

'£1,000*((((1+0.04)^(1/12)/((1.05)^(1/12)))^300-1))/(((1.04)^(1/12)/(1.05^(1/12)))-1)

not as simple as the house price calcuation so its in longhand

Anyways the principle is to look at the NPV of buying/ renting

if you were to create a monthly cash flow to represent rent for example and use the Excel NPV formula toy should get the same answer.

If you said my model says the NPV of buying / selling is XYZ with the following assumptions we are all on the same hymn sheet

Edited by economiccycle

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.

  • 245 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.