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?

Take a look at the attached.

Attached Files

Repayment.xlsx (17.0 KB, 10 views)

What about a tiny response?

Apologies!

I only just got back to the office today, and just taking a look at your response now!

I've just opened up your repayment spreadsheet and looking at it now.

Thank you in advance for the time and effort you put in! I'll let you know how i go!