Credit Card Calculator

In this assignment you'll use Excel to do a simulation of how a credit card can work. This will be a near "worst-case" scenario. You will learn how high interest rates and low monthly minimum payments can lead many people into a trap.

(1) Begin a new Excel spreadsheet.

(2) Type the following data into the indicated cells.


Cell What to type Why you typed it.
Initial Balance
It's just a column heading
Interest Rate (%)
Minimum Payment (%)
Initial credit card balance of $7500
19.4 Yearly interest rate of 19.4%
Minimum monthly payment of 2% of balance.
Divided yearly interest rate into monthly interest rate.


(3) In cells A9 and A10 we're going to type our first two billing dates. The first billing date will be the 15th of next month and the second will be the 15th of the month after that.

Select cells A9 and A10 and fill down to row 500 or so.

(4) Then type these headings


Cell What to type Why you typed it.
B8 Month begin balance It's just a column heading
C8 Interest added
D8 Payment Due
E8 Month end balance

(5) Type these formulas

Cell What to type Why you typed it.
E9 =A2 Copies the value from cell A2 into cell E9
B10 =E9 Copies the value from cell E9 into cell B10
C10 =B10*($B$3%) Calculates amount of interest to add
D10 =B10*($C$2%) Calculates minimum monthly payment due.
E10 =B10+C10-D10 Calculates monthly ending balance.


At this point this is what you should see:

All of the numbers in cells B10, E10, D10 and E10 are the results of the formulas you typed in step 5.

(6) Fill cells B10 to E10 all the way down the column. Format all cells as currency as appropriate. Make sure dates in column A are formatted to show the year.

(7) Clean up and make it look pretty. Use colors to make sheet easier to read. Make sure all columns are appropriate widths.

(8) Save and TURN IN.