Required Loan Repayment Amount

[Jiing Chiang]

Hi everyone,

I understand the 'PMT' formula can be used to calculate the required loan repayment amounts.

However, I would like to add in the use of an offset account.



I would like to calculate the required monthly repayment amount in order to reduce my loan balance from the existing $600,000 to $200,000 in 5 years' time. The loan has an interest rate of 4.50%, and I contribute $1,000 per month towards my Offset Account (which has a starting balance of $0).

i can use =PMT(4.50%,5*12,-600000,200000), but that isn't entirely correct as it does not take into account the effect of contributing $1,000 towards my Offset Account to reduce the interest payable on the loan.

Anyone have any ideas or able to assist me in any way please?