Wednesday, May 1, 2013

Car Costs Spreadsheet

Reader AnatoliN asked me to share the spreadsheet I used to work out the fixed and variable costs on my car. So, I cleaned it up, and with great fanfare, here is the car costs spreadsheet.

Remember that garbage in leads to garbage out. Unless you have an accurate list of your car expenses, this spreadsheet cannot magically give accurate answers. In my experience, most people know they paid too much for their cars and have a hard time admitting the real costs to others and to themselves.

If you want to protect your ego from the brutal truth of how ridiculously expensive cars are, I suggest using the advertised price of $23,999 that got you into the dealership showroom rather than the actual $33,000 you paid after various add-ons and taxes. You can also just estimate your gas costs instead of looking up your credit card bill to see that you actually pay much more. Leaving out the cost of new tires or brakes might help as well.

OK, enough sarcasm. You get the point. If you don’t include all costs, then the final answer will be wrong. For those who try it out, please let me know if you find the spreadsheet useful.


  1. Thank you, Michael. Following your lead and prior to your spreadsheet I have done my quick (10 lines) estimate of driving costs and arrived at $0.49 per km of total cost, 2/3 of which came from allocation of annual depreciation, condo parking, insurance onto 20K km annual drive. My car provides me with average liter/100 km and I bought new tires recently; I estimated the rest.

    Took me under 10 minutes to be self-shocked, I never done such brutal calculations previously.

    This result is actually well in line with what companies commonly charge to bring a specialist to client site.

    1. @AnatoliN: The real shocker for me was to take the per km cost and see what it meant for various trips. For you, a return trip between Montreal and Toronto would be over $500. Seems incredible to me.

  2. Thanks for sharing your Excel file.

    The line variable cost (187) should be line 182 (total) - line 186 (fixed cost).

    Right now, the formula is line 187 = line 186 (fixed), which does not make sense.

    The calculation was giving me a small number... found this small error.

    My cost for a 2006 Elantra bought in 2010 used for 6k was used for 60 000 km from Feb 2010 to May 2013, insured one way.

    Total cost of all lines is $22,916 from Feb 2010 to May 2013.

    My fixed cost per year is $1017.41, estimated it can give another 3 years at least (20k per year), if not more.

    My variable cost is $0.22 per km ($0.11 in fuel and the rest in depreciation/maintenance/insurance).

    I did not take into account all the money I got from my ex-employer for using the car (around $2 400 over 2 years).

    1. @Anonymous: Thanks for pointing out the error. A curious thing happened when I tried to fix it. I clicked on the cell to be edited, then typed an equals sign, clicked on the appropriate cell on line 182, then a minus sign, and then on the cell in line 186. Everything was fine until I did the final click on the line 186 cell. For some reason everything was replaced by just the reference to line 186. I repeated these actions twice more to make sure it wasn't just finger trouble. Seems like some sort of strange software bug. I had to type out the references to fix these cells properly.

      It sounds like your Elantra is quite cheap to operate. However, even 22 cents/km will add up surprisingly quickly.