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

Blogger
Share this content
6

By David Ringstrom, CPA

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:

rate - The interest rate for the loan expressed as a monthly rate.

pmt - The monthly payment, which should always be shown as a negative amount.

pv - The current loan balance

fv - This optional argument allows you to specify a future value if a balloon amount is due at the end of the loan. Omitting this argument implicitly states that the loan is to be paid down to 0.

type - This optional argument allows you to specify if payments are made at the beginning of each period, or you can omit the argument to indicate that payments are made at the end of each period. You may also specify 0 in this position to explicitly indicate that payments are made at the end of each period.

As shown in Figure 1, it will take 36 months to pay back $20,000 with a monthly payment of $586.04 at an interest rate of 3.5 percent. Always be sure that the payment is shown as a negative number, otherwise NPER may show a slightly longer payment term. I omitted the 2 optional arguments, so in this case, the PMT function assumes the loan is paid to 0 and payments are made at the end of each period.


Figure 1:
Use Excel’s NPER function to calculate the payment term for a loan.

For longer term loans, NPER may return a large number of months, such as 94, which can be difficult to convert to months in your head. Let’s extend our calculations to make the output more user-friendly.

First, as shown in Figure 2, we’ll add the ROUNDUP function to our NPER formula. Loan periods will typically involve some fraction of the final month, which for our purposes we want to treat as a whole month. The ROUNDUP function rounds a number up, as opposed to the commonly used ROUND function that may round numbers up or down.

ROUNDUP has two arguments:

number – In this case the result of NPER will be our number

num_digits – in this case we’ll use zero, as we wish to round up to the next whole month. If you wanted to round a number to say the nearest thousand, you’d use -3 instead.


Figure 2:
Use the ROUNDUP function with the NPER formula to convert to months.

Next, we need to convert the result that ROUNDUP/NPER return into a number of years and months. To do so, we can use the TRUNC function. This function converts a number to an integer by removing the decimal or fractional portion. You could also use ROUNDDOWN and specify zero as the number of digits to accomplish the same effect.

To calculate the number of whole years in the loan, we can use this formula:

=TRUNC(B4/12)

Or this would work as well:

=ROUNDDOWN(B4/12,0)

In either case we’re taking the number of periods returned by NPER, dividing it by 12, and then truncating the decimal places.

Use this formula to calculate the number of months remaining after the whole years:

=ROUNDUP(B4-TRUNC(B4/12)*12,0)

We can then string all of this together into a tidy format, as shown in Figure 3:

="months, or "&TRUNC(B4/12)& " Years, "&ROUNDUP(B4-TRUNC(B4/12)*12,0)&" Months"

In this we’re using the ampersand to concatenate, or join together, text and calculations into an understandable phrase. I much prefer using the ampersand to join text together, but if you’re a fan of Excel’s CONCATENATE function, the formula would take this form:

=CONCATENATE("months, or ",TRUNC(B4/12), " Years, ",ROUNDUP(B4-TRUNC(B4/12)*12,0)," Months")


Figure 3:
Use the ampersand sign to concatenate text and calculations into a logical phrase.

Read more articles by David Ringstrom. 
 
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Replies

Please login or register to join the discussion.

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.

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.

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?

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.

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.

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.