Excel DB Function: Declining Balance Depreciation
Returns the depreciation of an asset for one period, using the fixed-declining-balance method.
DB depreciates an asset faster at the start of its life than at the end, which is how most equipment actually loses value: a van is worth much less after its first year than after its fifth. Straight-line depreciation, which SLN calculates, spreads the loss evenly instead and is simpler but less realistic.
You give it what the asset cost, what it will be worth when you are done with it, how long that takes, and which period you want. It returns the depreciation for that one period, so a full schedule is one call per year with the period argument counting up.
The `month` argument handles assets bought part-way through a year. It says how many months of the first year the asset was actually owned, and it shifts the whole schedule accordingly — which is why a DB schedule usually runs to one more period than the asset's life in years.
Syntax
=DB(cost, salvage, life, period, [month])Arguments
costRequired- What the asset cost when new.
salvageRequired- What it will be worth at the end of its life. It cannot be zero — DB's rate calculation divides by it.
lifeRequired- How many periods the asset will be depreciated over, usually years.
periodRequired- Which period to calculate, counting from 1.
monthOptional- Months of ownership in the first year. Defaults to 12. Anything less shifts the schedule and adds a final partial period.
The example data
Headers in row 1, data in A2:E4. Rates are annual, terms in years.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Asset | Cost | Annual rate | Years | Salvage |
| 2 | Delivery van | 24000 | 0.059 | 5 | 4000 |
| 3 | Packing line | 85000 | 0.072 | 10 | 9000 |
| 4 | Forklift | 12500 | 0.045 | 3 | 2500 |
Worked examples
=DB(B2, E2, D2, 1)Result: 6739.20
First-year depreciation on the van. Over a quarter of its value goes in year one.
=DB(B2, E2, D2, 2)Result: 4846.31
Year two is noticeably smaller, because the rate applies to what is left rather than to the original cost.
=DB(B2, E2, D2, 5)Result: 1802.71
The final year of the van's life, by which point the annual charge has more than halved.
=SUM(DB(B2, E2, D2, 1), DB(B2, E2, D2, 2), DB(B2, E2, D2, 3), DB(B2, E2, D2, 4), DB(B2, E2, D2, 5))Result: 20000.00
The whole schedule sums to cost minus salvage, which is the check that it is right.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#NUM!
Why it happens: salvage is zero or negative, period is above life, or one of the arguments is negative.
How to fix it: DB cannot handle a salvage value of zero because its rate formula divides by it. Use DDB or SLN for assets written down to nothing.
#VALUE!
Why it happens: An argument is text.
How to fix it: All five arguments must be numeric.
The schedule does not sum to cost minus salvage
Why it happens: A period is missing, or the month argument was used and the schedule needs one extra period.
How to fix it: With month set to less than 12, run the schedule to life + 1 periods.
Depreciation looks too fast
Why it happens: That is the method. Declining balance is front-loaded by design.
How to fix it: Use SLN for an even spread, or DDB if you want to control the acceleration factor.
Tips worth knowing
- The whole schedule should sum to cost minus salvage — that is the one check worth doing.
- SLN is straight-line, DB is fixed-declining, DDB is double-declining with a factor you can set.
- DB rounds its internal rate to three decimal places, which is why its answers differ slightly from a hand calculation.
- Build the schedule with =DB($B$2, $E$2, $D$2, ROW()-1) and fill down, so the period increments itself.
Frequently asked questions
What is the difference between DB, DDB and SLN?
SLN spreads the loss evenly across every period. DB uses a fixed declining rate, so more is written off early. DDB declines faster still and lets you set the acceleration factor. DB is the middle option and the one most closely matching how equipment loses value.
Why does DB return #NUM! when salvage is zero?
Its internal rate is derived from the ratio of salvage to cost, and that calculation cannot divide by zero. For an asset depreciated to nothing, use DDB or SLN instead, both of which accept a salvage of zero.
How do I handle an asset bought mid-year?
Pass the number of months you owned it in the first year as the fifth argument: =DB(cost, salvage, life, 1, 7) for an asset bought in June. The schedule then needs one extra period at the end to account for the shift.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.