The VDB function in Google Sheets uses the variable declining balance method—a variation of the double declining balance approach—to calculate asset depreciation. Unlike the simpler DDB function, VDB lets you specify both a start and an end period, allowing you to calculate depreciation for any part of an asset’s useful life.
For example, if an asset’s useful life is 10 years, you can calculate depreciation from periods 1 to 5, or 2 to 6, or any other range within that lifespan.
If you want to calculate depreciation for a single period, typically a single year, you can also use the DDB function since both VDB and DDB use the double declining balance method.
I have already covered the double declining balance method in detail in my post about the Google Sheets DDB Function in Depreciation Calculation, so I won’t repeat those details here.
Depreciation Calculation Using the Variable Declining Balance (VDB) Method
Before diving into VDB, let’s quickly recap the double declining balance (DDB) method.
To understand it better, you should first know the Straight Line method (SLN), where the rate of expense remains constant throughout the asset’s useful life.
For example, if an asset has a useful life of 10 years, the depreciation rate is:
100% / 10 = 10% per yearExample:
If the asset cost is $100,000 and the salvage value (scrap value) is $10,000, then the yearly straight-line depreciation is:
(100,000 - 10,000) * 10% = 9,000 per yearIn contrast, the double declining balance method (DDB) doubles the straight-line depreciation rate:
20% per year (i.e., 2 × 10%)
But here, you do not deduct the salvage value when calculating depreciation for each period. So the first year’s depreciation is:
100,000 * 20% = 20,000Subsequent years use the remaining book value:
(100,000 - 20,000) * 20% = 16,000 for the second yearVDB Function Syntax in Google Sheets
VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])- cost — Initial cost of the asset.
- salvage — Salvage value or scrap value of the asset at the end of its useful life.
- life — Useful life of the asset (number of periods).
- start_period — Starting period to calculate depreciation.
- end_period — Ending period to calculate depreciation.
- factor (optional) — Depreciation factor. Default is 2 (double declining balance).
- no_switch (optional) — A TRUE/FALSE value to control switching to straight-line depreciation when it becomes more favorable.
- Default is
FALSE(switch enabled) — meaning Google Sheets automatically switches to straight-line depreciation in later years to ensure the asset fully depreciates down to its salvage value. - Set to
TRUEto disable switching, and depreciation will continue using the declining balance method even if it results in under-depreciation.
- Default is
VDB Formula Example in Google Sheets
Suppose your data is in the following cells:
| Cell | Value |
|---|---|
| B1 | 100000 (cost) |
| B2 | 10000 (salvage) |
| B3 | 10 (life) |
Calculate depreciation for the first period:
=VDB(B1, B2, B3, 0, 1, 2, FALSE)For the second period:
=VDB(B1, B2, B3, 1, 2, 2, FALSE)Switch vs No Switch in VDB Function
The table below shows how depreciation values differ based on whether the no_switch parameter is enabled (TRUE) or disabled (FALSE). The asset has:
- Cost: £100,000
- Salvage value: £10,000
- Useful life: 10 years
- Factor: 2 (Double Declining Balance)
| Period Start | Period End | Switch Enabled (FALSE) | Switch Disabled (TRUE) |
|---|---|---|---|
| 0 | 1 | £20,000.00 | £20,000.00 |
| 1 | 2 | £16,000.00 | £16,000.00 |
| 2 | 3 | £12,800.00 | £12,800.00 |
| 3 | 4 | £10,240.00 | £10,240.00 |
| 4 | 5 | £8,192.00 | £8,192.00 |
| 5 | 6 | £6,553.60 | £6,553.60 |
| 6 | 7 | £5,242.88 | £5,242.88 |
| 7 | 8 | £4,194.30 | £4,194.30 |
| 8 | 9 | £3,388.61 | £3,355.44 |
| 9 | 10 | £3,388.61 | £2,684.35 |
| Total | £90,000.00 | £89,262.58 |
VDB vs DDB Functions in Google Sheets
While you can calculate depreciation for each period using the DDB function separately:
First period: =DDB($B$1, $B$2, $B$3, 1)
Second period: =DDB($B$1, $B$2, $B$3, 2)The VDB function lets you calculate depreciation across multiple periods in one go, like this:
=VDB(B1, B2, B3, 0, 2, 2, FALSE)This example calculates depreciation for periods 0 through 2.
Total Depreciation Over Asset Life Using VDB
You can also use VDB to determine the total amount depreciated over a specific range of periods. For example, to get total depreciation over 10 years:
=VDB(B1, B2, B3, 0, 10, 2, FALSE)This sums the depreciation values from period 0 to 10.
Using the VDB function in Google Sheets gives you more flexibility than DDB, especially when you want depreciation values over specific periods rather than just for single periods.
