Car Purchases: Decisions, Decisions (Part 2)

Goal 1: utilize technology to adapt and use mathematical formulas that include cell referencing to answer real-world questions and interpret results.
Goal 2: interpret key features in an Excel table to investigate real world data.
    Go to this lab on the class highlights page, download this Excel file and open condocarlabsols.xls.
    Buying versus Leasing a Car: Let's use our Excel table in another situation which will probably be more relevant to you in the next few years - buying a car and the income you need to do so!
  1. Write down the make, model, year and price of the car that you have chosen in the last lab from Kelly Blue Book fair purchase price.

  2. In North Carolina, you must pay a 3% "Highway Use Tax" whenever you buy a car. Add this to the price of your car to get your car's total price. How much is this? Show work.

  3. In B1 Click on B1 in your Excel sheet. You will see 105265 appear. Change the amount to match your car's total price and then hit return.
    In B2 Click on B2 and then click up top in the formula bar next to the = sign. Assume that you will put 10% down on your car (since this is typical in real life). Then you will need a loan for 90% of the car's total price. You must change the loan to 90% of the car cost instead of 80%, so do that and hit return.
    In D1 Click on D1 and then click up top next to the = sign. Change this to the rate for the car loan you found in #16 of last lab divided by 12 (i.e. =rate/12).
    In B3, by going to the formula bar, change the 360 (the number of house payments) to 72 (the number of car payments in 6 years (72=6*12)) so that the Excel loan payment formula reads
    In D3, by going to the formula bar, change 360 to 72, and change the text from total interest 30 years to total interest 6 years.
    Fill in the chart below. The top row should contain numbers, while the 2nd row should contain your Excel formulas.
    Total interest       
    6 years        

    Excel formula

  4. By-hand Verification Set up the loan payment formula with numbers and solve for the monthly loan payment by hand for the loan in B2. If you do not get the same answer, then you should recheck both Excel and your by-hand work to see what you did wrong and resolve any differences.

  5. Leasing for 3 years You also have the option of leasing your car for 3 years, and then giving the car back at the end of the 3 years. Monthly payments and upfront costs on a lease can be less expensive and allow you to drive a new car every few years. However, with leasing you are limited to a certain number of miles per year, or you must pay extra, and insurance costs can be higher. Most leases charge you as much as 25 cents per mile if you exceed the annual mileage limit -- usually between 12,000 and 15,000 miles, and it is very expensive to break a lease early. Would you rather purchase or lease your car? Why?

  6. By-hand Verification Fill in by-hand computational work (like 84212 x .0675/12 for the interest paid the first month, but use your car's numbers) for the first two months of the amortization table and compare your computations with Excel. Are there any rounding differences if you use dollars and cents?
    month # end of month payment interest paid that month principal paid that month loan balance

    1 already in #4

    2 already in #4
  7. Debt-to-Income Ratio Assume the bank allows a 35% debt-to-income ratio and assume that you have the following monthly debts:
    monthly rent = $425.00
    monthly student loan payment = $80.00
    monthly insurance = $40.00
    monthly credit card minimum payments = $50.00
    car payment= monthly car payment amount in above chart. What is your monthly debt?

    What is the monthly income necessary for a 35% debt-to-income ratio? Show work.

    Use this to figure out what your annual income should be. Show work.

  8. Paying Extra What if you can afford an extra $85 per month on the car payment? First adapt the Excel file. What cell did you add the 85 to?

    Next calculate the total interest over the life of the loan now. Careful: D3 will not give the correct answer since it assumes 72 months and it does not account for the negative balance the last month, so compute using your the last payment in the Excel file just like we did in the last lab #7-9. Show work.

    Compare your response to the total interest from #3 and address why the increase or savings makes sense.