VDB in Google Sheets

What is VDB in Google Sheets?

The VDB function in Google Sheets calculates the depreciation of an asset for a specified period using the variable declining balance method. It can also switch to straight-line depreciation. The function’s arguments include the asset’s cost, its salvage value, useful life, and the wanted start and end periods. You can also specify the factor for the declining balance optionally and whether to enable the switch to straight-line depreciation.

VDB uses a form of the double-declining balance method. With VDB, one can calculate the depreciation for any portion of an asset’s life. It is not just restricted to one year or the entire lifespan. The optional no_switch argument provides flexibility to switch from accelerated depreciation to straight-line depreciation at a particular point in the asset’s life if it becomes more beneficial for accounting purposes.

Syntax

The VDB in Google Sheets formula is as follows:

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

The arguments are:

1. cost: The initial cost of the asset.
2. salvage: The asset’s salvage value at the end of its useful life.
3. life: The useful life of the asset in years.
4. start_period: The beginning period for which you want to calculate depreciation.
5. end_period: The ending period for which you want to calculate depreciation.
6. factor (optional): The rate of the declining balance. The default is 2 (double-declining balance).
7. no_switch (optional): A TRUE/FALSE argument. If TRUE, the function will not switch to straight-line depreciation when depreciation becomes greater than the declining balance calculation. The default is FALSE.

How To Use VDB Function in Google Sheets?

As mentioned, the VDB function is used to calculate the depreciation of an asset for a specific period. For this, it uses the variable declining balance method. This method applies faster depreciation in the early years and slighter depreciation later.

It is useful in scenarios where we must find the depreciation of assets like machinery and vehicles that lose value more quickly in the beginning and slower over time.

There are two main ways to enter the VDB function in Google Sheets:

1. Enter VDB manually
2. From the Google Sheets menu

Enter VDB Manually

Let us look at the manual method to enter the VDB function. To illustrate this, we will calculate the depreciation of an asset with the following details:

1. Initial cost: 10,000
2. Salvage value: 1,000
3. Useful life: 5 years
4. Start period: 1
5. End period: 2 (second year depreciation)
6. Factor: 2 (double-declining balance)

Step 1: Open Google Sheets and in a new spreadsheet, enter the details in cells.

Step 2: In an empty cell, here we choose B8, we enter the VDB formula. Start with an = sign, followed by the function name and arguments in order:

=VDB(B1, B2, B3, B4, B5, B6)

Step 3: Press Enter. The result will display the depreciation expense for Year 2.

Entering VDB Through the Menu Bar

1. Go to the Insert tab.
2. Choose Function → Financial.
3. From the list, select VDB.
4. Fill in the arguments.
5. Press Enter to get the result.

Examples

Now that we have an idea of how the VDB function works, let us look at a practical real-life scenario where it can be combined with other functions like SWITCH to handle different depreciation methods dynamically.

Example #1 – VDB Function with Switch

In this example, consider a company that owns different types of assets that include vehicles, computers, and machinery. Here, we consider a vehicle in our example to find its depreciation by the end of the third year. For vehicles, the value is 2 (double-declining balance).

Hence, we can use the VDB function with the no_switch parameter as TRUE. This means, the function will not switch to straight-line depreciation when depreciation becomes greater than the declining balance calculation

Step 1: In a new spreadsheet, enter the following asset details:

Step 2: In an empty column, here its Ccolumn G, enter the following formula in cell G2:

=VDB(B2, C2, D2, E2, F2, 2, TRUE)

Here:

B2 → Cost of the asset

C2 → Salvage value

D2 → Useful life in years

E2 → Start period

F2 → End period

2 -> The rate of the declining balance

TRUE -> The function will not switch to straight-line depreciation when depreciation becomes greater than the declining balance calculation.

Step 3: Press Enter. The depreciation expense up to Year 3 will be calculated automatically, with the correct factor applied.

By using VDB with TRUE for to_switch, VDB will never switch, and it will always keep using the accelerated method all the three years we calculate for, even if straight-line would give a higher depreciation in later years.

no_switch controls whether VDB is flexible (FALSE) or rigid (TRUE) in its calculation method.

Example #2 – VDB Function with No Switch

The VDB function in Google Sheets calculates the depreciation of an asset for a specified period using the variable declining balance method.  We want to calculate the depreciation of an asset over its entire useful life (10 years) using the VDB function.

• The asset was purchased for $100,000.
• After 10 years, it is expected to have a salvage value of $10,000.
• The useful life is 10 years.
• We want to calculate depreciation from Period 1 through Period 10.

Let us consider another example, where no_switch is FALSE.

Step 1: Let us enter the data in a sheet, in the format shown below.

In cell D1, enter “Period.”

In cell D2, enter 1.

Drag the fill handle of D2 down to D11 to create periods 1 through 10.

Step 2: Calculate the depreciation for each period.

 In cell F2, enter the formula: =VDB($A$2, $B$2, $C$2, D2-1, D2, 2, FALSE)

1. $B$1: Absolute reference to the cost.
2. $B$2: Absolute reference to the salvage value.
3. $B$3: Absolute reference to the life.
4. D2-1: The start period for the current period (e.g., for period 1, it’s 0).
5. D2: The end period for the current period (e.g., for period 1, it’s 1).
6. 2: The factor for the double-declining balance method.
7. FALSE: Allows switching to straight-line depreciation if it results in greater depreciation.

Step 3: Drag the fill handle of F2 down to F11 to calculate depreciation for all periods.

Step 4:  Create a depreciation chart. For this, select the range E1:F11 (including the headers).

Go to Insert -> Chart.

Google Sheets will likely suggest a column chart, which is suitable for visualizing depreciation over time.

You can customize the chart as required.

Adjust colors and styles as desired.

This will produce a chart illustrating the declining depreciation amounts over the asset’s life using the VDB function.

VDB vs DDB Functions in Google Sheets

Let us compare the DDB and VDB functions.

VDB stands for Variable Declining Balance. It is used to calculate the depreciation for a specified period using a declining balance method. However, it is more flexible because it can switch from accelerated depreciation to straight-line depreciation when it becomes more favorable. Most suited for realistic depreciation schedules where flexibility is needed.

DDB stands for Double Declining Balance. It calculates depreciation using the double-declining balance method. It always applies a constant accelerated depreciation factor. Most suited for assets that lose value very quickly in the early years, without needing flexibility.

Important Things to Note

1. VDB in Google Sheets may throw an error if the start period is greater than the end period
2. You also get an error if the factor is negative, or if cost ≤ salvage.
3. The factor argument defaults to 2 (double-declining balance), which may give results different from what you expect.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand VDB Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What is VDB Function in Google Sheets. We learn how to use VDB function in Google Sheets with its syntax and examples. You can learn more from the following articles. –

SYD in Google Sheets

ISDATE in Google Sheets

SORT in Google Sheets