The Excel VDB Function

Related Function:
DDB Function
Variable Declining Balance Depreciation

When calculating the depreciation of an asset, it is common to use an accelerated depreciation calculation, in which the calculated value of an asset is reduced by a larger amount during the first period of its lifetime, and smaller amounts during subsequent periods.

One of the most popular accelerated depreciation methods is the Double Declining-Balance Method, in which the straight-line depreciation rate is doubled. A useful example of this is provided on the Wikipedia depreciation page

The Excel VDB function allows you to specify a factor to multiply the straight line depreciation by, although the function uses the Double Declining-Balance Method by default.

Basic Description

The Excel VDB function calculates the depreciation of an asset, using the Double Declining Balance Method, or another specified depreciation rate, for a specified period (including partial periods).

The format of the function is:

VDB( cost, salvage, life, start_period, end_period, [factor], [no_switch] )

where the arguments are as shown in the table below:

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.
start_period-The starting period for which you want to calculate the depreciation.
end_period-The ending period for which you want to calculate the depreciation.
[factor]-

An optional argument that is used to specify the rate of depreciation.

If the [factor] argument is omitted from the function it takes on the default value of 2 (specifying the double declining depreciation method)

[no_switch]-

An optional logical argument that specifies whether the method should switch to straight line depreciation when depreciation is greater than the declining balance calculation. Possible values are:

TRUE-Do NOT switch to the straight-line depreciation method;
FALSE-DO switch to the straight-line depreciation method when depreciation is greater than the declining balance calculation.
If omitted, the [no_switch] argument takes the default value FALSE.

VDB Function Example 1

 Formulas:
 AB
1Depreciation 
2=VDB( 10000, 1000, 5, 0, 1 )- Depreciation during Yr 1
3=VDB( 10000, 1000, 5, 1, 3 )- Depreciation during Yrs 2 & 3
4=VDB( 10000, 1000, 5, 3, 5 )- Depreciation during Yrs 4 & 5
 Results:
 AB
1Depreciation 
2$4,000.00- Depreciation during Yr 1
3$3,840.00- Depreciation during Yrs 2 & 3
4$1,160.00- Depreciation during Yrs 4 & 5

In the the above spreadsheets on the right, the VDB function uses the double declining depreciation method to calculate the depreciation across different periods, of an asset that cost $10,000 at the start of year 1, and has a salvage value of $1,000 after 5 years.

Note that the sum of the depreciations from year 1, years 2 & 3, and years 4 & 5 add up to $9,000, so the asset value at the end of year 5 is,

$10,000 - $9,000 = $1,000

which, as expected, is the specified salvage value.

Note also, that, as the [factor] and [no_switch] arguments are omitted from the examples, these arguments take the default values of 2 and FALSE respectively.


VDB Function Example 2

 Formulas:
 AB
1Depreciation 
2=VDB( 10000, 1000, 60, 0, 1 )- Depreciation during Month 1
3=VDB( 10000, 1000, 60, 0, 6 )- Depreciation during first 6 mths
 Results:
 AB
1Depreciation 
2$333.33- Depreciation during Month 1
3$1,840.56- Depreciation during first 6 mths

If you wish to use the VDB function to find the depreciation for a partial period, you can supply the life, start_period and end_period arguments as a number of months.

This is shown in the above spreadsheets on the right. The life argument of 5 years is equal to 60 months.

In order to the calculate depreciation during the first month, the start_period and end_period are specified as 0 and 1 respectively.

Similarly, if you want to calculate depreciation during the first six months the start_period and end_period are specified as 0 and 6 respectively.


See the Microsoft Office website for further information and examples of the Excel VDB function.


VDB Function Errors

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

Common Errors
#NUM!-

Occurs if either:

  • Any of the supplied cost, salvage, start_period, end_period or [factor] arguments are < 0;
  • The supplied life argument is ≤ 0;
  • The supplied start_period is > the supplied end_period;
  • start_period > life or end_period > life.
#VALUE!-Occurs if any of the supplied arguments are non-numeric values.