Cars lose value fast and unevenly. Model declining-balance depreciation — each year’s value is the prior year times a retention rate — to project resale value at any age.
The example
$35k car, 15%/yr, 5 years.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | 35000 × 0.85^5 | — |
| 3 | Value | → ~$15,533 |
The formula
The formula:
How it works
How it works:
- Each year the car keeps a fraction of its value:
1 - annual_rate. - After N years, value =
price × (1 - rate)^years— compounding decline. - Total depreciation = price − that value.
- Cars often drop fastest in year one; a higher first-year rate models that better.
First-year drop is steeper. New cars can lose 20%+ the moment they leave the lot, then settle to ~15%/year. Model year one separately, or use the steeper average if buying new — it’s the main reason a lightly-used car is often the value buy: someone else absorbed the first-year cliff.
Try it: interactive demo
Price, annual depreciation rate, years.
Variations
Total depreciation
Price less value:
Straight-line (compare)
Even per year:
Annual rate from values
Back into the rate:
Pitfalls & errors
Rate as decimal. 15% is 0.15 in the power.
Year-one cliff. New cars drop faster early — a flat rate understates it.
Model varies. Make, mileage, and condition swing real resale.
Practice workbook
Frequently asked questions
How do I model vehicle depreciation in Excel?
How do I find total depreciation?
How do I back into the depreciation rate?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class