5

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): mortgage statement 1

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): mortgage statement 2

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): 0.7% offset statement

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):

mortgage calculations

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
  • Accumulated rounding error in the functions? – RonJohn Aug 31 at 13:37
  • hi @RonJohn, i thought of that, but i have tried roundup and rowndown to the nearest cent and it is still not correct – mulllhausen Aug 31 at 13:38
  • I'm on the road so I can't calculate if it fits, but be aware that banks often consider a year to consist of 12 months with 30 days each. So you save a bit of interest in 31-day-months, but lose a bit in Feb. maybe using 360 and 30 matches? – Aganju Aug 31 at 15:12
  • 1
    Thats good to know @Aganju, but I don't think it applies here. I tried recalculating using 360 days instead of 365 but the amount is much more incorrect: 30 days * ($418,000 - $500) * 6.59% / 360 days = $2292.77 (incorrect by $32.43 now) – mulllhausen Sep 2 at 4:26
  • Have you checked the loan payment statement on additional cost e.g. stamp duty, services tax, government tax, etc? – mootmoot Sep 2 at 11:58
4
+50

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.

  • I would add that you can test this by making each calculated figure in Excel =round("PREVIOUS FORMULA"), so that the rounding should match. – Grade 'Eh' Bacon Sep 6 at 19:36
  • 1
    Careful - round always rounds up on exact ties (0.5 => 1, 1.5 => 2, 2.5 => 3). Banker's rounding rounds to the nearest even if there's an exact tie (0.5 => 0, 1.5 => 2, 2.5 => 2). – Roy Tinker Sep 6 at 19:54
  • no I tried both ROUNDUP and ROUNDDOWN to 2DP - neither of these changes the end result (yellow cell) by more than a couple of cents anyway - see my comment to @RonJohn under the OP – mulllhausen Sep 9 at 1:45
  • @RoyTinker Thanks for that, I wasn't aware of the difference, and interesting that it corrects the problem here, very informative. – Grade 'Eh' Bacon Sep 17 at 13:03
  • 1
    Note it looks like this wasn't the issue, since @mulllhausen used both ROUNDUP and ROUNDDOWN. If bankers rounding were the solution, those functions would have at least resulted in numbers greater than, and less than, the expected result. (Or perhaps there was another problem as well...) – Roy Tinker Sep 17 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.