Calculate interest on mortgage

Question

I have been trying to arrive at the same interest figures that my bank charged me on my mortgage, however I am off by a few dollars so I must not be using the correct formula. I have a 30 year principle + interest loan, on a fixed interest rate for 5 years. It has a 0.7% offset account - ie. only $7 in every $1000 are deducted from the loan. I am trying to balance the calculations for the first month of the loan which was many years ago (the year had 365 days). The principle was $418,000 and the interest rate was 6.59% at that time.

Here is a screenshot of the relevant part of the first mortgage statement (I have blanked out the sensitive information, but no money amounts have been removed):

And here is a screenshot of the relevant part of the second mortgage statement, which follows directly on from the previous screenshot (again, I have blanked out the sensitive information, but no money amounts have been removed):

And finally the relevant part of the 0.7% offset account statement over the same period (again, I have blanked out the sensitive information, but no money amounts have been removed):

So the question is, how has my bank arrived at the interest value of $2,260.34 (on 28 September)?

If I try a basic calculation that does not take into account the 0.7% offset account then I can get close:

30 days * ($418,000 - $500) * 6.59% / 365 days = $2,261.36

This is off by $1.02, but really it might as well be off by $1000 since it is incorrect, it is not useful.

So then I tried computing the balance daily, taking into account the partial offset account, but I get no closer (the cells in yellow should match but they do not):

The spreadsheet is here, if you want to have a play with it: https://docs.google.com/spreadsheets/d/1Z1fKrKKqE8BMDOjDaA6wGiw09yfH-g-r77K7BbgDGhs/edit?usp=sharing

If more information is required to answer this question please let me know and I will provide it.

---

Extra information that may be useful

• This mortgage is with BankSA in South Australia
• The only fee that I am aware of associated with the mortgage is the "Advantage Package" Annual Fee of $395 which you can see from the above statements was deducted on 1 September from the offset account
• Stamp Duty was paid via a conveyancer - this bank was not involved in that payment - it was made from an entirely different bank. Likewise with council rates, the fee for registering of the property via the Land Titles Office, and a variety of other fees and expenses.
• According to the conveyancer, the "anticipated settlement date" was on 30 August, and all payments to the conveyancer were received on time, so I assume this is the correct settlement date

Answer 1

I suspect the discrepancy has to do with accounting rounding conventions. Google Sheets is using IEEE-754 double-precision floating point numbers in each cell. It does not round the balance any lower than the limits of that number format, even though the numbers you see have been rounded to two decimal places (cents).

If you were to round the calculated balance to two decimal places (cents) using banker's rounding (round to the nearest even on an exact tie - see my comment below) for each month, and then use the rounded balance to calculate the next month's balance, you would end up with a very different number at the end - which might match the number they have (I don't have time to check, though). If that number doesn't match, try altering the precision -- perhaps round to 4 decimal places instead of 2.