Help with spreadsheet interest calculation

PiKe

PiKe

PiKe

Caporegime
Joined
18 Oct 2002
Posts
25,287
Location
Lake District
I have set up a spreadsheet to project my interest on my mortgage and to allow me to play with overpayments to see how much I can knock off the balance before the fixed term is up, the problem I have is that the interest calculation in the spreadsheet isn't matching that of the statement.

I'm using the equation
=(C2*(0.0203/365)*(EOMONTH(A2,0)-EOMONTH(A2,-1)))

Where C2 is the balance.

Does that look correct? APR is 2.03%
 
Pipe & Slippers said:
=POWER(+A1+1,1/365)-1 gives the daily rate where A1 is the quoted APR in the format 2.03%

Might be bit more of a helpful reply
Click to expand...
Still isn't coming out at the interest rate shown, maybe it's complicated by the payment being taken interest added on different days?

For example, my current balance is 64877.5, 125.6 interest was added 25th Jan but a payment of 325.54 was made 21st.

Using the above, the interest on the spreadsheet shows 112.5
 
Yes, as interest is calculated daily, it does get distorted eg, if your payments were due on the 1st of the month, but that's a Saturday, the payment will go on the Monday so you have 2 days extra interest on that balance
In the big scheme of things these only amount to a few £s a year. Your case is a little different, but it's still only going to be £10s out
If you want, I have a excel mortgate model that's pretty accurate if interest is added and payments are made on the first day of the month - you could always adapt it without too much issue to accommodate yor different interest and payment dates
 
Pipe & Slippers said:
... If you want, I have a excel mortgate model that's pretty accurate if interest is added and payments are made on the first day of the month - you could always adapt it without too much issue to accommodate yor different interest and payment dates
Click to expand...

I would be very interested in that if you don't mind sharing. :)
 
You must log in or register to reply here.
Back
Top Bottom