VDB Function

What is the VDB Declining Balance Function?

The Variable Decline Balance (VDB) function calculates depreciation in accordance with the "Double Declining Balance" (DDB) method. This results in a larger depreciation expense in the early years of an asset's life and lower depreciation costs later in the asset's life.

It is commonly utilized by accountants, financial analysts, and professionals involved in asset depreciation calculations.

The DDB method calculates yearly depreciation by multiplying the asset's book value by a fixed percentage rate, which is set as two times the straight-line depreciation rate, where

Asset Book Value = Cost - Depreciation

This creates the following formula for calculating depreciation in accordance with the DDB method:

Depreciation Expense = Book Value x (2 / Useful Life)

This method is suitable for assets anticipated to yield higher productivity and revenue during the initial phase of their useful life. It also benefits companies that need to reduce taxable income during the early years of the asset's life.

As this function employs the DDB method, it proves beneficial for accounting purposes in Excel, especially for assets exhibiting a high depreciation rate early in their life, such as vehicles and heavy machinery.

This function can also be used in financial forecasting and modeling, as you can use this function to calculate the depreciation expense for a single or set of assets, as depreciation expense is an essential factor in determining the net income & profitability of a company.

Understanding the VDB Function

As mentioned earlier, the VDB function stands for "variable declining balance." It is a method of depreciation used when performing accounting-related activities to calculate the value of an asset over time.

The variable declining balance function calculates depreciation using a declining balance method. While it defaults to the double declining balance method, you can specify a factor to multiply by to calculate straight-line depreciation.

Before we break down the syntax for the VDB function, let's look at the arithmetic calculations that Microsoft Excel does behind the scenes to understand how the variable declining balance function works. First, the variable declining balance is calculated with these formulas:

1. The depreciation rate per period 

Depreciation Rate = 1 - ((Salvage / Cost) ^ (1 / Life))

2. The depreciation expense for a given period

Depreciation Expense = Cost * Factor x Number of Periods

3. The number of periods

Number of Periods = (1 - (salvage / cost) ^ (1 / life)) ^ (period - start_period + 1)

If the depreciation rate per period falls below the straight-line depreciation rate, the variable declining balance function switches to straight-line depreciation for the remaining periods.

VDB Function Formula

As for the variable declining balance function with Microsoft Excel, it takes four arguments: Asset cost, salvage value, useful life, & time period.

They are denoted as

Where:

• cost = the purchase price of the asset
• salvage = the value of the asset at the end of its useful life
• life = the useful life of the asset in periods
• start_period = the starting period for which you want to calculate depreciation
• end_period = the ending period for which you want to calculate depreciation
• factor = the depreciation rate per period (optional)
• no_switch = a logical value that specifies whether to switch to straight-line depreciation when the depreciation rate is less than the straight-line depreciation rate (optional)

This function in Microsoft Excel will help accountants and financial analysts build financial models and create a fixed asset depreciation schedule.

Calculate Declining Balance with the VDB Function in Excel

Now that we know how the VDB function works let's work through a simulated situation where we need to use our knowledge of this function to perform various calculations:

You own a company that recently purchased a new machine for $100,000 on January 1st of this year. The machine is expected to have a useful life of 10 years, and its salvage value at the end of its useful life is expected to be $10,000.

We will be using this machine in our manufacturing process, and since the technology is brand new, we plan to use it a lot more in the machine's early years than in its later years.

We also know we will depreciate the asset 1.5 times our straight-line rate. This is because the tax limits imposed by the state do not let us depreciate more than a 150% rate.

Using this information, let's build a depreciation schedule for this asset. Well, start by building a table in Microsoft Excel with the information we know:

We input $100,000 for the beginning balance because we bought the asset at the beginning of the year.

Let's start calculating the value of the depreciation for the first year. In the first row for depreciation, we will enter =VDB( to begin inputting our data.

Using what we learned from the previous section, we know:

• cost = The value of the asset at the beginning of the year. We will insert the cell for the purchase value (i.e., $100,000)
• salvage =  This is the asset's salvage value at the end of its useful life. This cell value corresponds to the $10,000 we were told in the prompt
• life = The asset's useful life. This is going to be 10 (from the prompt)
• start period = The year in question. This is year 0 because we are calculating the depreciation for the 1st year
• end period = Since we are interested in finding out the depreciation cost for this year, we will be entering "1" here to account for the whole year
• factor = We were told in the prompt that the depreciation rate per period would be 1.5 times the straight-line rate so that this argument will be 1.5
• no_switch = FALSE. We want to switch to the straight-line depreciation method when the depreciation rate falls below the straight-line rate.

After inputting our arguments, our formula should look like this:

Hitting "Enter" gives us the depreciation value for this year.

Now, to calculate the ending balance, it is as simple as subtracting the beginning balance from the depreciation value.

This gives us our ending asset value for the year.

We can simply add the depreciation value into the accumulated depreciation column to begin calculating the depreciation we have accrued throughout this process.

Our next step is to set the ending balance from Year 1 as the beginning balance of Year 2.

We can now begin formula auto-filling. We can start with the "Beginning Balance" column. This way, the information in these cells will automatically be updated as we add more information:

Now, we may be tempted to do this to the next column, but there are some edits we must make to our formula to ensure it does not get messed up in the process of auto-filling. 

First, we must set some absolute reference points for certain arguments to ensure those values do not change. We can do this by adding a "$" in front of a cell's row and column values.

We must set absolute reference cells for the following arguments:

• Cost
• Salvage
• Life

That is because these values stay the same throughout the formula. The only thing changing is the period in which we are finding depreciation. This makes our new formula:

Now doing the autofill for this column, our new table looks like this:

We can also autofill our Ending Balance columns:

We will use a "=S() "formula with an absolute reference cell to calculate our accumulated depreciation. Originally, we set our first value in the "Accumulated Depreciation" column as just $15,000, but we will update that with our new formula.

This gives us the same value as before but allows us to auto-fill the column to get:

This is our fixed asset depreciation schedule. As we can see, our accumulated depreciation equals our beginning balance minus our salvage value. This is a good indicator that our table's calculations & formulas are correct.

We can also note that the amount depreciated from the asset decreases over time before eventually being depreciated by the straight-line method. We can see this graphically below:

If you want to get more comfortable working with Excel and making tables & models, consider taking the Wall Street Oasis Excel Modeling Course.

Summary

The variable declining balance function (VDB) is a function built-in in Microsoft Excel. It is a function designed to calculate asset depreciation over a period based on the declining balance method.

The declining balance depreciation method is utilized for assets anticipated to experience higher usage or revenue generation early in their useful life, with diminishing utilization or revenue thereafter.

As such, the DDB method depreciated more of the asset's value in its earlier years and less later. The variable declining balance function can be denoted as 

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

For example, suppose your company purchased a machine with a useful life of 5 years for $10,000, with a salvage value after the 5 years of $2,000 and a depreciation rate of 1.5. You want to know the depreciation expense for the first 3 years.

Your formula would look like this:

This would result in a depreciation expense of $3,570 for the first 3 years of the asset's life, calculated using the variable declining balance method (VDB).

This function in Microsoft Excel is a powerful formula for calculating asset depreciation over time via a DDB method. It allows for better customization of depreciation and is widely used in financial modeling, forecasting, and statement analysis to determine net income & profitability.

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• DB Function
• MDURATION Function
• NPER Function
• OFFSET Function
• SLN Function