Mortgage Calculator: P&I repayments calculator with Offset Account?

[l2oBiN]

Hi Guys. I am trying to get a monthly loan/mortgage calculator that takes into account funds in an offset account.

By using the PMT on the total loan (excluding the offset balance) I get the monthly repayments, but I can't seem to work out how I can get the total interest payed given the offset account balance. Any ideas?

[curiouscat408]

I am not familiar with loans with offset accounts. But the following matches the results (attached) at https://www.ing.com.au/home-loans/ca...rs/offset.html .


loan with offset acct.jpg
Key formulas:
G4: =IF(E4="", "", MAX(0, H3*$B$4))
L4: =IF(J4="", "", MAX(0, M3-$B$6)*$B$4)
Other formulas:
B5: =PMT(B4, B3, -B2)
E4: =IF(N(H3)<=0, "", E3+1)
F4: =IF(E4="", "", MIN(H3, $B$5-G4))
H4: =IF(E4="", "", MAX(0, H3-F4))


See details in the attached Excel file.

That makes the simplifying assumption that the offset balance remains constant throughout the loan term.

That defeats one of the benefits of an offset account, to wit: ``Because the offset account acts like an everyday account, your [account balance] is still accessible whenever you need it, even while it's working to reduce your overall interest payments.`` (Ref: https://www.commbank.com.au/articles...t-account.html )

And of course, presumably the offset account grows monthly by interest earned.

So, in reality, I suspect that the monthly loan interest is based on the previous loan balance minus the average daily offset balance.

Mathematically, that is the same as the sum of the daily interest based on the daily loan balance minus the daily offset balance.

In either case, the amortization schedule is more complicated.

And according to the second cited article, there might be other factors to consider, to wit:

1. Is 100% of the balance offset against the home loan?

2. Is there a limit to the [loan?] balance of the account that will be offset?

3. Are there any account-keeping fees [...]?


-----

[l2oBiN]

I am not familiar with loans with offset accounts. ...


-----

Thank you so much. I am trying to workout why my PMT and manual calculation are different? I must be doing something wrong?


https: //1drv.ms/x/s!AsFQhtcXKc0ehhWKniprX5QZqtPO

[curiouscat408]

I would like to take a look. But when I enter that URL into a browser, it does not work.

Please follow the instructions at the top of this webpage for attaching your Excel file to a posting in this forum.

-----

[ERRATA] My bad! I did not notice the errant space in the posted URL; presumably your attempt to work around this forum's limitation.

When I correct that, I am able to open your Excel file.

Just about everything in your manual calculations is wrong. In a nutshell, it appears that you did not study -- or understand -- the Excel file that I posted previously.

No time to explain now. I'll be able to post a play-by-play later.

[curiouscat408]

[.... deleted by me; irrelevant ....]

[curiouscat408]

I am trying to workout why my PMT and manual calculation are different

This question has nothing to do with the original posting (loans with offset accounts).

Instead, it has to do with a basic understanding of how (regular) loans are amortized.

Your example is essentially the following:


pmt vs mnl calc.jpg
PMT Formulas (left):
B6: =B5*12
B7: =B4/12
B10: =-PMT(B7,B6,B3)
B11: =B6*B10
B12: =B11-B3
Manual Formulas (right; copied from left except as noted):
D8: =D3/D6
D9: =D3*D7
D10: =SUM(D8:D9)


The flaws in your manual formulas are:

1. The formula in D8 assumes that the monthly principal payment is equal, namely: initial loan amount divided by number of payments

2. The formula in D9 assumes that the monthly interest payment is equal, namely: initial loan amount times monthly interest rate

Neither assumption is correct, which should be apparent if you look at columns F and G of the example attached to my previous posting #2.

Instead, the equal monthly payments calculated by Excel PMT assumes that the payments are amortized based on the "actuarial method".

Thus, the amount of principal and interest varies each month. That is:

1. The monthly interest payment is: the previous outstanding loan balance times the monthly interest rate

2. The monthly principal payment is: the equal monthly payment minus the calculated monthly interest payment

That results in the following algebraic formula (making some simplifying assumptions):

PMT = loan*(1+rate)^nper * rate / ((1+rate)^nper - 1)

where "rate" is the monthly rate, and "nper" is the number of monthly payments.