Making a worksheet for a Credit Card Account
Investigate the finance charges for a consumer loan 1. The Problem Your credit card company charges 18% interest compounded monthly. Your balance on Jan. 1, 2002 is $2000. You make regular monthly payments of $400 until the loan is paid off. When is the loan paid off? How much is the total finance charge on the loan? We will use this as a starting point and investigate how the total finance charges are influenced by the size of the interest, the monthly payment and the initial balance. 2. Assumptions and Definitions A. We call the original debt the “Principal” of the loan. B. We make fixed monthly payments, with the exception of the last payment, which might be less, to pay the last outstanding balance. C. We compute a monthly interest, which is one twelfth of the yearly nominal interest rate. The interest is added every month on the outstanding balance after the payment is subtracted. We also refer to this as “interest being compounded monthly”. Most credit cards actually compute daily interest. D. We make each payment right after we receive the monthly balance statement at the beginning of each month and we assume that the payment is credited instantly. (Web banking) Most credit cards set a required minimum payment but the customer can pay more if possible. Car loans and mortgages are usually based on fixed monthly payments for a desired length of the loan, like 36 months or 30 years. E. The “Finance Charge” of a loan is the difference between the sum of all the payments until the loan is paid off and the principal. This does not take inflation into account. Inflation reduces the finance charge. We neglect inflation. 3. Example by Hand Assume the principal is only $600 with other conditions as in the stated problem. After you make the $400 payment the remaining debt is $200. The interest on this debt for one month is (.18/12)$200= $3. The balance on Feb. 1, is $203. Since you are able to pay at least $400, you pay the full remaining balance. So the loan is paid off in February and the total finance charge is $3. You made total payments of $400 + $203 = $603, and your principal was $600. What is the finance charge if principal was $800? 4. Find the Formulas Use the blank Lab worksheet (Page 1) and find the formulas. Make sure that you enter the payment in cell D3 and the nominal interest rate into cell D4. We will later change these numbers, and therefore we want to control them from this single input cell. Whenever you want to refer to the payment in a formula you write $D$3, and when you want to refer to the interest rate you write $D$4.
5. Make the Worksheet. Enter your formulas into a new Excel worksheet. Use Autofill to help you. Most cells can be filled down. Make sure that you prevent adjusting through Autofill of those cell references, which you want to remain fixed. When you are done compare your results with the sample solutions on page 2. Then change the initial balance to $3000, the monthly payment to $150 and the nominal interest rate to 17%. Compare your results to the solutions on page 3. 6. Find the solution to the original problem Using the lab page 2 determine when the loan of $2000 is paid off and the size of the total finance charge. Remember that the last column contains the cumulative finance charge up to that point in time. When is the loan paid off? What is the finance charge? 7. Use the worksheet to find the charges for other loans Compute the finance charges for loans with the various terms as given in the chart on the next page. Change the numbers in your worksheet and then read off the results from it. Remember that the finance charge can only be computed when the loan is paid off! Not before. So make sure that you wait until the loan is paid off. You may have to fill down the worksheet for quite a few rows until you get to that time.
Dr. Ahlbrandt Credit Card Instructions Page 2
Beginning Balance $5000 $5000 $5000 $5000 $5000 $5000 $5000 $5000
Interest rate 20% 20% 20% 20% 10% 10% 10% 10%
Monthly Payment $400 $200 $100 $70 $400 $200 $100 $70
Finance Charge
8. Write a report answering the following questions: a. What are the finance charges for the different loan terms? (These are the entries in the table.) b. If the monthly payment doubles does the finance charge get cut in half? You should look at the different examples given, and check where the monthly payment doubles. c. If the interest rate doubles does the finance charge double? Make sure you check where the interest rate doubles and compare those cases. d. What conclusion do you draw from your computations? What advice would you give a consumer who has an outstanding loan of $5000? e. What is the best strategy from the point of view of a credit card company in setting the minimal monthly payment on a credit card loan
Dr. Ahlbrandt Credit Card Instructions Page 3
Solution Beginning Balance $5000 $5000 $5000 $5000 $5000 $5000 $5000 $5000 Interest rate 20% 20% 20% 20% 10% 10% 10% 10% Monthly Payment $400 $200 $100 $70 $400 $200 $100 $70 Finance Charge $549.54 Feb. 10 $1,380.99 Aug. 11 $5,363.48 Aug. 17 Charge is ∞ never paid off $255.91 Feb. 10 $577.81 Apr. 11 $1,424.02 May 14 $2,527.22 Dec. 17
Dr. Ahlbrandt Credit Card Instructions Page 4