Project 4: Line charts & debt payments

The United States government relatively recently passed some credit card reforms, requiring credit card companies to tell borrowers how long it will take to pay off their debts if they only pay the minimum balance. But what if you pay $1 more than the minimum balance? How much will that affect it?

It’s actually pretty easy to figure out and graph, if you have a spreadsheet. You don’t even need to know any fancy formulas like the online calculators use. All you need to know is what the current balance is, what the interest rate is, and what amount you want to consider paying. The graph below was made with the assumption of a current balance of $1000, an interest rate of 18%, and a monthly minimum payment of $17.50. Look! You’ll have paid off the $1000 in only 11 years! And paid approximately 17.50 per month*12 months per year * 11 years = $2310 total, which means 2310-1000=$1310 in interest. ….uhm, really? Yes, really.


To make this chart, the first step is copying this table and pasting it into your spreadsheet. Then name all the cells appropriately. If the formula in the third line isn’t showing 0.015, try checking to see if there’s a quote mark (‘) before the equal sign(=) in the formula bar, and if it’s there delete it.

Starting Balance 1000
Yearly interest 18
Monthly Interest =yearly_interest/100/12
Monthly Payment 17.5

Now pick three columns, right next to each other. I’m going to assume that you’re using columns d, e, and f, but if you use different columns just change the formulas to reflect that.

In cell d1, enter the text “Year”.
In cell e1, enter the text “Balance”.
In cell f1, enter the text “Interest”.
These are your column headers.

In cell d2, enter 0. (the number zero)
In cell e2, enter the formula “=starting_balance” (or whatever you named the cell with 1000 in it).
In cell f2, enter the formula “=e2*monthly_interest” (or whatever you named the cell with 0.015 in it).

In cell d3, enter the formula “=d2+1/12”. That’s saying to take the value in d1 (which is zero), and add one-twelfth to it. This is to reflect 1/12th of a year, which is to say one month. You could also just make it =d2+1, but then you’d have to remember that 12 was the end of year 1, and figure out that 132 was the end of year 11.

In cell e3, enter the formula “=e2+f2-monthly_payment” (or whatever you named the cell with 17.5 in it). This gives you the previous month’s balance, plus the interest that accumulated during the previous month, minus the monthly payment you make this month.

In cell f3, copy and paste the formula from f2. It should now read “=e3*monthly_interest”

For rows 4 through however far you need to go for the balance column to reach zero (or negative numbers), copy and paste row three. You can do this all in one chunk by highlighting all the cells you want to paste into, and then pasting just once. It will paste in all of those cells — you don’t have to do each one individually.

Then just make a line chart and add titles and labels. When the line reaches the horizontal axis, that’s when the debt’s been paid off. So you can now change the amount in the “monthly_payment” cell, and watch the intersection point move closer to zero. Even fifty cents makes a difference…instead of 11 years the debt is paid off in 10 years and a month, and the amount of interest paid drops to 18*(12*10+1)=2196-1000=1196, for a total savings of $114.