Credit and Amortization Spreadsheet Activity

Math 131

Spring 04

Credit Cards and Amortization

In this activity, we will explore some of the workings of credit cards. We'll start working by hand, and then move to the computer.

First, an example: suppose Katia charges a $200 couch on a credit card which has an annual interest rate (APR) of 24%, and she doesn’t use her credit card again until she has paid off this bill, which she pays at $20 per month. Although most people don’t use credit cards quite this way, this model describes how most people pay off student loans, car payments, mortgages, etc. We will use what is called an amortization schedule or amortization table to describe the payment structure.

First, we need to determine how often the interest is compounded. Most credit cards are compounded monthly, which means that the annual interest rate is divided by 12 to determine a monthly interest rate. In the example where the APR is 24%, the monthly interest rate is 24/12 % or 2% (or 0.02). The credit card company adds the monthly interest (or finance charge) to the bill each month. Student loans, however, are compounded daily, which means the interest rate is divided by 365 (approximately) and then interest is applied every day, while payments are usually made monthly.

The amount of money in the payment that goes toward the original bill is called the principal.

Let us look at our specific example:

1) First we compute the monthly interest rate. This is the annual interest rate divided by 12. In our example: 24% ÷ 12 = 2% or .02.

2) Now we enter the initial balance, $200 into the first row of the table below. 3) We compute the interest for the first month by multiplying our monthly interest rate by the balance: $200 x .02 = $4, and enter this into the first row of the table.

4) Katia’s bill for the first month will be $204. Thus, of her $20 payment, $4 will go toward interest, and the rest $20 - $4 = $16 will go toward principal. From Katia's first $20 payment, the credit card company makes $4 for itself, and $16 goes toward the couch.

5) Now we compute the new balance. This is the amount Katia still owes on the couch. Her bill read $204 and she paid $20, so she now owes $204 - $20 = $184.

Now we repeat steps 3-5: 3) The new interest is $184 x.02 = $3.68.

4) The new bill is $184 + $3.68 = $187.68

The new principal is $20 - $3.68 = $16.32

5) The new balance is $187.68- $20 = $167.68

Now finish the table below. When the bill is less than $20, Katia will just pay what she owes, not the full $20.

Payment # Initial Balance Interest Principal

1 $200.00 $4.00 $16.00

2 $184.00 $3.68 $16.32

3 $167.68

What patterns do you notice in the Interest and Principal columns? How much total interest did Katia pay? How long did it take her to pay off the bill?

Excel 1:

Now move to Excel and set up a table yourself. You can add additional columns to make the table clearer. Other than the initial balance of $200, you probably should enter a formula, not a number, into the cells you use. How can you figure out the total interest Katia paid? When your table is set up, try modifying it. What if the couch cost $250 or $300? What she paid $10 per month instead of $20? Experiment with a few changes like these. Which is harder to change on your sheet, the initial balance or the payment?

Excel 2:

In the folder "Public Use" there should be another folder called "Math 149 Excel Stuff," and in there is an Excel worksheet called "Credit Card Worksheet." This is a fancier version of the table you just made (if you don’t have these files, ask for them on a disk). In this sheet, the only cells that you can type in are the cells marked, Initial Balance, Interest Rate, and Payment. Try entering the numbers we just used and see what happens. Notice that the computer automatically calculates the total number of payments, the total amount paid, and the interest paid.

Now click on various cells to see how this sheet is set up. Feel free to ask it you have any questions. Also feel free to take home a copy of the worksheet.

1) Try charging Katia's couch on your own credit card or on a credit card whose offer you’ve seen, paying it off at $20 a month. How much interest does she pay with each card. How long does it take to pay the total bill?

2) Now suppose Katia charged $1000 on three different credit cards with 12%, 18%, and 24% APRs. If she paid off her bill at $100 per month, would which card she used make a significant difference? How about at $50 per month? At $25 per month?

3) If the initial balance is $1000 with a 24% interest rate, fill in the table below with the number of payments and the interest paid, if the monthly payments are as listed.

Monthly Payment Number of Payments to Pay Bill Interest Paid

$100

$90

$80

$70

$60

$50

$40

$30

$20

$10

4) What patterns do you notice in the above table? What is going on with the last few numbers?

5) Now experiment with making the payment $20.05, $20.10, etc. until you see significant changes. Write them down.

6) Now change the interest rate to 12% and repeat exercise 3. Compare to exercise 3.

7) Now keep the payment fixed at $50 and change the interest rate to 13%, 14%, etc. Make a table of your results. What do you notice.

8) Suppose you have a $5,000 student loan at 9% interest. You want to pay the loan back in 5 years (that is 60 payments). Set the Initial Balance to $5000 and the Interest to 9% and experiment with the payment amount until the number of payments is close to 60. How much interest do you end up paying? How about a loan at 4% interest?

9) Repeat number 7 but assume you want to pay the loan back in 10 years (120 payments). Is your payment half of the payment in 7? Why or why not? How much interest do you end up paying in this situation?

To Commentary Doc File PDF File Home