Required loan repayment amount

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?

Hi there,

Take a look at the attached workbook and see if it does what you need.

The input values are highlighted in blue, and the monthly repayment is calculated by clicking on the "Calculate" button.

Hope this helps - please let me know how you get on.

Regards,

Greg M