Many people are painfully aware that when you buy on credit you can end up paying a lot more than you expect. This lesson shows how to create an Excel worksheet for calculating the true cost of paying with credit.
In this lesson two credit card payment worksheets will be created: one that assumes you make a fixed monthly payment and another that assumes you pay only the minimum amount each month. Both worksheets will calculate the total interest paid for the privilege of borrowing.
Interest is charged each month you carry a balance on your credit card. Part of your monthly payment goes toward interest and the rest goes toward principal which lowers the remaining balance. Here is a simple example:
Figure 1. Simple Credit Card Payment Calculator
At the start of the month you charge $500 on a credit card that has an APR (Annual Percentage Rate) of 12%. The annual percentage rate is the rate that is charged each year. The monthly rate is APR/12, or in this case 1%. As the example in figure 1 shows, at the end of the first month, you are charged $5 interest (1% of $500). Assuming a payment of $40, $5 of the payment would go toward interest and the remaining $35 would go towards principal which is subtracted from the balance. The next month works the same way. Interest is calculated on the remaining balance and a portion of he payment goes toward interest and the rest toward principal. Each month as the balance drops less goes toward interest and more towards the balance of the loan.
In this section we will create a worksheet that shows the cost and length of time to pay off a credit card balance when making fixed monthly payments.
Assume you have a balance of $1200 on a credit card with an APR of 17.5% and you plan to make monthly payments of $100.
Here is the initial setup with just the input data from the problem:
Figure 2. Initial Setup
The hardest part of creating the worksheet is getting the first row of our payment table correct. Once we get it correct, you can fill the row down until the balance goes to 0.
Here is the worksheet showing the values for the formulas in the first line:
Figure 3. Formulas needed to create payment table
Month is just the previous month +1. Payment is an absolute reference to the fixed payment in cell c5. Interest is APR/12 times balance. Principal is payment minus interest rate. The new balance after the payment is old balance minus principal.
The next step is to fill down row 9. To fill down row 9, select cells A9:E9, grab the handle in the lower right-hand corner and drag down.
Figure 4. Filling down row 9
Now, filling down row 9 creates the payment table. Notice that we overshot the end of the table. The loan is paid off after only 14 payments. (The 14th payment will be less than $100.)
Figure 5. Balance is paid after 14 payments
We can now delete the extra two rows in the table and change payment 14 to be previous balance plus the current month's interest. We also use the SUM formula to sum column C which give us the total interest paid over the life of the loan.
Figure 6. Final result.
The conclusion is, if you charge $1200 on a credit card with an interest rate of 17.5% it will take 14 months to pay it off in fixed payments of $100 a month and you will pay a total of $128.86 in interest.
Most, if not all, credit card companies require a minimum payment each month. The minimum payment is a certain percentage of the balance (before adding the interest for the month. For example, if the minimum payment is 3% of the balance of your account and you owe $500, you would have to pay at least $15 at the end of the month (3% of $500 = $15). To avoid a nearly endless stream of very small payments towards the end of a loan, most credit card companies require a certain percentage of remaining balance or fixed payment (usually $10) whichever is larger. For example, if you had a balance of $300, the credit card company would ask for a flat minimum payment of $10 rather than 3% of $300 which is $9.
The setup for this worksheet is nearly identical to the example above:
Figure 7. Setup
The biggest difference is in the first row:
Figure 8. Formulas for minimum payment each month
Notice the IF() formula in B9. It just says that if the calculated minimum payment (3% of balance) is less than $10, make the payment $10. The rest is the same as the example above.
Now, filling down we get a very long payment schedule. Here are the first 12 payments:
Figure 9. First 13 payments
And the last few with a summation of interest paid:
Figure 10. Last few payments and total interest paid
The example shows it takes a considerable amount of time to pay off a credit card balance when making minimum monthly payments.
If you charge $1200 on a credit card with an interest rate of 17.5% and pay the minimum each month, it will take 129 months to pay it off and you will pay a total of $946.32 in interest.