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? – ChrisInEdmonton Oct 28 '14 at 15:15
  • would you comment on both simple, and compound with quarterly compounding? – Cyril Oct 28 '14 at 15:21

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 '14 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. – JTP - Apologise to Monica Oct 28 '14 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.