The Excel DB Function

Related Function:
DDB Function
Depreciation

When calculating the depreciation of an asset, several different calculations can be used.

One of the most popular methods of calculating depreciation is the Fixed Declining-Balance Method, in which, for each period of an asset's useful lifetime, the calculated value is reduced by a fixed percentage of the asset's value at the start of the period.

The Excel DB Function uses the Fixed Declining Balance Method, using the following constant percentage reduction per period (rounded to 3 decimal places):

Fixed Declining Balance Rate Equation

where,

  • salvage is the final value of the asset at the end of its lifetime;
  • cost is the initial cost of the asset;
  • life is the number of periods over which the depreciation occurs.

Basic Description

The Excel DB function calculates the depreciation of an asset, using the Fixed Declining Balance Method, for each period of the asset's lifetime.

The syntax of the function is:

DB( cost, salvage, life, period, [month] )

where the arguments are:

cost-The initial cost of the asset.
salvage-The value of the asset at the end of the depreciation.
life-The number of periods over which the asset is to be depreciated.
period-The period number for which we want to calculate the depreciation.
[month]-

An optional integer argument that specifies how many months of the year are used in the calculation of the first period of depreciation.

The number of months in the last period of depreciation is then calculated as 12 - [month].

If the [month] argument is omitted, it takes the default value of 12.


Excel DB Function Examples

Example 1

In the spreadsheet below, the DB function is used to find the yearly depreciation of an asset that costs $10,000 at the start of year 1, and has a salvage value of $1,000 after 5 years.

 Formulas:
 A
1Depreciation
2=DB( 10000, 1000, 5, 1 )
3=DB( 10000, 1000, 5, 2 )
4=DB( 10000, 1000, 5, 3 )
5=DB( 10000, 1000, 5, 4 )
6=DB( 10000, 1000, 5, 5 )
 Results:
 AB
1DepreciationAsset Value at End of Period
23690.00- Asset value at end of Yr 1 is $10,000 - $3,690 = $6,310
32328.39- Asset value at end of Yr 2 is $6,310 - $2,328.39 = $3,981.61
41469.21- Asset value at end of Yr 3 is $3,981.61 - $1,469.21 = $2,512.40
5927.07- Asset value at end of Yr 4 is $2,512.40 - $927.07 = $1,585.32
6584.98- Asset value at end of Yr 5 is $1,585.32 - $584.98 = $1,000.34

Example 2

In the example below, the DB function is used with the same cost, salvage and life argument values as in Example 1 above. However, in the following example, the depreciation calculation starts 6 months into year 1.

 Formulas:
 A
1Depreciation
2=DB( 10000, 1000, 5, 1, 6 )
3=DB( 10000, 1000, 5, 2, 6 )
4=DB( 10000, 1000, 5, 3, 6 )
5=DB( 10000, 1000, 5, 4, 6 )
6=DB( 10000, 1000, 5, 5, 6 )
7=DB( 10000, 1000, 5, 6, 6 )
 Results:
 AB
1DepreciationAsset Value at End of Period
21845.00- Asset value at end of Yr 1 is $10,000 - $1,845 = $8,155
33009.20- Asset value at end of Yr 2 is $8,155 - $3009.20 = $5,145.81
41898.80- Asset value at end of Yr 3 is $5,145.81 - $1,898.80 = $3,247.00
51198.14- Asset value at end of Yr 4 is $3,247.00 - $1,198.14 = $2,048.86
6756.03- Asset value at end of Yr 5 is $2,048.86 - $756.03 = $1,292.83
7238.53- Asset value 6 mths into Yr 6 is $1,292.83 - $238.53 = $1,054.30

Note that, in the above example, the value at the end of year 1 has only depreciated over 6 months and the value at the end of year 2 has depreciated over 18 months, etc.


Further examples of the Excel DB function are provided on the Microsoft Office website.


Excel DB Function Errors

If you get an error from the Excel DB Function, this is likely to be one of the following:

Common Errors
#NUM!-

Occurs if either:

  • The supplied cost or the supplied salvage argument is < 0;
  • The supplied life or the supplied period argument is ≤ 0;
  • The supplied [month] argument is ≤ 0 or is > 12;
  • The supplied period > life (and the [month] argument is omitted);
  • The supplied period > life+1.
#VALUE!-Occurs if any of the supplied arguments are non-numeric.