In: Accounting
P2. Car Shopping
You have just gone car shopping for a new SUV. You are trying to play one dealer off another. You have the following two deals to consider. Dealer 1 is offering you the car at a $6,000 discount off of the list price of $30,000. He is offering you a car loan with no money down and making annual payments over five years at 12 percent interest. You went to the bank and found that 12 percent annual interest is what they would have charged you too. Dealer 2, on the other hand did not budge on price, but he had an attractive financing offer of 3.6% over 4 years on the same $30,000 vehicle.
Part 1.
Which dealer should he buy from (assume one payment per year)? Show your analysis in an Excel spreadsheet. Print that sheet and your writeup should be one or two paragraphs explaining your decision and showing me that you understand the numbers.
Part 2.
Car loans actually require monthly payments, not annual payments. Assume the same facts except that the payments are monthly. Does this change your decision? Explain why or why not briefly. Normally, I would ask for supporting calculations. You don’t need to attach your detailed amortization schedule for the monthly loan. Provide the pertinent information only that you used to make your decision (keep it brief).
You can type (or write legibly) your rationale for your loan selection(s) below. (and attach annual payment spreadsheet results on the next page)
Part 1:
The loan amount in case of dealer 1 is 30,000 – 6,000 = $24,000. We can use the “PMT” function here and the variables are Nper = 5 and rate = 12%. Excel’s PMT function gives a value of an annual payment of $6657.83
For dealer 2 there is no discount and so loan amount = 30,000. Nper = 4 and rate = 3.6%. Annual payment = 8186.93 (again using the PMT function).
Dealer 1 | Dealer 2 | ||||||||||||
Year | Principal due at the start of the year | Annual Payment | Principal paid | Interest paid | Principal due at the end of the year | Year | Principal due at the start of the year | Annual Payment | Principal paid | Interest paid | Principal due at the end of the year | ||
1 | 24,000.00 | 6,657.83 | 3,777.83 | 2,880.00 | 20,222.17 | 1 | 30,000.00 | 8,186.93 | 7,106.93 | 1,080.00 | 22,893.07 | ||
2 | 20,222.17 | 6,657.83 | 4,231.17 | 2,426.66 | 15,990.99 | 2 | 22,893.07 | 8,186.93 | 7,362.78 | 824.15 | 15,530.29 | ||
3 | 15,990.99 | 6,657.83 | 4,738.91 | 1,918.92 | 11,252.08 | 3 | 15,530.29 | 8,186.93 | 7,627.84 | 559.09 | 7,902.44 | ||
4 | 11,252.08 | 6,657.83 | 5,307.58 | 1,350.25 | 5,944.49 | 4 | 7,902.44 | 8,186.93 | 7,902.44 | 284.49 | 0.00 | ||
5 | 5,944.49 | 6,657.83 | 5,944.49 | 713.34 | 0.00 | ||||||||
Total | 2,747.73 | ||||||||||||
Total | 9,289.17 |
As we can see that in case of purchase from dealer 2 the total interest amount = $2747.73 and that from dealer 1 = 9289.17. Difference = 9289.17 - 2747.73 = 6541.44
As 6541.44>6000 (the discount amount) the car should be purchased from dealer 2.
Part 2: Here for dealer 1 nper = 5*12 = 60 and rate = 12%/12. In case of Dealer 2, nper = 4*12 = 48 and rate = 3.6%/12
Dealer 1 | |||||
Month | Principal due at the start of the month | Monthly payment | Principal paid | Interest paid | Principal due at the end of the month |
1.00 | 24,000.00 | 533.87 | 293.87 | 240.00 | 23,706.13 |
2.00 | 23,706.13 | 533.87 | 296.81 | 237.06 | 23,409.33 |
3.00 | 23,409.33 | 533.87 | 299.77 | 234.09 | 23,109.55 |
4.00 | 23,109.55 | 533.87 | 302.77 | 231.10 | 22,806.78 |
46.00 | 7,402.09 | 533.87 | 459.85 | 74.02 | 6,942.24 |
47.00 | 6,942.24 | 533.87 | 464.44 | 69.42 | 6,477.80 |
48.00 | 6,477.80 | 533.87 | 469.09 | 64.78 | 6,008.71 |
49.00 | 6,008.71 | 533.87 | 473.78 | 60.09 | 5,534.93 |
50.00 | 5,534.93 | 533.87 | 478.52 | 55.35 | 5,056.41 |
51.00 | 5,056.41 | 533.87 | 483.30 | 50.56 | 4,573.11 |
52.00 | 4,573.11 | 533.87 | 488.14 | 45.73 | 4,084.98 |
53.00 | 4,084.98 | 533.87 | 493.02 | 40.85 | 3,591.96 |
54.00 | 3,591.96 | 533.87 | 497.95 | 35.92 | 3,094.01 |
55.00 | 3,094.01 | 533.87 | 502.93 | 30.94 | 2,591.09 |
56.00 | 2,591.09 | 533.87 | 507.96 | 25.91 | 2,083.13 |
57.00 | 2,083.13 | 533.87 | 513.04 | 20.83 | 1,570.09 |
58.00 | 1,570.09 | 533.87 | 518.17 | 15.70 | 1,051.93 |
59.00 | 1,051.93 | 533.87 | 523.35 | 10.52 | 528.58 |
60.00 | 528.58 | 533.87 | 528.58 | 5.29 | 0.00 |
Total | 8,032.00 |
Dealer 2 | |||||
Month | Principal due at the start of the month | Monthly payment | Principal paid | Interest paid | Principal due at the end of the month |
1 | 30,000.00 | 672.02 | 582.02 | 90.00 | 29,417.98 |
2 | 29,417.98 | 672.02 | 583.76 | 88.25 | 28,834.22 |
3 | 28,834.22 | 672.02 | 585.51 | 86.50 | 28,248.71 |
4 | 28,248.71 | 672.02 | 587.27 | 84.75 | 27,661.44 |
46 | 2,004.01 | 672.02 | 666.00 | 6.01 | 1,338.01 |
47 | 1,338.01 | 672.02 | 668.00 | 4.01 | 670.01 |
48 | 670.01 | 672.02 | 670.01 | 2.01 | 0.00 |
Total | 2,256.72 |
Differential for interest rates = 8032-2256.72 = 5775.28
As 5775.28<6000 (discount amount) the car should be purchased from dealer 1.
Thus for part 1 purchased should be made from dealer 2 and in case of part 2 purchase should be made from dealer 1.