Depreciation function inconsistencies

[Conrad Carlberg]

After several years away from it I've recently dusted off DDB and looked at
it more closely. There is a serious inconsistency between the results it
returns, and both the formula given in the Help docs and the generally
accepted definition of "double declining balance" in the accounting
literature.

The syntax is:

=DDB(Cost, Salvage, Life, Period, Factor)

The only optional argument is Factor, which defaults to 2 (hence, "double
declining balance").

The following arguments are used in the examples below:

Cost: 11,000
Salvage: 1,000
Life: 5
Factor: 2 (the default)

In accounting literature the term "double declining balance" is generally
taken to mean that the DDB depreciation for the first accounting period
after the asset is put into service is twice that of straight line
depreciation for the same period (whether the IRS likes it or not, they
don't yet mandate argument defaults in Excel).

So, we'd expect this:

=SLN(11000, 1000, 5)

to return 2000, as indeed it does: (11000 - 1000) / 5 = 2000. The
depreciation is the same during each period of the asset's life, hence
"straight line."

And we would expect DDB to return a first period value of 4000 (twice the
SLN value), given the same arguments. But it does not. This:

=DDB(Cost,Salvage,Life,1)

returns 4400, not 4000, as the depreciation for the first period.

Now, the Help documentation gives this formula for DDB (I know, superfluous
parentheses, but let it go):

DDB = ((Cost-Salvage) - Total Depreciation from Prior Periods) * (Factor /
Life)

There is no depreciation prior to the first period, so in that case the
formula simplifies to:

DDB = (Cost - Salvage) * (Factor / Life)

or, using the values given above:

DDB = (11000 - 1000) * (2 / 5)

or 4000: just what the textbooks would lead us to expect, twice the SLN for
the first period, and definitely not the same as DDB(Cost,Salvage,Life,1).

But notice that if we ignore the salvage value in the formula, we get:

DDB = 11000 * (2 / 5)

or 4400, just what the DDB() function returns. And if you extend the
equation through the first four periods via:

DDB = (Cost - Total Depreciation from Prior Periods) * (Factor / Life)

still ignoring the salvage value, you get exactly the same figures as are
returned by the function for the first four periods.

In sum, it appears that the DDB function calculates depreciation neither
according to the formula supplied by the documentation, nr according to the
definition provided in the literature. And it flies in the face of common
sense: the Cost value is not reduced by the Salvage value, as it should be,
to keep Salvage from depreciating. By leaving Salvage in the Cost, DDB is
depreciating Salvage (in this example, by $400 during the first period).

I find precious little in the ngs concerning Excel's depreciation functions,
and nothing at all regarding this inconsistency. Someone did say something
like, "We get few questions about depreciation around here."

Does anyone have comments or clarification?

 

[Philip J Smith]

Hi Conrad.

This is an old post, I picked it up cos I searched on DDB.

You are right the formula shown in the help notes on this function is
incorrect both theoretically and as implemented in the function.

The formula for DDB, in any period except the last,
should be (Cost - Depreciation provided in Prior Periods) x Factor / Asset
Life

The Salvage value is only relevant in the final period when the period
depreciation is adjusted so that the Cost - Depreciation provided in all
period = Salvage.

Since mine has been the only response I would guess that no one at Microsoft
is interested in correcting the errors in their notes.

As an aside, I never use the built in functions for these calculations as
they do not consider mid year aquisitions and disposals.

Regards
Phil Smith