Use Excel to Find the Best Used Car Deals

Although used cars have become more expensive relative to new models in recent years, they are still a good buy. Consumers can save substantial money by letting the previous owner pay for the depreciation hit a new car takes in the first years of its life. Compared to the new car market, however, used car buyers have less information at their fingertips. New models all have the same year and mileage, while a used car can have any combination year and mileage.

Both of these factors contribute to depreciation and make it very difficult to price-compare cars. Should you buy a 2011 model with 10,000 miles, a 2005 with 50,000 or a 2007 with 15,000 on the odometer? This guide shows you how to use Excel to find the “sweet spot” in the used car market for any given model, where depreciation and mileage come together to give you the best deal.

excel scatter plot

You’ll learn how to quickly scrape data from hundreds of used car listings on the Web, plot it in Excel and discover which model years are the best deal for a given mileage count. Instead of comparing between a handful of models, you’ll be able to see the whole market on your computer screen. This strategy also reveals which cars are the most overpriced and exposes information about how dealers price cars. This project will take an hour or two to complete, but save you thousands of dollars on a used car purchase.

After spending hours browsing through Mini Cooper S listings, I decided there must a better way to find the best used car deal. Most people only compare used car listings directly with other used listings, not the market as a whole. If they took time to compare several hundred models, however, they would find that some model years are priced significantly less than other model years with comparable mileage. This makes sense if a model has been updated with new features, but doesn’t account for the whole difference. Since the Mini Cooper S has only been updated once (a modest redesign in 2007), I wondered if there was any cost advantage to buying an older model year with the same miles as a newer one. I not only found the answer to this, but made discoveries about used car prices you can use to get the best deal.

Tools

You need the following things to find the best deal on your next used car.

Microsoft Excel (any version). A working knowledge of this program is helpful but not necessary.
OutWit Hub Light extension for Mozilla Firefox. This is what you’ll use to scrape data from used car listings on Cars.com.
Download my Excel 2011 file with the final results for the Mini Cooper S. I reference it throughout the guide and it will be helpful if you get lost.

Step 1: Obtain the Data

Navigate to Cars.com and perform a used car search for the make and model you want to buy. The more results the better. I looked for Mini Cooper S models at any price within 500 miles of 75353 (Dallas, Texas). I used the filter tool on the left side of the results page to include only hatchback models sold by dealers with prices. I did not narrow down to a single transmission or engine type because this would give me too few results. The goal is to get as many results as possible (several hundred) while being as specific as possible.

Doesn’t including cars from different cities with specific feature packages, different transmissions and warranties as I have done introduce error into the analysis? You bet. That is why you need data for several hundred cars. Such a large sample size reduces the effects of these differences and makes it possible to find trends in the market. With a large sample size, you can analyze the entire market for a particular vehicle. Don’t continue unless you can find at least 75-100 dealership listings for the same exact model. I made sure I wasn’t comparing Mini Cooper Base and convertible models to the Cooper S. Don’t look at both Honda Civic and Honda Civic Si models in the same analysis, for example.

Step 2: Scrape the Data

Make sure results per page at Cars.com is set to the default 50. This is important because the free version of OutWit limits us to 50 results per scrape. Sort the cars by year (oldest to newest) and open OutWit. Click the “scrapers” tab in the left pane. You’ll see the HTML source of the search results page in the top half of the window. Create three entries in the table below: Year, Price and Mileage. After filling in the “Marker Before” and “Marker After” fields, OutWit will be able to extract this data from each listing and prepare tables we can throw into Excel. Neat, huh? Use the following markers:

OutWit Hub firefox extension

Year Marker Before: “modelYearSort”>
Year Marker After: </span>

Mileage Marker Before: “milesSort”>
Mileage Marker After: mi.

Price Marker Before: “priceSort”>
Price Marker After: </span>

Click “Execute” to scrape the first 50 listings. They will appear as a nice little table in the “scraped” window. Right-click the data and choose “Select All.” Then right-click again and press “Export Selection as…” and pick “Excel…” from the list of options. Name the file “Car Listings Page 1″ or whatever else you want.

OutWit Hub firefox

Click the “page” tab in the top-left corner of the OutWit window to return to Cars.com. Go to Page 2 of the results and repeat the above process again. Go to “scrapers,” click “Execute” and export the data from the “scraped” page. You should have at least four or five Excel files when you are finished. I put all of mine in a single folder for ease of access later.

Step 3: Put it All Together

Open a new workbook in Excel. This will be the master file from which you generate a scatter plot and perform the analysis. Copy and paste all the data from the other files into the workbook. Note that for whatever reason OutWit exports the data as text. Excel should prompt you to convert it to numbers when you copy and paste each set of data over. Delete any junk like the first result in my table.

You should now have a list of several hundred cars with separate columns for year, price and mileage. If you ordered the Cars.com results by mileage, you should see the lowest-mileage cars first in the workbook. Now you can easily scroll through the data and make sure the age decreases as you move downward. If it doesn’t then you likely copied over a set of cars twice on accident.

Important: Make sure the Mileage column is second and Price is third. This is important because when you generate the scatter diagram in the next step, mileage (the independent variable) must be on the horizontal (x) axis and price (the dependent variable) must be on the vertical (y) axis. Mileage is an independent variable because in our analysis, we assume it changes the dependent variable we are studying (price).

Step 4: Generate the Scatter Plot

Note: Steps may vary depending on your version of Excel. I’m using Excel 2011 for Mac.

a) Starting with the earliest model year, highlight the mileage and price columns for that year. Don’t highlight the year. Generate a scatter plot for this data. Right-click on the data markers and choose “Select Data…”

excel dialogue box

b) The data you just plotted is one of several series you must add. The default name for the series is Series1. Change it to the model year you must graphed. In my file, it’s “2002.” Repeat this step and the one above for the other the model years.

scatter plot

c.) Click “Add” to add a data series for each model year (2003, 2004, etc.). The x-values should be the mileage for that year while the y-values should be the price. Click “OK” when you are finished. You should now see several hundred markers on the scatter plot in different shapes– a plot of all the cars showing their prices relative to mileage. Each shape represents a different model year. Look to the right for a legend.

d.) Create a trend line covering every data point to make the data easier to understand. Unfortunately, this isn’t possible with data spread across multiple series in Excel. So you will need to create one last series containing all the data. Right-click again, choose “Select Data…” and create a series called “ALL.” This should contain the mileage and price for every car on the spreadsheet. Click “OK” to add the data.

e.) You’ll now notice that your plot is covered with a single marker, the series “ALL.” Right-click one of the points and select “Format Marker…” You should see a “No marker” option somewhere in the settings. Click it.

f.) Insert a linear trend line from the charts menu. Excel will ask you which data series to use. Choose the “ALL” series. Display the equation and coefficient of determination, R², under “Trend line options” for extra geek points.

trend line

You should now see a pretty trend line covering your immaculate data plot. Drag the scatter diagram to make it as big as possible. Because you chose such a large sample size, the plot is a fairly good representation of the entire market for that car model. That’s right, you have the entire market right in front of you. This is something your dealer doesn’t have. Now let’s analyze the results.

Step 5: Findings

The linear trend line you drew shows the average of each price for a given mileage. In other words, it shows the average model price for the whole market. Cars below the trend line are priced lower the rest of the market. Cars above the trend line are priced higher than the rest of the market. The further a car is away from the trend line, the more or less expensive it is relative to all other cars.

If you examine the plot in the Mini Cooper S file closely, you’ll notice that most 2009-2011 models are far above the line. The 2006-2008 Minis are on both sides of the line, but 2005 and older models are almost exclusively below the line. Focus on the lower-left portion of the plot and you can see a number of low-mileage Minis for great prices– these are the cars you want to look at. They are under-valued relative to the rest of the market simply because of their model years. Remember that there have been very few changes to the Mini Cooper S since 2002.

excel scatter plot

Observe that a 2004 Cooper S (look for a green triangle) with 49,519 miles retails for $13,145. A 2007 Mini Cooper S with around 50,000 miles has a price of just under $21,000. You could argue that perhaps the 2007 has a plethora of extra options missing on the other car, or that maybe the 2004 is a lemon. The 2007 and later models are second-generation designs, but is the slightly updated design worth an extra $7,000? That’s up to you to decide. The design change shouldn’t account for more than a few thousand dollars in price, I think. Regardless of the updated models, you’ll notice the trend of older cars selling for less than newer cars with the same mileage repeats itself over and over again, meaning that individual vehicle characteristics aren’t the main cause (with the exception of a design change in come cases, which doesn’t account for the entire price difference).

Why would someone pay nearly $7,000 more for almost the same car with the same mileage? My guess is a substantial number of consumers prefer newer model years regardless of mileage. A smart used car shopper looks at mileage first and model year second. I’m no Mini expert, but I wasn’t able to find any information indicating major issues with pre-2007 Minis. It’s also true that the last year of a design are the most reliable and the newest are the least. Therefore many used car buyers should consider a 2006 more reliable than a 2007, which saw both interior and mechanical updates.

I now know that I should look for 2006 Mini Cooper S cars and older. Those cars sit either close to the trend line or far below. To get the best value, I should pick a car as far down from the line as possible. These are the cars that have absorbed most of their depreciation and will lose the least amount of money if I resell them later on. If I see a Mini I like at a local dealer, I can also add it to the data set and see how its price compares to the rest of the market. Maybe a 2007 or newer would be nice for its updated engine, but only if I can find one below the line.

Does the plot for your car show model years that consistently appear below the trend line? If so, there could be a good reason why (defects, a design change in later years, etc.). Otherwise you are better off shopping for that year. Since depreciation is a factor of both age and mileage, it makes sense that older models cost less than similar new ones with the same mileage. Buying a car far above the line means paying significantly more for depreciation in the long-run. You can take advantage of this and save thousands of dollars by only buying model years that are consistently below the line.

Note that the newest models in your analysis most likely have warranties. You need to determine on your own if this is worth the price premium or not.

Step 6: Extra Credit

If you remember anything from statistics, you’ll know that the closer the coefficient of determination (R²) is to 1.0, the better the relationship between two variables (mileage and price in this case). The R² in the Mini Cooper S file is roughly 0.63. This means there is a strong correlation between mileage and price, but not a perfect relationship. In a perfect world these Mini Cooper models would be priced exactly according to mileage and R² would be very close to 1.0. The fact that the coefficient is 0.63 helps justify my assumption that consumers prefer newer models regardless of mileage, but another force is also at play here.

scatter plot Excel

Look at the Mini Cooper S plot carefully. Notice how the car prices tend to be arranged horizontally regardless of mileage? This is true even for Mini Coopers with the same model year. The price in the very middle is right around $17,000. The arrow below it is $16,000 and above it is $17,000. Used car dealers are hoping you won’t pay attention to mileage when they set prices. They’re aiming to sell you a high-mile car for a low-mile price and choose prices that appeal to buyers psychologically, but aren’t necessarily fair. In the very middle arrow, there is nearly a difference of 40,000 miles between the first two 2006 cars (orange circles) and the last one. Yet both cars have a sticker price of just under $17,000.

This is another reason why there isn’t a perfect relationship between mileage and price. As a used car buyer, you should pay careful attention to this. Because shoppers generally have fewer models to compare with, they only see a very tiny window of the market. Thus a consumer isn’t always going to know when he or she is paying a 40,000-mile price for an 80,000-mile car. Add the fact that many consumers focus on year and you’ll see why some used cars are very poor deals.

Conclusion

Using this strategy, you can obtain more information about the market than your dealer has and put yourself on a level footing. Use it to determine which model years are the best deal for a given number of miles. Educating yourself about the market as a whole instead of just the cars you see on the lot is the smartest thing you can do.

As I’ve demonstrated, a few hours at the computer will let you outsmart the dealer and save several thousand dollars on your next used car. It’s just a matter of finding out which model year has the most age depreciation relative to mileage and taking advantage of this information. Additionally, plotting a prospective purchase on the graph lets you compare its price relative to the rest of the market.

Couldn’t you just use the Kelley Blue Book to figure out if you’re getting a good deal? Yes, but the KBB won’t help you find the “sweet spot” in the used car market like this method does. Performing this analysis gives you a visualization of car prices right now, whereas the KBB is only updated every few months.