When we looked at mortgages, the payment amount was set so that the balance was paid off in 30 years. When you carry a balance on a credit card, the credit card company isn’t going to promise a payoff date. The time needed to pay off a $1,000 balance is going to vary based on the interest rate and the policies of the credit card company. Each credit card company sets your minimum payment slightly differently.
About 15 years ago, some credit cards actually had a minimum payment so low that you would never pay off your balance! If you pay less than the interest amount each month, your balance increases. This is not good.
I’m not sure whether this is still legal or not. But it almost never happens anymore, because of a clever change in the law. In 2010, the Federal Reserve started making credit card companies tell you how long it will take to pay off your balance:
Your monthly credit card bill will include information on how long it will take you to pay off your balance if you only make minimum payments. It will also tell you how much you would need to pay each month in order to pay off your balance in three years
If you would never pay off your balance, your credit card statement would need to say that, and I don’t think many companies want to say that outright! Let me know if you ever see an example of this, though.
Here’s what the new information section can look like:
Here’s the question: If you make no additional charges and pay the minimum amount each month, how long will it take you to pay off the balance? The Federal Reserve already forced your credit card company to tell you the answer: At the minimum payment level, it will take you 20 years to pay off this balance. We’re going to confirm this now.
You need some additional information before you can start working. First, the interest rate is a whopping 17.99%! That information is buried at the very end of the statement:
In this example, the account holder is paying their balance off every month, so there’s no interest being charged. Good thing, because 18% is just a silly interest rate to pay. Let’s assume from here on out you only make the minimum payment, and don’t make any additional purchases on the card.
This credit card company’s policy is that the person must pay 1% of the principal balance each month, plus whatever interest is charged. The sum of these is of course greater than just the interest payment, so the balance will go down.
But, if you keep paying 1% of the balance off each month, you never actually finish paying it off! As the balance gets smaller, your payments also get smaller. At a $100 balance, you’d pay $1 (1% of the principal) plus $1.50 in interest (18% a year is 1.5% a month, ignoring compounding.) That’s just $2.50 total. At $1 you’d pay 2.5 cents. You’d never get to $0!
To fix this problem, the credit card company set a minimum payment amount of $25. As soon as you get below a $25 payment, the percentage of the principal that you pay off each month increases. And at a balance of $24.63 or so, you’d still pay that minimum of $25, which would bring your balance to $0 ($24.63 in principal plus $0.37 in interest.)
This spreadsheet will be more complicated than the mortgage spreadsheet, since we need to determine whether or not that $25 minimum kicks in for any given month. And, it’s going to have a lot more rows since we’re going to look at monthly payments for the first time.
First, create a month column, and make sure you have 240(!) numbers there. Add in the starting principal, and the payment (before interest) of 1%.
I probably should have labeled column C something besides Payment, since this isn’t your total payment. It’s really just a working column … maybe “Working Principal Payment”?
Next, let’s add in our constant for the interest rate, and then the interest. The sum of the Payment plus Interest is the actual payment, unless that’s less than $25. We’re going to use the Max function, which returns the larger of a set of values.
Here are a couple of examples of how the Max function works:
=Max(1, 3) returns 3.
=Max(3, 1) also returns 3.
=Max(3,1,3,4,3,1,2,3,4,1) returns 4. Max doesn’t care about the number of numbers in there, or their order, or if there are duplicates.
In the first row above, the formula in E2 for the actual payment simplifies like this:
=MAX(25,C2+D2)
=MAX(25, 56+85.15)
=MAX(25, 141.15)
=141.15
Next month, the comparison will be:
=MAX(25, 140.31)
The second number keeps getting smaller until eventually 25 is larger. Then MAX will return 25 for the rest of the rows.
Now all we have to do is set next month’s principal lower by the amount of principal we paid off. Note that we don’t apply the total payment as a reduction in the principal– the interest payment just goes to the bank!
Here’s where that $25 minimum gets confusing. The amount of principal we pay down isn’t just the value in column C. It is exactly that value for the first payments. But eventually, once the $25 minimum kicks in, the principal payment is more than the small value in Column C. Scroll down and see when that $25 kicks in:
Notice I used the Freeze Panes button. My cursor was in cell A2 before I pressed Freeze Panes. This leaves row 1 frozen, so I can see my header labels as I scroll down. (If you’re using Google Sheets, click on a call in row 2 and then choose View–> Freeze–> 1 row.
Notice how once the actual payment is fixed at $25, the amount of that $25 that goes to principal increases each month. This makes sense: as the balance goes down, the interest I owe goes down. Since the payment stays fixed, more of that payment can go to principal. You can graph column F to see how this kicks in:
I forgot to answer the question. How long does it take to pay off your balance? Scroll down and see when the principal is finally paid off. I see the last positive balance at month 235. Divide 235 months by 12 and that’s 19.583 years, which rounds to 20 years. Just like the credit card company told us!
This isn’t so important, but you can try one more thing if you don’t have spreadsheet fatigue yet. Notice that the minimum payment in the first image was exactly $56:
The credit card company makes you pay 1% toward the principal, but they round down to the nearest dollar. We can use the ROUNDDOWN function to get even more accurate:
=ROUNDDOWN(B2*1%,0) says to round the value down, and leave 0 decimal places. The truncate function would also do the same thing: =TRUNC(B2*1%,0)
Now our principal payments are a little bit lower each month, or at least never higher. So it should take even longer to pay off the balance. And when I scroll down, now it takes until month 239 until the balance is paid off. 240 is exactly 20 years, so we’re right at 20 years now.
You can use the spreadsheet to see how much you paid in interest. I see about $7,686 in interest, and about $13,366 of total payments (interest plus the initial principal amount.) This is very close to the image above; it’s within $60 of the value the credit card company says.
These numbers aren’t that different from a mortgage! At 6% we paid a $117,000 in interest on a $100,000 principal. Here we paid $7,686 in interest on a $5,680 balance. The difference is the 1% principal payment each month. At the beginning of the mortgage, you’re only paying off 0.11% of the principal each month! Lower payments mostly cancel out the lower interest rate.
But there is one fundamental difference between a mortgage and credit card debt: Your mortgage is paying for an asset that usually retains or increases in value, which most credit card purchases decrease in value!