ISP 120

Credit Cards

Learning Goals for this Activity

  1. Be able to calculate an amortization table for a credit card loan in Excel
  2. Be able to calculate how much you need to pay in order to pay off a credit card loan in a set amount of time.
  3. Be able to model various realistic scenarios involving a credit card loans.
  4. Be able to appreciate that, because of the high interest rates and very low minimum payments, credit card loans can be very costly and can take a very long time to pay off.

1. You make a purchase for $2,500 on your credit card which has an annual interest rate of 21%. If you pay the minimum payment of 2% per month (not less than $25),

a. What is your balance after 3 years, assuming you make no other purchases with your credit card? 

    Make a table in Excel that looks like:

Month Credit card balance at beginning of month Payment at end of month Interest for month Credit card balance at end of month
1  $2,500.00 =MAX(B2*0.02,25) =B2*0.21/12 =B2-C2+D2
2 =E2 =MAX(B3*0.02,25) =B3*0.21/12 =B3-C3+D3

and continue the pattern. The Excel function MAX will calculate which value is the greater option between minimum payment of 2% or $25.

Paste the first 10 rows of this table into your Word document.

b. If you only paid the minimum each month (and didn't make any additional purchases!), how long would it take to pay off the balance?  (HINT: complete the previous table until your balance reaches zero). How much interest would you pay altogether? (HINT: look at column D.)

2. You only use your credit card in the case of an emergency. You charge $2,000 in auto repairs to your credit card, which has an annual interest rate of 9.99%. 

a. What should your monthly payment be if you would like to pay off your balance in full after one year?

b. What would be your total payments on the credit card loan?

3. You take a vacation once a year and charge it to your credit card and pay it off in 6 months. You have the option to apply two credit cards. One credit card has an annual rate of 7.99% with an annual fee of $20 and the other credit card has an annual rate of 9.99% with no annual fee. The total cost of your vacation was $3,350. Assuming that this is the only purchase you make all year, which credit card should you apply for? Explain. (Hint: Assume that the credit card fee is paid separately and is added to the total interest paid after six months.)

4. Assume that you have a balance of $1,200 on a credit card that carries an annual percentage rate of 18%.   You start making monthly payments of $200, but at the same time you charge an additional $75 per month (assume that this charge is made at the end of the month and interest is calculated on the balance before the charge is added). 

Make a table that shows month, beginning balance, payment, interest, charge amount, and end balance.     

            a.  Calculate the first six months and paste this table in your word document.
            b.  Assuming that the scenario above doesn't change, how long will it take to pay off the credit card debt?

5. Repeat problem 4 but assume you make monthly payments of only $125.  Assuming that the scenario above doesn't change except for the payment amount, how long will it take to pay off the credit card debt?

6. Your best friend just received a credit card offer through the mail. She asks you for advise about credit cards. What kind of tips can you offer her to avoid credit card trouble? Make a list of at least three tips.