loan offset account saves $47952, are my calculations wrong?

Just about to take out our first home loan, and i'm getting my head around all the loans and types of options such as offset accounts, fixed, variable and split loans etc.

As an exercise to see the benefits an offset account could provide, I ran some calculations in the attached excel spreadsheet, and it seems to indicate i'll save approx $47952 over the life of a $400,000 loan, making approx payments of $1400 a fortnight.

The problem is, i'm not convinced my calculations are correct and i'd really appreciate you guys casting an eye over my formulas to see what I am doing wrong.

Basically, I am calculating the daily interest rate (eg PA interest rate of 7.62/365 = 0.000208767) and multiplying that by the amount owing on the loan to figure out what the daily interest charge is (eg daily interest rate 0.000208767 * amount owing $400,000 = $83.50 interest on the first day of the loan etc)

In the offset example, I am slightly modifying it, to have the offset account balance reduce the amount owing for the purpose of calculating the daily interest (eg daily interest rate 0.000208767 * (amount owing $400,000 - offset account balance 5000) = $82.46 interest on the first day of the loan etc)

Anyhow if that all seems a bit confusing, have a look at the attached spreadsheet and it will hopefully be a bit clearer.

Note: linking to spreadsheet instead of attaching, as it is slightly over the allowed size limits.

Spreadsheet Linkage Click Here

 

I've had a quick look at it...

You've got Feb 2008 right (29 days), but you forgot that since it's a leap year there are 366 days. Same for every leap year.

But essentially I think you've got the formula/theory right.

What I don't like though is that you're not comparing apples with apples though... in your offset scenario, you're starting off $5000 wealthier than your standard loan scenario.

Why, in the standard loan scenario, did you borrow $400 000 instead of $395 000 if you had $5000 lying around somewhere?

But I take if you're just looking into understanding how offset accounts affect interest charges so your theory is valid.

Your saving in your example ($47952) should be equivalent to $5000 earning 7.62%, calculated and compounded daily. Do the maths on that and see what that works out to be.


Edit: In 30 years time (01/07/2037), $5000 -> $49 248. I wonder how we've come up with slightly different numbers.

 

Thanks Glebe, I updated it to reflect what you suggested about the initial $5K off the loan amount for the non offset account and changed the daily interest rate calculations on leap years.

I also accounted for ending the Offset Account option when there was 5K still owing on the loan, as you would simply transfer the balance of the offset account on it at that time to pay the remainder out.

Now i'm really confused, they both come out exactly the same!

Non Offset Total Cost: 1,659,562
Offset Total Cost: 1,664,562 - 5000 = 1,659,562

So, I created a new sheet in the workbook to try a different scenario. What if I had an extra $500 a fortnight, I either put it into my non offset loan as larger payments, or I keep it in my offset account. The results again are surprising:

Non Offset Total Cost: 1,214,327
Offset Total Cost: 1,059,272

Here's V0.2 with the above changes.

 

Borrowing $5k less or constantly keeping $5K in your offset a/c will naturally give you the same results as that is what an offset account does. It reduces the principle on which you have to pay interest... which is what you did by taking out a loan of $5K less.

The advantage of an offset account is that you can put all your income into it and for those days your interest will be lower. If you were to put all your income into the loan itself you would have to do redraws continuesly to pay for your living expenses. Some people even use their credit card to live on so that their cash can stay longer in the offset account. They naturally pay off the full amount on the card when it becomes due so as not to incur an even higher interest rate.

 

Yep, what he said

 

I don't know in what sequence the banks work out daily interest but in your spreadsheet the interest for the day of a repayment is calculated before the repayment is taken into account.

This is true for both your scenarios.

However, in the calculation of the interest in the offset a/c scenario you naturally deduct the amount in the offset a/c first and this already includes the extra $500 you have deposited.

This is what gives you the big difference in the cost. Amazing what a difference it makes.