Credit Card Calculations in Excel

Assume you have a balance of $1000 on a credit card with an APR of 12%, or 1% per month. You start making monthly payments of $200, but at the same time you charge an additional $100 per month to the credit card. Assume that interest for a given month is based on the balance for the previous month. The following table shows how you can calculate your monthly balance. Complete and extend the table to show the balance at the end of each month until the debt is paid off. How long does it take to pay off the credit card debt?
1. First, calculate the periodic interest rate (monthly interest rate in H2. If you have done this correctly it should be 1% (as stated above).
2. Use cell references to calculate interest in Month 1 (in cell D3). This should be written in such a way that you can fill it down in column D to calculate the interest each month. Remeber that this interest is on the balance from the PREVIOUS MONTH (it does not account for the new expenses or payment).
3. Use cell references to calculate the New Balance for Month 1 (in cell E3) with a Principle of $1000 (Month 0), a payment of $200 and new expenses (adding to the principle) of $100. This should be written in such a way that you can fill it down in column E to calculate the New Balance each month.
4. Extend this table until you pay off the balance. Think: What does that mean you need in the New Balance Column? Highlight the last row as your answer to the question: How long does it take to pay off the credit card debt?
5. Find the total amount paid (total of the payments) and the total amount paid in interest at the bottom of your payment column and interest column, respectively.
6. Insert a textbox at the bottom of the table and do the following reflection:
a. Do you think this was an effective way to handle their credit card debt of $1000?
b. What do you think they should have done differently?
c. What did you learn by doing this exercise?