Mortgage spreadsheet

Boobydoo

I'm not the best when it comes to figures and I'd like some help please.

I want to create a spreadsheet where the total amount owed comes down as I enter each months payment................I can do that bit.

My problem is I want it to show the interest figures too. I'm able to make overpayments whenever I like on the mortgage so I'd like to be able to enter each payment I make and the spreadhseet would show me the actual total amount I owe with all the interest etc.

How do I go about this please?

Gary123456790

Hi,

Interest is probably charged daily which you will struggle to recreate in excel, but you can get close by doing monthly interest.

If your rate is 5% then divided by 12 is 0.416667. If you divide this by 100 then you can times it by the balance outstanding to get the interest charge.

e.g. Current balance 100k, interest due for the month 100000 x 0.00416667 = £416.67

That'll give you basic figures to show how the balance is reducing and how the interest charged is less when you make overpayments.

Hope that helps.

Gary.

harvey115

Have a look at the followin thread:

My Excel mortgage spreadsheet

The spreadsheet you are building has already been developed by someone and its very good.

Boobydoo

Thanks both. I'm afraid I'm totally lost with that spreadsheet harvey115. I struggle at the best of times with things like this.

I have my latest statement here. The balance in Oct 2011 was £100899.93. The interest only payments are £57.18 per month. The closing balance this year at end of Sept was £100916.32 and interest for the period was £686.55. It says my current interest rate is 0.68% and I have 19 years 9 months left.

So, from this can you tell me what I need to put into my spreadsheet to give me the balance at the end of each month after interest and payments please?

Boobydoo

Anyone.......................please?

plane_boy2000

I would suggest that you try the spreadsheet in the link from Harvey115.

Its a really good spreadsheet, and although it looks a bit daunting its not that difficult. You literally just need to key in the information you have given above and it will tell you what you are asking. It also has the ability for you to drop your extra one off payments in to see the effect they have.

Boobydoo

But I don't understand why my interest rate is .68%. Is this daily, weekly etc? Sorry, but I'm not the brightest when it comes to things like this.

plane_boy2000

Its a yearly (annual) interest rate. It would appear that you have a very good tracker rate

Zero_Sum

I've knocked one up, which is a bit simpler to the other one

http://zerosum.freewebspace.com/