Spreadsheet for car purchase

There have been a few threads recently on fuel mileage as a basis for buying another car, including one that I started. I'm trying to put together a spreadsheet to look at things in dollars. I want to make sure I'm not missing anything. I don't think even the most carefully done spreadsheet will prove totally accurate over a long period, as variables of gas prices and/or maintenance can't be predicted. But at least you get a good ballpark idea. Anyone have one they care to share, or to at least list the factors they laid out? Here's what I have so far:

• Gas - based on mpg of car I have vs. car I want.

• Insurance - what I pay now vs cost of new car

• Car payment - no car payment now vs. what payment would be with new car

• Maintenance - this is a sort of variable number. I'm only calculating "unexpected" maintenance. All cars need oil changes, brakes, tires. So assuming they use similar tires and such, those costs wash out. I'm mainly looking at what would my current "beater" cost to keep running vs a new car (obviously a brand new car would be under warranty)

I figure I'd add 1-4 above and project it out over a 5 year period. Then consider what your residual value (assets) value is at the end of that, if you were to sell it.

I don't have the tenacity to record the data over time for my own cars, but I always try to look at the numbers when considering another vehicle purchase. A good starting place I've found for cost of ownership 'best estimates' for a wide range of cars is the Edmunds TCO. Their maintenance-cost estimate assumes that you're taking the car to a pro, not DIY, but looking over their other numbers could make putting your own spreadsheet together a lot quicker: http://www.edmunds.com/tco.html

Tidbits I've gleaned from running the numbers for my own spreadsheets: The ownership costs for Jaguars and (particularly) Land Rovers is simply staggering. Even used ones. Buyer beware, right?

Current value. This is what you could get for your car as it sits right now. Or, what you would pay for whatever car you are looking to purchase.
Depreciation - I just used a depreciation table I found online for my spreadsheet. Current year - car year = age. Lookup age in the table to get %Residual value, multiply by purchase price. Gas prices. check http://www.eia.gov/petroleum/gasdiesel/
Prices have increased on average $0.20/gallon/yr for the last 10 years. I used that as my ballpark number.

I will see if I can post mine when I get home from work.

FWIW if you're considering a Honda Fit and want some real-world data for your calculations, we've put 100kmi on our '09, done oil changes as prescribed by the Maintenance Minder(appx 8000mi), bought two sets of tires, flushed the trans fluid, and changed the air filter once.

Real-world MPG: Me = 37mpg, my wife = 31mpg.

Oh yeah, FWIW, I use Fuelly data to generate my best guess in regards to mileage. Every car I've had will end up just a tad above the average numbers seen there. Your numbers will vary, but its good to benchmark yourself vs. average there, assuming there are enough drivers of your current car.

The EPA numbers work ok if you are comparing new car to new car, but for comparing any older stuff, the change-of-methodology in '07 screws things up.

petegossett wrote: FWIW if you're considering a Honda Fit and want some real-world data for your calculations, we've put 100kmi on our '09, done oil changes as prescribed by the Maintenance Minder(appx 8000mi), bought two sets of tires, flushed the trans fluid, and changed the air filter once. Real-world MPG: Me = 37mpg, my wife = 31mpg.

My wifes' car is an '08 Fit. It's been dead nuts reliable for the 50k miles we've put on it. I'm actually considering a few cars, with the Mazda 2 at the top of the list.

I've looked at the Edmunds TCO stuff. It's a pretty good benchmark. As said, it assumes dealers do repair work. Also, when comparing to a car you own, you have to assume repair costs on the current car will be higher since my current car is 17 years old.

Put something in there to attempt to capture how much you like car. It's hard.

The Edmunts TCO is a nice concept, but how many fiscally conscious individuals limit their search to cars <5yrs old, and only keep said cars for 5 years?

tuna55 wrote: Put something in there to attempt to capture how much you like car. It's hard.

Impossible. Final decision will always come to this anyway. Important thing is to use real data to narrow down the cars and influence how much you like the car :)

i have a pretty good spreadsheet but it isn't set up to look forward. probably wouldn't be too hard to make it work that way, other than the uncertainty of parts and fuel prices in the coming years.

Another vote for Edmunds TCO as a good starting point. I use their numbers for an apples to apples comparison, knowing that they may vary somewhat from reality if you do your own maintenance and repairs, but if I use their numbers for every car I can at least compare between cars using the same variances. For my own use I discount repairs and maintenance by 50% to somewhat equalize/right-size things for me. For cars older than 5 years (the limit of what Edmunds keeps), I use the newest equivalent as a basis.

I have a spreadsheet I use to compare the cost of ownership of a car over a five year ownership period, and use the following columns:

• Car description (year, make, model)

• Engine

• Trans

• Drivetrain layout

• Value (using Edmunds), even though you can always find deals, I just use one source to be consistent

• Payment – I assume any car over $10k, the amount over $10k will be financed, under $10k I can come up with from selling my current car. I use a formula to figure out the cost of interest.

• 1st year fees and taxes – from Edmunds

• Annual insurance cost – from Edmunds, again reality will be different than their number, but try to keep it consistent

• Depreciation for 5 years – from Edmunds

• Annual fuel cost – using numbers from FuelEconomy.gov for mileage, assumptions about annual mileage, and using published averages for the cost of regular, premium, and diesel (depending on drivetrain).

• Maintenance – from Edmunds, I discount by 50%

• Repairs – from Edmunds, I discount by 50%

All the numbers plug into a formula to come up with an annual cost of ownership, first year cost, five year cost, value of the car in 5 years, and 5 year cost after figuring in the sale of the car when I’m done.

I only plug in the information for cars that I’m interested in, or dream of owning. By my formulas, the least expensive car I have plugged in is my current challenge build, a 1999 Mk3 Jetta at $12.4k if I were to use it as a daily driver for 5 years. The most expensive would be a 2007 Cadillac CTS-V at $35.6k.

Also, after plugging in numbers and looking at a bunch of different cars on the spreadsheet, fuel cost doesn’t make as much difference as I thought it would. Reliability (in the form of maintenance and repair costs) made much more of a difference than I thought, even after discounting Edmunds numbers by 50% to account doing most of the work at home and just paying for parts.

Yeah, the "how much do I like the car" factor is an intangible that needs to be considered. But for now, I'm trying to look at things as objectively as possible.

Instead of comparing one new car to another for TCO, I'm actually doing the spreadsheet of comparing keeping my old beater for 5 years vs. the costs of a new car. Obviously, not having a car payment with my Volvo is a huge plus. The hard part is factoring in repair costs. My Volvo obviously has no warranty, so that's all out of pocket. Like I mentioned before, I'm not factoring in brakes, tires, pads, etc...as any car needs those. I'm looking at things like seals, water pumps, gaskets, starters, etc...that may need to be replaced on my beater. On a new car, they shouldn't need to be replaced and/or would be under warranty.

From my math so far, the old beater initially looks cheaper from a sheer cost perspective. But the residual value makes things much more even. My current beater would be worth not much more than scrap value in 5 years. A new Mazda2 for example would probably be worth 7-8k in 5 years. That's assets I could use for the next car.

I've got a spreadsheet that figures out how many miles it will take to recover the cost of a new vehicle based on MPG alone.