Spreadsheets Excercise #2

Comparing Car Loans

In this exercise you will decide on the car you want to purchase and then determine which financing option is best. Step one is to find the car. Here are several local dealers. Feel free to find another option but do NOT spend more than a few minutes looking for the car. Once you have the car and the price use this information to create a spreadsheet like the one shown below. Enter the information for your car.

Mike Harvey Honda | Carlsen Subaru | Toyota 101 | Towne Ford | Putman Chrysler Jeep Dodge

Now you will need to find financing for your car. Here are several credit unions and the car rates they offer. Again feel free to find another site with better rates but don't spend more than a few minutes searching. You will be comparing three different rates with three different terms.

San Mateo Credit Union | Redwood Credit Union | Nasa Federal Credit Union | Bank of America

Comparison of Car Loan Options
 

3.9%
36 Months

4.9%
48 Months

5.9%
60 Months
Price of Car      
Down Payment      
Loan Amount      
Interest Rate      
Term (in months)      
Monthly Payment      
Total Cost      
Interest Paid      
Percent of Total Cost
 paid as Interest		      
Conclusion:
The best loan for me would be ….. 

Explanation of terms:
Down Payment - this is the amount of cash you must provide when purchasing the car. It is typically 5%, 10%, 15% or 20% of the price of the car. For a $25,000 vehicle 10% down would be $2,500.
Loan Amount - the price of the car - the down payment ($25,000 - $2,500 = $22,500 as the loan amount - the amount you will finance)
Interest Rate - when a bank lists the interest rate for a loan it is an annual interest rate but you will be making monthly payments not just one annual payment so when entering this value into a formula be sure to divide it by 12 (the number of payments you make in a year)
Term - how long is the loan for? Typical terms are 36, 48, or 60 months.
Monthly Payment - this is just what it says but in Excel you will use the pmt function to determine this amount. The pmt function requires the following values:
Rate - this is your annual interest rate divded by 12
Nper - number of payments - same as the term in months
Pv - present value - this is how much money you will be borrowing. Since you are borrowing this amount it must be negative.
The last two values for the formula can be left blank.

Directions:

  1. Create a spreadsheet similar to the one above.
  2. Calculate the loan amount (the principal) based on the car you chose and your down payment (choose 5%, 10%, 15% or 20% ).
  3. Using the Paste Function button, select the Pmt Function.
  4. Enter the rate: Rate = percentage rate/12
  5. Enter the term in months: Nper = months
  6. Enter the amount to finance as a negative amount: PV = - loan amount.
  7. Calculate the total cost (payment * number of months).
  8. Calculate the interest amount (total cost - principal).
  9. Calculate the percentage of total cost paid as interest (interest/total cost).
  10. Which rate and term is the best for you? Explain your answer and include in it a comparison of the rates and associated costs. Use your specific examples in your answer. This answer should be one paragraph.
  11. Show your spreadsheet to your instructor for teacher check.
`