Help understanding mortgage interest

russ401

Hi there,

I've joined this forum to see if anyone can help me solve a problem I'm having, with regards to working out mortgage interest. Basically I'm trying to construct a spreadsheet that can show my mortgage repayments/interest/balance much like the statements I get once a year. The 'APR' in my case is 4.95% which I calculate to be 0.01356% daily interest.

Below is what I have so far:

(Date)(Day)(Type)(Amount)(Balance)(Days)(Days Total)(Interest/Day)(Daily Rate)
01/10/2012	Mon	Interest	£263.42		£64,815.95	14	30	8.780667	0.013602
15/10/2012	Mon	Payment		£363.49	£64,452.46	14			
01/11/2012	Thu	Interest	£271.66		£64,724.12	17	31	8.763226	0.013596
15/11/2012	Thu	Payment		£363.49	£64,360.63	14			
01/12/2012	Sat	Interest	£262.54		£64,623.17	16	30	8.751333	0.013597
17/12/2012	Mon	Payment		£363.49	£64,259.68	16			
01/01/2013	Tue	Interest	£270.94		£64,530.62	15	31	8.740000	0.013601
15/01/2013	Tue	Payment		£363.49	£64,167.13	14			
01/02/2013	Fri	Interest	£270.46		£64,437.59	17	31	8.724516	0.013597

The data on the left (days/amounts etc) is straight off my statement. On the right I have the number of days calculated, then amount of interest per day broken down. As you can see though, the 'daily interest rate' that results from these calculations varies slightly. Shouldn't it be consistent? How can I go forward and get the spreadsheet to calculate future interest installments when I don't know what daily interest rate to use? (I could use 0.01356% but then won't get an accurate calculation?)

Confused and hope someone can help!

Thanks in advance,
R

HappyMJ

Rounding will produce slightly different results.

I would use a slightly higher rate of 0.013575% per day. When multiplied by 365 it comes to 4.95%

ACG

http://www.amortization-calc.com/ - will this help do the work for you?

Its in US$ but just imagine it being in sterling and it should be ok?

kingstreet

Use the notional rate, not the APR.

The APR includes set-up fees which may not be included in the outstanding balance.

getmore4less

Have you accounted for the changes in balance through the month.

remember the interest is daily but compounds monthly

looks a bit like Barclays but their payments are the 16th.

Thrugelmir

You need to calculate the interest on a daily basis but add it to your account monthly.

In doing so reduce the balance for any payments that you make during the month.

russ401

Thanks to everyone who replied to my query, there was some really good advice. In the end it was Thrugelmir's comment that made me realise I wasn't calculating the interest in the correct way - I've now fixed that and everything checks out!

Kindest Regards,
R

:j