Car Buying Spreadsheet Template

	A	B	C	D
1			Notes

2		This spreadsheet is opinionated. One assumption it makes is that you are only looking at cars that meet your needs and are in your price range, and that within that field, all else being equal, you should buy the car that costs you the least to drive on a per-mile basis. In other words, if you are looking at cars that cost between $8,000 and $15,000, you should be happy to buy a $14,800 car if it has proportionally more life left in it than a $8,200 car. The table also assumes that the car is being driven until the end of its natural life. If that is not the case – that is, if you plan to resell or trade in the car – then the amount you are actually paying to own the car over the period you own it can’t yet be known. If these assumptions aren’t true of your situation, feel free to modify the spreadsheet to make it work for you.
3		Sorting the table by Total Cost per Remaining Mile (ascending) will put the best deal, given these assumptions, at the top.
4
5	Lifetime miles	200000		Until what mileage will the car last?
6	Average gas cost	$2.50		per gallon
7	Annual miles	15000		They say 12,000 - 15,000 is normal
8	Tax rate	6%
9	Total Fees	$650.00		Dealer fees, title, registration, etc. You can modify individual cells in the “Purchase Price with Taxes and Fees” column if you know a dealer's particular breakdown
10	Lifetime maintenance cost	$5000.00		To simplify, I assume I’m spending the same amount on maintenance no matter how old the car is or how much mileage it has – that is, I'm assuming the maintenance cost is entirely backloaded
11	Loan Term in Months	60
12	Interest Rate Modifier	1.112		Figure out what multiplier estimates the total amount you’ll pay on the life of the loan, based on your interest rate. If you’re not financing, or will have a 0% APR for the entire term of the loan, change this to 1
13
14	Other notes	This spreadsheet is meant to compare essentially equivalent cars. Don’t compare models with vastly different maintenance costs, especially – the spreadsheet assume the same lifetime maintenance cost for every car.
15		The "Individual Car Breakdown" sheet isn't linked to the "Car List" sheet – but you can look at it or plug the numbers in to see a little more easily how the underlying total cost evaluation is being made.
16		Desired Trim has no impact on the numbers. If you’d like you could code in a lookup to add or subtract value based on the trim. I just indicated No/Yes/Yes+ for my own subjective judgment.
17		Year is highlighted orange if it’s 7 or more years old.
18		Best Deal Equivalent treats the car with the lowest total per-mile cost as a baseline for what the best deal is. It subtracts the gas and maintenance per-mile costs to arrive at what the per-mile financed purchase cost would be if the deal were as good as the best one. Then it multiplies that by the remaining expected mileage to arrive at the total purchase price you’d be paying if it were as good a deal as the best one. “Difference”, then, is the gap between the purchase price you'd ideally be paying and the actual total estimated purchase price. Best Deal Equivalent Asking Price is the asking price before fees and taxes and whatnot that would result in an equivalent per-mile total purchase cost as the best deal in the list.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100