Why does Excel's DDB formula never depreciate the whole amount?

[brandi7862]

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.

 

[Fred Smith]

That's what declining balance does. When you're always reducing something
by, say 20%, you will never get to zero. It's got nothing to do with Excel,
it's the mathmatics of declining balance depreciation (or geometric
progressions, if you want to generalize).

Regards,
Fred

 

[joeu2004]

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.

 

[joeu2004]

PS....

However, you can compute a rate that will depreciate the cost to (nearly)
zero in the desired lilfetime.
[....]
=DDB(cost, 1, life-1, n, -rate(life-1, 0, -cost, 1)*(life-1))

I should emphasize that I was presenting a mathematical solution, not
necessary an accounting one.

In point of fact, that does not follow accepted accounting practice.
See http://en.wikipedia.org/wiki/Depreciation , especially the section
entitled "Declining-balance/Reducing balance depreciation".

 

[Tyro]

Conceptually, depreciate 100 by 10%, you get 90, depreciate that by 10% and
you get 81, depreciate that by 10% and you get 72.9 and depreciate that by
10% and you get 65.61, etc. It never gets to zero.

Tyro