Over the last few weeks, we have taken the time to look into various uses of some common excel functions. Often, you will not necessarily want to use it in the exact same way but it might give you different ideas and it is often much easier to understand such functions with more concrete examples. In this case, we take a deeper look at the NPER function which calculates how much time will be necessary to either pay back a debt or accumulate a specific amount if circumstances (payments, interest rates, etc) remain constant. It is usually the most realistic way to do things anyway.

In today’s example, we look at a very common scenario. How much time would be required for a consumer to pay back his credit card (or other) debt by paying only a portion of what is owed. Of course, this scenario is often the type of reality test that motivates credit card holders to stay far away from credit card debt. Why? Let’s just say that the 18% or so interest rate makes it very difficult to pay back a large amount.

For the NPER function, what is required is:

-Initial amount: $25,000
-Interest Rate: 18% annual which we will convert to a monthly rate by doing:

=(1+18%)^(1/12)-1

Payments made each peeriod: $350

Then, we will simply compute the NPER function. Take a look at our screenshot:

We used: =ROUND(NPER(C7,C9,-C4,0),1)
Then we changed the result into years= =ROUND(C11/12,1)

It is that easy. For those curious to see it in action, you can download the spreadsheet here. Also, here are the results using a couple of different scenarios:

Paying $500 per month instead of $300: