Excel Tip: Determining the Remaining Length of a Loan Using NPER

Spreadsheets and graphs on a desk
xfgiro/istock
6

Previously I’ve explained how you can use worksheet functions in Excel to determine the payment for a loan, as well as how to calculate total interest in a single worksheet cell. This time around I’m going to use the NPER function in Excel to show you how you can determine just how long it will take you to payoff that credit card bill on which you’re making monthly payments.

You can get a refresher on the PMT and CUMIPMT functions, but any typical loan has four key values:

  • Interest rate
  • Term of loan
  • Amount borrowed
  • Monthly payment

As discussed in my previous article, if you have the interest rate, term, and loan amount, you can then use the PMT function to solve for the payment. Sometimes, such as with a credit card balance, you’ll know the interest rate, payment, and loan amount, but not the term.  In this case, Excel’s NPER function (short for number of periods) enables you to calculate the fourth value.

NPER has 3 required and 2 optional arguments:

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Share this content

Replies

Please login or register to join the discussion.

avatar
By sana
Jun 26th 2015 01:11

What if we need to find out how much time it will take to reach a certain future value, can we specify that value in fv.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

Great question. Yes, exactly, use the FV argument, but make sure to list the FV as a negative value. In the example show, if you wanted to pay the loan down to $5,000, you'd put -5000 in the FV argument.

Thanks (0)
avatar
By Tom Culhane
Jun 26th 2015 01:11

After using the NPER function I then used the PMT function to recalculate the amount. I expected to get 586.04 as in your figure 1 but got 588.51 instead. Am I misunderstanding how these functions should work?

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

You raise a great question, Tom. I looked back and I don't see to have the source spreadsheet that I used to put together the calculations. I can confirm that based on a loan of 3.5%, term of 36 months, and $20,000 principal that the PMT function returns $586.04. Now it's possible that the NPER function isn't returning exactly 36, but instead returning 35.9 or something that would shorten the term slightly and result in a higher payment. However, it appears that I hard-coded the 36 months in the PMT function, because Excel doesn't allow me to have the PMT and NPER functions rely on each other simultaneously. Feel free to upload an example of your spreadsheet via the Upload a Project File at www.acctadv.com and I'll be glad to take a look.

Thanks (0)
avatar
By Tom Culhane
Jun 26th 2015 01:11

I now realise that I'd overcomplicated my NPER and PMT formulas. I went back and used your simpler NPER formula and got the correct term. Thanks for your original post and reply, you've helped me think through and correct my faulty logic.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

That's great! There's huge value in explaining an Excel problem to someone else. Sometimes just trying to explain the situation casts a different light and the solution pops out of the woodwork. Thank you for following up and letting me know the outcome.

Thanks (0)