calculating daily compounding interest for monthly amortization table

chamelion

Hi folks,

I maintain a monthly amortization table for my mortgage based on a sheet I had downloaded from this forum ages ago (can't find it now!).

I noticed the closing balance each month deviates from what my bank shows. It's not much, so typically I just "hard code" the value every so often. But - I want to see if I can fix it, especially in light of interest rate changes.

Key facts:
- interest charged in my sheet is taking my annual interest rate (let's say 10% to make it easy), dividing by 12, and multiplying by outstanding amount.
- In reality, my bank is calculating and compounding daily.

What I'm trying to work out is how to work out actual interest if compounded daily over the 28-31 days between payments.

I've gone through oogles of forums and threads and getting all sorts of different calculations, and they don't appear right. e.g. one of the threads found the daily interest, but then just multiplied the outstanding amount by daily interest for 30 days. That's not right, as it's not compounding!

Any help much appreciated, thanks!

TrickyDicky101

It's not compounded daily - it is charged daily but compounded monthly (when your monthly payment is due).

Rate/365 * days from last compounding point to current compounding point * Outstanding balance

should give you the interest being charged. The compounding points aren't fixed due to weekends/non-working days.

chamelion

TrickyDicky101 wrote: »

It's not compounded daily - it is charged daily but compounded monthly (when your monthly payment is due).

Rate/365 * days from last compounding point to current compounding point * Outstanding balance

should give you the interest being charged. The compounding points aren't fixed due to weekends/non-working days.

I'm with nationwide - they've confirmed interest is calculated and compounded daily...

If I log in and look at my balance, it does actually change daily!

TrickyDicky101

Whoever you have spoken to an NW is wrong. It is NOT compounded daily - the amount owed will move daily as an additional day's interest is added on - but the interest is calculated based on the balance at the period start (ie the last compounding point) and NOT on the prior day's total of Capital O/S plus Interest Accrued.

TrickyDicky101

If you don't believe me then quote some figures for your monthly interest charges which will need to include outstanding mortgage balance, payment dates, applicable rate % and I will demonstrate.

chamelion

TrickyDicky101 wrote: »

If you don't believe me then quote some figures for your monthly interest charges which will need to include outstanding mortgage balance, payment dates, applicable rate % and I will demonstrate.

I'd believe you over the folks I've spoken to who I'm quite sure don't even know how the calculation works

So then my next question:
let's say they compound the amount on the 1st of each month, and then calculate based on the value at that time.

Does this mean that if I make overpayments, I should be making the payment on or JUST before the compounding date? So if I make an overpayment on the 2nd of the month, in reality I'm "wasting" the interest benefit of that until the 1st of the next month?

TrickyDicky101

Not for overpayments - they will deduct the amount from the balance being used to determine future interest amounts from the point of payment. So, the earlier you make the overpayment the less interest you end up paying.

chamelion

TrickyDicky101 wrote: »

Not for overpayments - they will deduct the amount from the balance being used to determine future interest amounts from the point of payment. So, the earlier you make the overpayment the less interest you end up paying.

Got it - thanks!!