Project #9 : Auto Loan Strategy

A spreadsheet is as a excellent tool to answer financial questions.

• How much interest will I pay on this loan?
• How much will changing the time of the loan effect the payment?
• How will changing the interest rate change the payment required?
• How will a down payment effect the payments required?

This project explores the use of spreadsheets in answering financial questions about a car loan.
(A skill that you may need soon .)

Introduction

As motivation for this project, I will share what happened to my daughter when she went to buy a truck. She had already checked out the loan possibilities at a credit union and came armed to the car dealer with the payments of $ 200.00 per month needed for the loan at the credit union. She negotiated a good price for the truck. But when the dealer had the papers for her to sign, the payment was $ 300.00 per month!!!!!! How could this be true? She questioned the dealer only to find that without asking he had added many features to the loan including term life insurance. Soon she had the payments down to the proper level of $200.00 per month. I need to add that she took her dad to the dealer for support. Mr. Barrett

Project Background

• You are planing to purchase a car that costs $18,000.00.

• Since you have been out of school a few years, you have $9,546.00 in a savings account that yields 5 % interest. You could make a $8000.00 down payment.

• The interest rate on the loan will be 8% per year or an interest of 8%/12 each month.

• The Interest per month = principle [amount still owed]*Interest rate as a decimal divided by 12 months

• The first month's interest for the full amount would be
  $18000*0.08/12 = $120.00
  Any amount paid above this amount will be deducted from the principle.

• Use these relations above to calculate a loan payment schedule.
  New Principle = Old Principle - Interest
  ( So the more you pay the faster the loan decreases and than the less interest you have to pay.)

• Sometimes a car manufacture gives you a low interest rate to get you to buy his/her car. A careful buyer finds that the best way to get a low price is to pay for the car, and negotiates a low buying price.

Project Details

This project is to create a spreadsheet to calculate answers to each of the following questions :

1. If you borrow the full price of the car ($18,000 at 8% for three years), what is the payment and what is the total interest charges you will pay?

2. If you use $8,000.00 of your savings as a down payment then you need to borrow $10,000 ($18,000 - $8,000 )at 8% for 3 years, what is the payment and what is the total interest you will pay on the loan?
3. To find out if using $8,000 of your savings is a good idea, you need to find the amount of money you would have received if you keep your savings in the bank at fixed interest rate. [$ 8,000.00 of 5% for three years]
4. Using this information on from the savings account and the calculation in parts 1 and 2, is it better to use your savings and borrow less OR keep your money in savings and borrow more on the car?
   Explain your answers using the information on costs from the spreadsheets.
5. Find the amount of interest you will save, if you borrow the money for one year less [i.e shorten the borrowing period]. [$ 10,000.00 at 8 % for 2 years]
6. Find the amount you will save if you can get a reduced interest rate of 7%. [$ 10,000.00 at 7 % for 3 years]
7. Find the amount of extra interest you must pay if you borrow the money at the interest rate of 9% for three years? [$10,000 at 9% for 3 years ]
8. In your report, write your general conclusions about spreadsheets and loans.

Constructing the Spreadsheet

To answer the questions above you will need to set up a spreadsheet that can be modified to enter different principles to be borrowed, different interest rates, and different time periods for the loans.

• The spreadsheet that is created will be an active, if you change the principle, interest rate, or time period, all of the other numbers on the spreadsheet change. (the interest rate used through out the sheet is the number stored in one location [E3], decimal form of the interest in [E4], and the principle in [E2].)

• Set up a spread sheet like one below:

  

  • The principle at cell [E2]
  • The interest rate at [E3]
  • The decimal form of the interest rate at [E4]
  • The Columns of the table
    • Months - [A6]
    • Payment - [B6]
    • Interest - [C6]
    • Deduct from Principle - [D6]
    • Loan Amount Remaining - [E6]

  • The formulas below go into the rows of the table.
    • The first row A7..E7
      • The Loan Principle in [E7] should reference the principle in [E2]
      • The zeroth period will be zero in [A7]
    • The Second Row A8..E8
      • The first period is in [A8] +A7+1
      • The payment [B8] is 159
      • The interest in [C8] +E7*$E$4/12
      • The amount to reduce the principle [D8] +B8-C8
      • The new principle is [E8] +E7-D8
    • The Third Row A9..E9
      • The Third row is the same as the Second row except the payment column [B9] is +B8.
    • The third row is now to be copied down to row 43 - A43..E43
    • The Residue box [G7] should contain +E43 to get the value of the remaining principle at 36 months.

• Now with this spread sheet changing the payment in one cell will change the most of the cells.

  With the numbers given at the top of the sheet, you can check that your numbers through the whole sheet match the numbers in the example. Be careful not to make mistakes in the middle of the sheet - check your numbers through all the rows in the spreadsheet.

Using the Spreadsheet

1. First, use the spreadsheet to determine the payment and Interest paid for a loan of $18,000 at 8% for 3 years.
   • First change the principle to $18,000.00 with an interest rate of 8%.
   • From the spreadsheet , observe that you do not repay the loan at the end of the 3 years. Why? Note that cell E43 and its duplicate G7.
     At the end of 3 years there is still principle to repay.
   • IIncrease your payment in cell B8 until cell G7 goes within one dollar of zero.
     This is the proper payment for the three year loan.
     [I know this is a trial and error process but it is pedagogically valuable (good for learning).]

   • Note the sums of rows [B43] to [E43].
     The total interest paid is found in cell [C43].

   • Answer the following questions using the spreadsheet.

     1. What is the monthly payment required by this loan? ________________________

     2. What is the total amount of interest I will pay on the loan? __________________

     3. Print out the spreadsheet and the questions answers.

2. Next, use the spreadsheet to determine the payment and interest paid for a loan of $10,000 at 8% for 3 year.
   • Change the principle to $10,000.00 with an interest rate of 8%.
   • From the spreadsheet, observe that you overpay the loan at the end of the 3 years.
     (See cell [E43] and its duplicate [G7]).

   • Adjust the payment cell [B8] until you just repay the loan.
   • Answer the following questions using the spreadsheet.

     1. What is the monthly payment required by this loan? ________________________

     2. What is the total amount of interest I will pay on the loan? _________________

     3. How much less do you pay in interest charges with this loan? _______________________

     4. Print out the spreadsheet and the question answers.

3. Now find out how much money you would earn from interest payments if you invested $8,000.00 at 5 % for three years in a savings account.
   
   • You may need to create another spreadsheet that calculates the amount of interest earned in each month and increases the principle.

   • The figure below shows a spreadsheet for another savings account.

   • Test your spreadsheet with these numbers to check your calculations.

   • Now change cells [D2] and [D3] to reflect the proper amounts, $8,000 and 5%.

   • Answer the following questions using the spreadsheet.

     

     1. How much do you earn on your savings account?_________________
     2. Print out the spreadsheet and the answer to the questions.

     3. How much did you save by reducing the loan above?_________________
        Remember you are comparing the cost of the $18,000 loan to the cost of the $10,000 loan and the interest lost from the savings account.
     4. Is it a wise idea to keep your money in savings or to reduce the amount of your loan?_______________
        Discuss.
4. Change the spread sheet in 1. Above for $ 18,000.00 at 8 % interest for two [2] years..

1. How much money do you save if you borrow the money for one year less? ________________

2. Print print out the results.

5. Change the spread sheet in 1. Above for $ 18,000.00 at 7 % interest and 3 years.

1. How much money do you save if you obtain the lower interest rate? ________________

2. Print print out the results.

6. End your professional looking report with a half page or more discussion about the uses of spread sheets.
   • You do not have to import your spreadsheets into your word processing document but you must print out at least four of these "answer spreadsheets" and reference them in your report.

   • This symbol means print out the spread sheet.

        Return to CSC 171 Home Page

This page is Copyright 1998 by J. Barry DeRoos and Robert Barrett. All Rights Reserved.
This page may be distributed and used freely, provided that the copyright notices remain intact.
Last Modified: Nov 8, 2001.