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:
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.