Final Assignment:
Part 2
by
Margo Gonterman
Credit Card Exploration
You initially charge $5000 to a credit card on January 1, 2009. The credit card charges 20% interest annually and requires you to make a minimum payment of 5% of your balance or $50, whichever is greatest.
Creating an Excel File
Make Column A the Month column
Make Column B the Year column
Make Column C the Previous Balance column
Make Column D the Interest column
Make Column E the Payment column
Make Column F the Balance column
Minimum Payment
The Previous Balance column should equal the previous Ending Balance entry.
The interest column should equal 20% divided by 12 of the Previous Balance column.
The stated interest rate is annually, but we are calculating interest monthly. Thus, we must divide the yearly interest rate by 12.
The Payment column should be the maximum of $50 or 5% of the sum of the Previous Balance and the Interest.
The Ending Balance should be equal to the Previous Balance plus the Interest minus the Payment.
The final rows of the Excel spreadsheet are shown below. Note that the last payment is less than $50 because there is only $40.23 owed on the account.
From the table above, the last payment will be made in November 2014.
It will take 5 years and 11 months to pay off the initial $5000 charge.
The total amount of interest paid on the initial charge of $5000 is calculated by summing the interest column.
There will be $2177.41 paid in interest.
10% vs. Minimum Payment
What if you paid 10% of your balance (or $50, whichever is greatest) versus 5%?
How much money would you save?
How long would it take to pay off the initial $5000 charge?
The last few rows of the Excel spreadsheet are shown below.
You will pay off your charge in January 2012.
That is 2 years and 10 months sooner than making only the minimum payments.
You will pay $932.99 in interest.
That is a savings of $1244.42.
To see the full Microsoft Excel file, click here.