is there a formula to calculate the no. of payments "P" required to pay off a loan amount "X", with an interest rate "Y" and a monthly payment amount "Z". I think "X/Z" would be an approximation for the the no. of payments. But i needed to account the interest rate "Y" getting applied on the remaining loan amount each month.

  • What type of interest? Simple? Compound? If compound, what's the compounding frequency? Oct 28, 2014 at 15:15
  • would you comment on both simple, and compound with quarterly compounding?
    – Cyril
    Oct 28, 2014 at 15:21

1 Answer 1


At 0%, you are right, X/Y it is. Divide the Principal (original loan) by the payment, results in the number of payments due.

As rates rise, to, say 4%. A 30 year $100K loan will have a total of $171,871 paid over that time.

There are a multitude of on line calculators to answer your question. In my opinion, getting familiar with spreadsheets is the way to go. Excel or OpenOffice will let you create a few cells that will let you solve the missing variable. Payment for a given Loan, Rate, and Time is most common, but calculating time left is pretty trivial as well.

NPER() is the function that will return the number of payments given the other info. See Documentation/How Tos/Calc: NPER function for the implementation of this on OpenOffice, a free, open source, spreadsheet application.

  • Excel and Google Sheets, at least, support the =NPER() function
    – Noah
    Oct 28, 2014 at 16:09
  • 1
    @Noah - I updated my answer. Excel is the classic software for this but is costly. Google works fine but is not resident. I like that OpenOffice is free and just runs with no connection needed. Oct 28, 2014 at 16:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .