The payment is not what the car costs
Ask a dealer or a loan calculator what a car costs and you get a monthly payment. That figure is principal and interest, or a lease payment built the same way. It is one line out of eight.
It leaves out fuel or charging, insurance, registration and taxes, servicing, tires, and what is usually the largest cost of owning a car: depreciation — the value it loses every day whether you drive it or not. Nobody invoices you for that one. You settle it in full, on the day you sell.
This workbook adds all eight lines together, divides by the months and the miles you actually put on the car, and does it for up to three cars at once on the same terms.
Depreciation is a curve, not a rate
Most car-cost spreadsheets apply one flat percentage a year. Real depreciation is front-loaded and depends far more on what kind of car it is than on how carefully you drive it.
The Resale Value tab carries a per-year retention curve for five vehicle classes. The five-year point of each curve is a measured figure from iSeeCars’ analysis of more than 950,000 five-year-old vehicles (opens in new tab), published March 2026 — cited, with its date, on the Sources tab. The shape between is a stated assumption, and every cell in the grid is an ordinary number you can type over.
That curve is also what makes a used car’s case computable rather than assumed. Enter a car bought at eight years old and the model works back to what it cost new, then carries it forward from there — so the depreciation the first owner already absorbed is never charged to you, and the new car is never let off it.
A lease, amortized as a lease
A lease payment has two halves and neither is quoted to you separately: the depreciation charge, which is the capitalized cost less the residual divided by the term, and the rent charge, which is the capitalized cost plus the residual multiplied by the money factor.
The workbook builds both, converts the money factor into the annual rate it actually is, and adds the parts a quoted payment leaves out — the acquisition fee, the disposition fee at turn-in, the capitalized cost reduction that buys you no equity at all, and the mileage overage, charged from the miles you really drive rather than the allowance you were offered.
Paying cash is priced honestly too. Money spent on a car stops working for you, so every path is charged for the capital actually tied up in it — which is what lets a lease, a loan and a cash purchase finally be compared as one number instead of three incomparable ones.
Cost per mile, and the two questions that test your verdict
Cost per mile is the only figure on which a car you drive 6,000 miles a year and one you drive 20,000 can be honestly compared. The workbook splits it into the part that gets cheaper the further you drive — depreciation with age, financing, insurance, registration — and the part that does not: fuel, servicing, tires.
That split is what makes the last two tabs possible. The Head to Head tab re-prices everything across ten annual mileages and fifteen holding lengths, names the cheapest car in every row, and reports the mileage and the year at which the ranking flips.
The worked example that ships with the file runs three cars over seven years at 12,000 miles a year:
| Candidate | Total cost of ownership | Cost per mile |
|---|---|---|
| New compact hybrid sedan, financed | $52,955 | $0.63 |
| Eight-year-old compact SUV, cash | $52,155 | $0.62 |
| New compact electric crossover, leased | $96,544 | $1.15 |
The SUV wins — but only below about 15,000 miles a year, and only if you keep it fewer than nine years. That is the kind of answer no flat-rate calculator can give you, and it is the reason your own annual mileage is the first number to get right. Those cars are invented and those figures are illustrative; the point is the shape of the answer, not the amounts.
You can check the numbers it runs on
Almost every default figure has a row on the Sources & Assumptions tab: what it is, the value used, whether it is a measured published figure, an assumption we made, or a convention the industry uses, the study or agency it came from, and the date it was checked. Where something is our judgment, it says so.
There is also a Checks tab that reports problems in plain words rather than spreadsheet errors — a lease shorter than your holding period, mileage above the allowance, an electric car set to the wrong resale curve, a missing insurance quote. You do not need to know Excel to know the file is set up correctly.
Own it, don’t rent it
Nearly every car-cost calculator online belongs to somebody who sells cars, loans or insurance. It stops at the payment, it wants your email, and the assumptions underneath the answer are neither shown to you nor editable.
This is the alternative: the whole model in a file you own outright, that shows its work and takes nothing from you. Use the .xlsx in Excel or LibreOffice Calc, or open the included one-click link for a native Google Sheets copy in your own Drive — no importing, nothing to rebuild. Change an assumption and watch the answer move. Keep it for the next car, and the one after that.
Want to try the idea first? The free True Monthly Cost of a Car Estimator gets you the past-the-payment number for one car in about a minute, with no email required.
A financial planning model, not financial advice. This workbook is not licensed financial, tax or legal advice, and it is not a valuation or an appraisal. Every figure that ships in the file is an illustrative example for cars that do not exist — replace them with real quotes for the real cars you are looking at. It is built on US figures and US finance and lease conventions.
