Math 326, Day 2 - Car Payments

Search automotive company and dealer websites for a car you would like to buy. Take note of the price and interest rate for financing (if the interest rate is 0% or no interest rate is given, use 7%). Make a model which shows the balance of the loan after each month, assuming monthly payments of $300, downpayment of $3000 and interest compounded monthly (r/12% interest is added each month, where r is the interest rate).
  • You should have fixed cells referencing Price, Interest Rate, Monthly Payment and Down Payment so that these can be easily updated.
  • You should have columns for Months and Loan Balance.
  • Add more columns and fixed reference cells as you see fit, to help with programming and user-friendliness.
    •  Once your loan payment model is working, use Goal Seek to find out which monthly payment would give a 4-year payment plan, and which payment would give a 6-year payment plan.
    Additional Questions
  • Compute the total interest payed. Compare the interest costs for the 4-year plan versus the 6-year plan. Try different downpayments as well.
  • With the payment size for the 4-year plan, suppose you save up and make an extra payment after 1 year of approximately 1/5 of the original price (for example, you make a lump sum payment of $4000 where the car originally cost $18,278).  Your monthly payments stay the same afterwards. When does your payment plan end now? How much interest do you save? Assume there is no penalty for making early or extra payments. In the last month you just pay off the remaining balance instead of making the whole monthly payment.
  • Check the interest rate for a 3-year CD at your bank. Is it better to put that money from the previous question into your loan or a CD? Note that "APY" is how much interest is paid each year, while the regular rate given is usually then compounded continuously.
  • The dealer offers you a special anti-rust undercoating for $500. How much will this increase you monthly payment on a 4-year plan? How much will it actually cost you, including interest?
  • You currently own an SUV in good condition which gets 20 MPG. You are thinking of switching to a Toyota Prius (48 MPG), to save money on gas. The dealer tells you that since hybrids are in high demand (and SUVs in low demand), they won't give you any trade in value for your SUV. The Prius would cost $28,000, with a $3000 down payment, and 3.9% interest on a 4-year plan. Compare the cost of driving your SUV to the cost of driving the Prius plus the Prius's monthly payments. (a) How many miles would you need to drive per month for the Prius to save you money over 4 years? (b) Driving 1500 miles per month (quite a lot), how long would it be before the Prius was cheaper? (c) Driving 1500 miles per month, what price of gas would make the Prius cheaper over 10 years? For (a) and (b), assume the price of gas is $2/gallon. For (a)-(c), assume the maintenance/insurance/etc costs are the same.