How to calculate daily mortgage interest?

wannasavemoney

Hi everyone, I have done a search but cannot find any info on this - I regularly make overpayments on my mortgage and have online banking which lets me know how much is outstanding on my mortgage on that particular date it just lets me see the total. My question is, is there a spreadsheet/way of calculating the effect of daily interest for yourself? ie: a spreadsheet set up so that you can get it to calculate the interest daily and put in your payments and overpayments as and when you do them and see the effect that has on the outstanding amount? I'm not good enough with spreadsheet to know whether this is possible but am hoping that someone out there is more knowledgeable than me about these things. Looking forward to any information anyone may have. Thanks in advance.

wannasavemoney

I am assuming, from the deafening silence, that there is no answer to my question posed above? It is not possible? no-one who could help has viewed the thread? Anyway, shall endeavour to find a way of working this problem out. Might ring the mortgage helpline tomorrow and see if there is a way that they can let me see a more detailed account rather than just the yearly statement.

MickHG

Hi, It’s not too difficult to do the calculations for a simple repayment mortgage, but you have to know exactly what sort of mortgage it is, and how your lender calculates any early repayments.
You then have to incorporate an allowance for interest rate changes. By this time in can become rather daunting.

I’ve found in all this type of calculation it’s best to check your spreadsheet calculations against. you mortgage statements to verify your calculation methods.


If you require to do calculations including early repayments you will have to find the daily interest rate when compounded for 365 days which equals your yearly rate
i.e. 7% = 1.07 ^ (1/365) = 1.000185

Then multiply your last balance by this figure ^ number of days from last balance to your current payment date.
Example:- Last Balance 15 days ago = £50000 , your current repayment £500,.

Balance to date = (£50000 x 1.000185^15)-£500. = £49639

You then have to compare this with there figure, then modify your equation as necessary to get continuity with your statements.
When you’ve got the basic equation you can then start building your spreadsheet.

As you can see it could get quite complicated
Do you want to continue

Regards Mick

UK007BullDog

here are some calculators. hope this helps. http://www.thisismoney.co.uk/calculators

gboyd

I would also be very interested in such a calculator so I could accurately reflect the dates of my overpayments rather than the month which they occur.

I have tried setting this up myself but can not get the figures to match versus my online balance (I am in the same boat in that only my current balance is shown).

Thanks :beer:

amdangerous

Try this calculator - its really simple:

w w w . b l l a w . c o . u k

Sorry but as a fairly new user this is the only way I can add a link!! Just remove the spaces and you should get the website up ok.

SimbaSimon

wannasavemoney wrote: »

Hi everyone, I have done a search but cannot find any info on this - I regularly make overpayments on my mortgage and have online banking which lets me know how much is outstanding on my mortgage on that particular date it just lets me see the total. My question is, is there a spreadsheet/way of calculating the effect of daily interest for yourself? ie: a spreadsheet set up so that you can get it to calculate the interest daily and put in your payments and overpayments as and when you do them and see the effect that has on the outstanding amount? I'm not good enough with spreadsheet to know whether this is possible but am hoping that someone out there is more knowledgeable than me about these things. Looking forward to any information anyone may have. Thanks in advance.

Don't suppose you have online banking or could get online banking for your mortgage at all? I have it with Nationwide for all my accounts including the mortgage and I get to make payments as and when and see the balance which updates daily. Therefore to calculate my interest I just check it one day, and then check it the next to find the difference.

Gorgeous_George

I assume you have tried the calculator in the Free Tools section of this site. Also, search for locoblade's mortgage calculator.

GG

jamesperrett

Locoblades calculator thread can be found at http://forums.moneysavingexpert.com/showthread.html?t=1157173

Cheers

James.

moscow1818

I've produced an Excel spreadsheet I came up with that calculates the capital / interest split and the running balance. You can easily use it to show how increased repayments effect the loan term.

It's quite basic (can't change interest rate over loan term).

If you're familiar with Excel you should be able to adapt it to show whatever you need.

If interested please let me know and I'll email it.

dkmax_2

moscow1818 wrote: »

I've produced an Excel spreadsheet I came up with that calculates the capital / interest split and the running balance. You can easily use it to show how increased repayments effect the loan term

Unless you can better the existing spreadsheets, why bother?

https://forums.moneysavingexpert.com/discussion/1157173