brandi7862 said:
Why does Excel's DDB formula never depreciate the whole
amount when the salvage value = 0? It always leaves a few
hundred dollars left to depreciate
-- I've tried tons of examples and they all do this.
There are two issues to consider.
The first issue is that declining-balance depreciation is determined by
multiplying the remaining value (less salvage value) by a depreciation rate
-- some percentage less than 100%. Thus, the remaining value after
depreciation is the remaining value (less salvage value) times 1 minus the
rate (1-r).
Mathematically, such a formula will never reach zero. But in practical
terms, the depreciation must be rounded at least to the penny. So in fact,
eventually you can indeed depreciate to zero. But a mathematical formula
cannot compute that.
I will return to that in a moment.
The second issue is that by default, the Excel DDB() function arbitrarily
chooses a depreciation rate of 2/life. So, for example, if the lifetime is
10 years, the depreciation rate is 2/10 or 20%. There is no assurance that
applying that rate will depreciate the original cost to zero (or close to
zero) within the stated lifetime.
(You might have specified a different factor. But it sounds like it was
incorrect for the outcome that you want.)
However, you can compute a rate that will depreciate the cost to (nearly)
zero in the desired lilfetime.
Suppose your asset cost $10,000, and you want to depreciate it to zero after
10 years.
In practical terms, let's say that means you want the remaining value to be
$1 after 9 years. So, you can compute the depreciation rate with the
following formula:
=-rate(9, 0, -10000, 1)
Note the use of minus signs so that we get a positive percentage rate.
Also note that the last argument to the DDB() function is a "factor" such
that factor/life is the deprecation rate. So, the "factor" argument must be
computed by rate*life.
In summary, in general, DDB() can be used for life-1 periods as follows:
=DDB(cost, 1, life-1, n, -rate(life-1, 0, -cost, 1)*(life-1))
for periods "n" equal to 1 through life-1.
For my example:
=DDB(10000, 1, 9, n, -rate(9, 0, -10000, 1)*9)
for periods "n" equal to 1 through 9.
The depreciation for the last period should be $1, or approximately $1
depending on if and how you round results each period.
HTH.