I recently went through the joys of finding and purchasing a used car for my daughter. I wasn’t looking for a specific make and model. I was looking for a low-mileage, reliable car at a price point.
So what did I do? I created a spreadsheet, which is the tool in the title. If you’re not into research and tracking, just stop reading now. I’ll likely bore you with the rest of this.
I started with the basics – year, make, model, miles, link to the ad, and list price. I then decided I needed a more objective measure of reliability, so I decided on Consumer Reports reliability measures. (Note: you have to join Consumer Reports to get these from them. I found it valuable and relatively inexpensive.)
As I narrowed down my search, I came across a few vehicles I thought might be expensive to insure, so I added insurance quotes to the spreadsheet. I can get quotes online from my insurance provider easily. I only did this for top candidates for purchase.
I created a total cost of ownership category based on the purchase price of the car and a 5-year insurance estimate. No, this doesn’t encompass true TCO, but for the purchase decision, it was good enough.
Finally, I added a disposition code and disposition field that maps to it. In other words, what did I do about that particular car? Did I reject it before seeing it based on mileage, reliability, or other factors? Did I decide it was one of the best and make an appointment to go see it? There are other dispositions in between, of course. The reason I chose disposition codes is ease of sorting and putting the best cars in the process at the top. I sorted by disposition code (A to Z) and then TCO (smallest to largest) to figure out what I wanted to go see next.
There are some other features. Given the mileage and the year, the spreadsheet will calculate if mileage is low, average, or high for the model year based on US averages. To be classified as low mileage it has to be less than 90% of the average mileage for that model year. To be classified as high mileage, it has to be more than 110% of the average mileage for that model year. It’s got some conditional formatting rules that I found made decisions easier.
I also threw in the search variables I was using on Craig’s List to make my searches consistent. Those got refined over time. As an example, I decided that no matter what the average mileage for a model year, I wasn’t going to put my money on a car with 200,000 miles or more. I also eliminated pickup trucks from the search, since my daughter expressed that she didn’t want a truck.
I mention all this because I want you to have it. I originally built it in Google Sheets so I could share it with other stakeholders in the buying process. You should be able to import the Excel version here into Google Sheets if you want without any trouble.
Buying a used car? Here's a useful spreadsheet for you to use, free! https://www.johncloonan.com/used-car-buying-spreadsheet/ Share on XHappy car buying!