Loans. They’re a fact of life. Car loans, personal loans, credit cards, and home loans — once they’re taken out, they’re going to have to be paid back.
But how long is it going to take you to pay them back?
The best way to ensure that your finances are healthy is to have a financial plan. So, it’s time to load up the ol’ spreadsheet and figure out how much you need to pay down on your loan.
It’s easy — thanks to the Google Sheets NPER function. The NPER function will tell you how long of a period it’ll take you to pay off a debt, given a static payment and static interest rate.
To make your life even easier, we’ve created a free downloadable NPER function template, which you may find at the end of this article.
This Article Covers:
What is the NPER Function?
The NPER function calculates how many payments you’ll need to make to pay off a balance, given a static interest rate. There are a couple of important limitations to the NPER function:
- Your interest rate cannot change (be variable) over time.
- You will need to define your interest rates and the payments over the same interval of time (such as annual or monthly).
But if you are able to do these two things, the NPER function is very useful. With the NPER function, you’ll be able to see exactly how many payments you’ll need to make to clear your balance. Just stay on track, and you’ll be done in no time at all.
How Do You Use the NPER Function?
Let’s start building our spreadsheet. Let’s say that you currently have a credit card with a $20,000 balance (oh no!) and you want to pay $5,000 down on it a year. You would think intuitively that it’ll take you four years to pay off, but that’s not true — you have 12% annual interest to think about.
On this spreadsheet, we have entered manually:
- Your current card balance.
- Your annual interest rate.
- Your annual payment.
Note that it is an annual interest rate and an annual payment. Note also that the payment is a negative amount (in parentheses), not a positive amount. Since you are paying down the account, you need to have the payment be negative.
From those manual variables, we have created our Google Sheets NPER function:
Let’s completely break down the NPER formula:
NPER(rate, payment_amount, present_value, future_value, end_or_beginning)
- Rate: the interest rate.
- Payment amount: payment you’re making.
- Present value: how much you owe right now.
- Future value: how much you’re hoping to owe.
- End or beginning: when payments are due.
Most of that is self-explanatory, except perhaps “end or beginning.” By default, the value here is “0.” Let’s turn it to “1” (Beginning) and see what happens.
It shaved a whole year off! Why? Because this indicates that you’re making your payment now (Jan 1) rather than later (Dec 31). Not only does that mean that you have a year less of payments, but it also means that less interest compounds.
Using NPER to Calculate Your Total Payment
As you can see on our sheet, we’ve also calculated total payment. NPER can be used for this purpose; you just multiply the exact NPER by the payments you’re making.
In this case, we can see that over the course of the next six years, we’re going to pay about $8,000 in interest. That’s a lot, but at least it’s spread out. We can also calculate that the total interest paid is about 44.25% of the initially borrowed amount.
Changing Our NPER to Monthly
Well, obviously a credit card bill is due monthly rather than being due annually. So, maybe we would rather know how much we have to pay every month. All we have to do is change the interval of both the interest rate and also the interval of the payments.
Now, you might notice that the numbers are a little different. There’s a reason for that. Because you’re paying down the balance faster, you’re paying less interest. Our first calculation was annual which meant you had a full year of interest before the first payment. Now, you only have a single month of interest before the first, smaller payment.
It still rounds out to about 6 years to pay, but only just barely — it’s closer to five and a half years. Over the entire time, you’re paying closer to 40% in interest rather than 44.25%.
Calculating a Partial Pay-Off With Google Sheets NPER
Okay, but what if we want to know how long it will take us to pay off half of the balance? We can do that, too. When we first declared NPER, we used “0” as the desired balance, because we wanted to know how long the loan would take to completely pay off. We can modify it like so:
And then we get the following result:
There are a few things to note about this. First, the “future_amount” is a negative number. Otherwise, it won’t calculate properly.
Second, we can see that it’s going to take about 3 years to pay off the loan halfway. That makes sense. But we also note that we’ve paid about $6,000 in interest already. That’s correct.
Because of compounding interest, you will always be paying more interest and less principle at the beginning of a loan, and less interest and more principle at the end of the loan. Once you reach that halfway point, you’re paying interest on $10,000 rather than the $20,000 at the beginning and this continues to go down.
Can You Use NPER for Something Other Than Loans?
You might have noticed that the amounts you owed were expressed as negatives. You can use NPER to calculate positives as well — such as how much you might want to save for retirement. Take a look at this sheet:
Here, we’ve calculated that it will take 17 years to save $250,000, starting with $20,000 and paying $5,000 each year (and gaining 8% annually). That’s a lot of time, but look at the calculations. You’re only paying $86,000 or so and making a total investment income of around $160,000.
Let’s take a look at the NPER calculations we used.
All we had to do was change the signage of the payments and the goals (from negative to positive and vice versa) and we were able to calculate earnings rather than payments.
Calculating How Much to Pay With PMT
All that being said, a lot of people don’t determine how much to pay and then calculate out how many payments there will be. Rather, a lot of people want to know how much they need to pay so they can pay off a balance in a certain amount of time.
Let’s say you don’t want to spend six years paying off that $20,000 balance. You’d rather pay it off in 4. To do that, you’ll use the related PMT function.
Here, we’ve started with three numbers:
- The current balance.
- The monthly interest.
- The number of months we want to pay the balance off in.
From there, we’ve used the PMT function.
The PMT formula goes like this:
PMT(rate, number_of_periods, present_value)
In this case, that is 1%, 48, and $20,000. The PMT formula will tell you that you will need to pay $533.30 every month to pay off the balance in 4 years. That’s not a lot more than you were paying in the prior calculations, but it shaves off about 2 years of time and $3,000 in interest.
With NPER and PMT, you can do pretty much anything you need to in terms of calculating your loan payoff amounts and your loan payoff dates.
Google Sheets has quite a few functions that you can use to do extremely complex financial calculations. You can use Google Sheets for practically all your financial spreadsheet needs.
Download our Free NPER Google Sheets Template
If you’re trying to use the NPER function, you can get started with this NPER template.
Copy the template to your own Google Drive account and you can edit the template for your own credit card or loan. The NPER function should work to figure out how many payments you need to make to clear your balance — regardless of the type of debt.
For nearly any financial function, Google Sheets will have a correlative function. This also includes tracking investments. In addition to being able to see how much money you need to pay down on a loan, you can also use Google Sheets to see how much you would need to pay toward, for instance, your retirement.
Related reading: NPV Google Sheets Formula
