Excel =pmt formula

[Leon Erasmus]

My client owes the bank 5988.77. Over 12 months at 23% using =pmt this is 563.36 per month. I need a formula or a method to calculate how many months will be required if the client can only pay say 70.32 per month. Neither Tools / Goal seek nor Tables seem to work for me. Any ideas would be welcome. Thanks!

 

[Colin Legg]

Hi,

If the client can only afford monthly repayments of 70.32 then the client will never be able to pay off the amount owed because the monthly interest is greater than the repayments. The debt will just continue to grow.

Colin

 

[Leon Erasmus]

Thanks for that. But the 70 odd is just an example. Lets use any other 'reasonable' amount as the payment. I'm more interested in the formula or methodology rather that the actual numbers.

 

[Colin Legg]

Sure, instead of using the PMT() worksheet function, you can use the NPER() worksheet function.

If the payments are monthly, don't forget to divide the annual interest rate by 12.

So, say monthly repayments of 200:

=NPER(23%/12,-200,5988.77,,0)


HTH,
Colin

 

[Leon Erasmus]

Thank for that Colin