Ask what a car costs and you’ll be told its price. But the price is just the ticket in the window — the real number is what the car takes from you per month of your life, and the two are so loosely related that the cheaper-to-buy car is routinely the dearer one to own. Depreciation, insurance, fuel, tax, servicing, the MOT, the finance — motoring is a subscription wearing a price tag.
Excel is the right tool for exactly this shape of problem: several flows of money, different rhythms, one honest answer. The model is one sheet, ten inputs, three formulas — and once built, it prices any car decision for the rest of your life.
The idea: everything becomes £/month
The trick that makes wildly different costs comparable is forcing them all through the same unit. Some costs are monthly already (finance, charging). Some are annual (insurance, tax, servicing — divide by 12). And the big ones only show up when you sell — which is where most gut-feel goes wrong.
Depreciation is the headline act. What the car costs you in capital is not its price — it’s the price minus what you’ll get back, spread over the years you keep it:
=(price - resale_value) / years / 12
A £26,000 car sold for £13,000 after four years cost £271 a month in depreciation alone — silently, with no direct debit to remind you. A £9,000 car that’s worth £5,500 three years later cost £97. That gap outweighs almost any difference at the pump, and it’s the number the window ticket is designed to keep you from computing.
The model
One column of named inputs — price, resale_value, years,
miles_per_year, p_per_mile (fuel or electricity),
insurance_yr, tax_yr, service_yr, finance_mo, plus a
contingency_yr for the repairs that always come (a few hundred
pounds honest; zero is a lie you tell yourself). Then:
depreciation_mo = (price - resale_value) / years / 12
running_mo = miles_per_year * p_per_mile / 100 / 12
fixed_mo = (insurance_yr + tax_yr + service_yr + contingency_yr) / 12
true_cost_mo = depreciation_mo + running_mo + fixed_mo + finance_mo
Lay two or three candidate cars side by side as columns — same rows, one column per car — and the comparison stops being vibes.
Making the inputs honest
The model is arithmetic; the value is in the research, which takes
one evening. Resale value — look up 3–4-year-old versions of
the exact car on the used market right now; that is your answer,
no crystal ball needed. Insurance — run a real quote, not last
year’s number. Pence per mile — from your real consumption:
a 40 mpg diesel at 150p/litre is ~17p/mile; an EV charged at home
at 7p/kWh is ~2p, at public rapids ~15p — which is why where you
charge matters more than what you drive in an EV comparison.
And check the servicing schedule for the lumpy items (cambelt,
battery health check) — contingency_yr is where those live.
Then let the model earn its keep with what-ifs, because every input is a named cell: keep the car six years instead of four (depreciation per month falls — the strongest argument for buying at three years old and holding); double the mileage (the £/mile cars reshuffle); drop to one car (the number that funds a lot of taxis and train tickets).
The same three formulas, everywhere
This model is the budget’s missing line item — transport is most households’ second or third biggest cost, usually entered as “fuel” and undercounted by half. The monthly figure feeds your crisis floor honestly, and if the what-ifs free up £150 a month, the pension countdown just moved closer — the leverage of a cheaper life, compounding again.
As with every money model here: this prices the decision, it doesn’t finance it — finance agreements and their small print deserve professional eyes.
The car will still be a want as much as a maths problem — that’s allowed. But want it with the stack in view, not just the ticket. £536 a month is a different question than “£26,000?”, and it’s the real one.