Debt is the American way. Six months ago, you whipped out your credit card again to buy that
state-of-the art computer for little Johnny so he could
get his homework done. It only cost $1500. Or did it?
If it feels like you are sinking into a quagmire of
debt, here’s something else you can do with that new
computer: use it to figure out what things
really cost you.
Even if you aren’t a spreadsheet whiz, figuring
out what is really going on with your credit isn’t
difficult. The simple spreadsheet in the figure below can
be the first step in an eye-opening financial exercise.
Our spreadsheet shows two separate situations. The first one shows the dangers of making
minimum payments and what would happen if you
increased your credit card payment. Under Scenario 1, you
input the price of Johnny’s new computer, i.e., $1,500.00 (in parentheses because money you owe
is a negative), the interest rate on your credit
card, 17%, and the minimum payment of $30.00. In
Excel, you format numbers to tell Excel the type
of number. So, you format the dollars by
highlighting the text in the cell and clicking the dollar sign ($)
on the toolbar. Similarly, you format the percentage
rate by highlighting it and clicking the percent sign (%).
Now you get to the fun stuff. You want to find
out how many payments it would take to pay off that
new computer if you made the minimum payment. So you put this formula in C9 to calculate it:
=ROUND(NPER((C6/12), C7, C5),2)
The ROUND function rounds the payments to two decimal places, and the NPER function
calculates the number of periods it would take to pay
off the debt. You divide the interest rate (in cell C6)
by 12 to get a monthly rate. Then you use the NPER function and pass it the monthly interest rate,
the payment (C7), and principal amount (C5) to get
the number of periods it would take to pay off. With
our sample data, that result is 87.59, which is more
interesting if you find out how many years that is. So
in cell C9, you divide that number (C8) by 12 and round it to two decimal places. You learn it
would take more than 7 years to pay off Johnny’s
computer! If you multiply the number of payments (C8)
times the payment amount (C7) you find Johnny’s
computer actually cost $2,627.70—$1,100 more than
you thought you paid for it! The second column
shows what happens if you were to double the payment.
Okay, so you decide that seven years is too long
to pay off the computer and even 2.59 years is
depressing. So the next section calculates how much
you have to spend to pay off the debt in two years
and one year. To do this, you use this formula in C16:
=PMT((C14/12), C15, C13)
You use the PMT function and pass it the
interest rate (the APR in C14 divided by 12), the total
number of payments (C15), and the principal (C13).
You discover that to be free of this debt in two
years, you’d have to pay $74.16 and only about $200 in
interest. Paying in one year looks even better.
The moral of the story is paying minimum payments won’t get you out of debt. If you get
depressed, remember the reason you got in debt was that you spent more money than you had. And
the only way to get out of debt is to do the reverse.
