Depreciation in Excel: SLN, DB, DDB, SYD, and the One That Actually Reaches Salvage
Excel has five depreciation functions and one of them quietly leaves $510 of an $85,000 machine undepreciated. Here is the same asset through all of them, side by side, plus which one matches the tax method you probably need.
Five Functions, One Asset
Excel ships SLN, SYD, DB, DDB, and VDB. They all spread the same cost across the same life, and they disagree about year one by more than a factor of two. Choosing between them is an accounting decision, not a formula preference.
One of them has a trap worth knowing before you use it: DDB does not necessarily depreciate the asset down to its salvage value, and it does not warn you.
Try the depreciation calculatorGenerate a schedule under any of the standard methods without building the sheet first.The asset
Swipe sideways to compare columns.
| Name | Value |
|---|---|
| Cost | $85,000 |
| Salvage | $8,000 |
| Life | 8 years |
| Depreciable base | $77,000 |
What each function does
Swipe sideways to compare columns.
| Function | Syntax | Pattern |
|---|---|---|
| SLN | =SLN(cost, salvage, life) | Equal every year |
| SYD | =SYD(cost, salvage, life, per) | Accelerated, smooth decline |
| DB | =DB(cost, salvage, life, per, [month]) | Fixed-rate declining balance |
| DDB | =DDB(cost, salvage, life, per, [factor]) | Double declining balance |
| VDB | =VDB(cost, salvage, life, start, end, [factor], [no_switch]) | Declining, switching to straight line |
SLN takes no period argument because every year is the same. The other four do, and the period is a whole year unless you are using DB with a month argument or VDB with fractional start and end values.
The same asset, four ways
Swipe sideways to compare columns.
| Year | SLN | SYD | DDB | VDB with switch |
|---|---|---|---|---|
| 1 | $9,625 | $17,111 | $21,250 | $21,250 |
| 2 | $9,625 | $14,972 | $15,938 | $15,938 |
| 3 | $9,625 | $12,833 | $11,953 | $11,953 |
| 4 | $9,625 | $10,694 | $8,965 | $8,965 |
| 5 | $9,625 | $8,556 | $6,724 | $6,724 |
| 6 | $9,625 | $6,417 | $5,043 | $5,043 |
| 7 | $9,625 | $4,278 | $3,782 | $3,782 |
| 8 | $9,625 | $2,139 | $2,837 | $3,346 |
| Total | $77,000 | $77,000 | $76,490 | $77,000 |
VDB fixes this. Its last argument, no_switch, defaults to FALSE, meaning it switches to straight line on the remaining book value whenever straight line would give a larger deduction. On this asset that happens only in year eight: $3,346 rather than $2,837, and the schedule lands exactly on $8,000.
DB, and why its answer differs from DDB
DB does not use a factor. It derives its own rate from the ratio of salvage to cost, then rounds that rate to three decimal places.
Choosing a method
Swipe sideways to compare columns.
| Method | Use when | Effect |
|---|---|---|
| SLN | Book reporting, simple assets, buildings | Smooth, predictable, easy to explain |
| SYD | The asset genuinely loses value fastest early | Accelerated without the salvage problem |
| DDB | Modelling a rapid write-down, matching a policy | Fastest early deduction, may miss salvage |
| DB | Matching a fixed-percentage policy | Derives its own rate, close to salvage |
| VDB | Anything mirroring a tax declining-balance system | Accelerated and lands exactly on salvage |
For most modelling work the honest default is VDB with the switch enabled, because it is the only accelerated method that both front-loads the deduction and reaches the salvage value exactly. Use SLN when the audience needs a number they can verify in their head.
The full schedule
Swipe sideways to compare columns.
| Column | Header | Formula in row 2 |
|---|---|---|
| A | Year | 1 |
| B | Opening book value | =Cost |
| C | Depreciation | =VDB(Cost, Salvage, Life, A2-1, A2) |
| D | Accumulated | =C2 |
| E | Closing book value | =B2-C2 |
Row 3 changes three cells: opening book value becomes =E2, and accumulated becomes =D2+C3. Copy down for the life of the asset. The final closing book value must equal the salvage value, and if it does not, you are using DDB.
Partial years
VDB is the only one of the five that handles fractional periods natively, because it takes a start and an end rather than a period number. An asset placed in service on 1 October has three months in its first fiscal year: =VDB(Cost, Salvage, Life, 0, 0.25).
For the second year, the range runs from 0.25 to 1.25, and so on. This does mean the schedule has one more row than the asset has years of life, which is correct rather than an error.
What these functions do not do
- None of them is a tax calculation. Tax depreciation follows statutory class lives, conventions, and elections that differ by jurisdiction and by year, and no Excel function encodes any of that.
- They do not implement the half-year or mid-quarter conventions that many tax systems require in the first and last year. You can approximate those with VDB and fractional periods, but the classification decisions are yours.
- They ignore immediate expensing elections, bonus depreciation, and any cap on deductions, all of which usually matter more than the method.
- They assume salvage value is known. In practice it is an estimate, and revising it partway through the life requires recomputing the remaining schedule rather than adjusting a formula.
- They say nothing about impairment. An asset that loses value suddenly needs a write-down, which no depreciation schedule anticipates.
- Book and tax depreciation commonly differ on purpose, and a model usually needs both schedules with the difference tracked between them.
Why does my DDB schedule not reach the salvage value?
Because declining balance applies a fixed percentage to a shrinking base, which approaches zero without arriving. Excel clamps DDB so it never goes below salvage, but nothing pushes it up to salvage. Use VDB with the switch enabled if the schedule must land exactly.
What is the difference between DB and DDB?
DDB uses a factor you supply, defaulting to 2, applied as factor divided by life. DB derives its own rate from the salvage-to-cost ratio and rounds it to three decimals. DDB is a policy choice; DB is fitted to the salvage value you specified.
How do I model a half-year convention?
Use VDB with fractional periods. The first year runs from 0 to 0.5, the second from 0.5 to 1.5, and so on, with a final half period at the end. The schedule will have one more row than the stated life, which is expected under that convention.
Which method gives the biggest first-year deduction?
DDB, at $21,250 on this asset, against $17,111 for SYD and $9,625 for straight line. VDB with a factor of 2 matches DDB in the early years and only diverges once straight line on the remaining balance becomes larger.
Can I depreciate to zero salvage value?
Yes for SLN, SYD, and VDB. DDB and DB both misbehave with a zero salvage: DDB never reaches zero, and DB computes a rate of 1 minus zero to a power, which produces a 100% first-year rate. Use VDB if the asset genuinely has no residual value.
Do these functions work in Google Sheets?
All five exist with the same names and arguments. The only practical difference to watch is that named ranges behave slightly differently across sheets, so check the references if you move a workbook between the two.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team