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.

 


Return