The Variable Declining Balance is a newer and more versatile method than the other declining balance methods. The benefits of using VDB are:
- It can be used to compute the depreciation amount for any period.
- It has the benefit over the other declining balance methods in that it will always depreciate down to zero or to the salvage value.
- Like the Double Declining Balance method it has a factor argument that uses 2 for a default but can be set to any value you want.
- Unless you change the no_switch argument it will switch to straight-line when depreciation is greater than the declining balance.
The Syntax for the VDB function is:
= VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
| Argument | Description |
|---|---|
| Cost | The original cost of the asset |
| Salvage | The value of the asset at the end of its expected useful life |
| Life | The useful life of the asset. It is the number of periods over which the asset is being depreciated |
| Start_period | The starting period for computing the depreciation. The Start_period must use the same units used in the life argument |
| End_Period | The ending period for computing the depreciation. The end_period must be the same units used in the life argument. |
| Factor Optional | The factor is 2 by default giving you the double-declining method but you can change it to whatever you want. |
| No_switch Optional | When omitted or set to False Excel switches to straight-line depreciation when depreciation is greater than the declining balance. If set to True Excel stays with the declining balance method. |
The table below shows the formulas for computing the depreciation for an asset that cost $80,000 has a salvage value of $4,000 and a life of 5 years. The first year has a Start_period of 0 and an End_period of 1. The second year has a Start_period of 1 and an End_period of 2.
| Year | Formula |
|---|---|
| 1 | =VDB(80000,4000,5,0,1) |
| 2 | =VDB(80000,4000,5,1,2) |
| 3 | =VDB(80000,4000,5,2,3) |
| 4 | =VDB(80000,4000,5,3,4) |
| 5 | =VDB(80000,4000,5,4,5) |
'The periods do not have to be years they can be months or days or quarters, etc. The start_period and end_period arguments have to be of the same type as the period used in the life argument. The formula =VDB(100000,5000,60,0,3)computes the depreciation for the first 3 months for an asset that cost $100,000 has a salvage value of 5000, and has a life of 60 months.
Practice for using the Variable Declining Balance Method
This practice demonstrates the difference between setting the no_switch value to TRUE and setting the no_switch value to FALSE.- Create a new worksheet named VDB.
- Enter the data as shown below.
- Type
=VDB(in cell B6.
Click on cell B1. Press F4. Type a comma.
Click on cell B2. Press F4. Type a comma.
Click on cell B3. Press F4. Type a comma.
Since the start_period has to have a value of 1 less than the current period, we will subtract 1 from cell A6 for the first year, and we will use cell A6 for the end_period. The start_period and end_period for the other cells in the column will be automatically adjusted when we use AutoFill.
Type A6 – 1. Enter a comma.
Type A6. Enter a comma.
We want to use the default of 2 for the factor. Type a 2. Enter a comma.
The options for the no_switch appear
With TRUE selected press the Tab key.
The switch is set to TRUE so that we stay with the double declining balance. Type the ending right parenthesis. Your formula show now be=VDB($B$1,$B$2,$B$3,A6-1,A6,2,TRUE)Press Ctrl + Enter. - Using the AutoFill handle drag down through cell B10.
- In cell C6 enter the formula
=VDB($B$1,$B$2,$B$3,A6-1,A6). Press Ctrl + Enter. Since the default factor is 2 and the no_switch default is false; we do not need to enter arguments for them. - Using the AutoFill handle drag down through cell C10.
- Select cells B11 and C11. On the HOME tab in the Editing group click on the Autosum button.
