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