How to use the VDB function
What is the VDB function?
The VDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based on user input, you may use partial periods in this function. VDB is an abbreviation for variable declining balance.
Table of Contents
1. Introduction
What is depreciation of an asset?
Depreciation is an accounting method that allows businesses to allocate the cost of a tangible asset over its useful life. It represents how much of an asset’s value has been used up over time.
Depreciation helps businesses to match their expenses with their revenues, and to reduce their taxable income by deducting the depreciation expense.
What is the the double-declining balance method?
The double-declining balance method is an accelerated depreciation technique used in accounting and finance, it is a specific application of the declining balance method.
Depreciation is calculated by multiplying the asset's book value by a fixed rate of 200% each year. This results in the asset being fully depreciated over roughly half the straight-line depreciation schedule.
The depreciation amount declines each year as the asset's book value declines, depreciation is highest in the first year, tapering down each subsequent year.
Depreciation functions in Excel
| Function | Description |
|---|---|
| SYD(cost, salvage, life, period) | Sum-of-the-years digits depreciation |
| DB(cost, salvage, life, period, [month]) | Fixed-declining balance depreciation |
| DDB(cost, salvage, life, period, [factor]) | Double-declining balance depreciation |
| AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis]) | Depreciation for period using linear method |
| AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis]) | Depreciation from date of purchase to end of period |
| VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]) | Variable declining balance depreciation |
2. Syntax
VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
| Cost | Required. What you pay for the asset. |
| Salvage | Required. The value of the asset at the end of depreciation. |
| Life | Required. The number of periods the asset is being depreciated. |
| Start_period | Required. The start of the range you want to calculate the depreciation. Start_period must use the same units as life. |
| End_period | Required. The end of the range you want to calculate the depreciation. End_period must use the same units as life. |
| [factor] | Optional. How quickly the balance declines, default value is 2 (the double-declining balance method). |
| [no-switch] | Optional. A boolean value determining whether to use to straight-line depreciation when depreciation is larger than the declining balance calculation.
TRUE - Does not switch to a straight-line depreciation even if the depreciation is larger than the declining balance calculation. FALSE - Switches to straight-line depreciation if the depreciation is larger than the declining balance calculation. |
What is salvage value?
Salvage value is the estimated value of an asset at the end of its useful life. It is also known as scrap value or residual value. It is used to calculate the depreciation expense of an asset over its useful life.
What math formula is the VDB function using?
The VDB function uses the same formula as the DDB to calculate depreciation for a period, however, it uses additional arguments and logic to handle the switching to straight-line depreciation if applicable.
Depreciation = Min( (cost - total_depreciation_from_prior_periods) * (factor / life), (cost - salvage - total_depreciation_from_prior_periods))
- If no_switch is set to FALSE (the default), the function will automatically switch to the straight-line depreciation method when it becomes greater than the declining balance method.
- If no_switch is set to TRUE, the function will continue using the declining balance method even if it becomes less than the straight-line method.
3. Example 1
A company purchased a machine for $500,000 with an estimated useful life of 8 years and a salvage value of $50,000. Calculate the depreciation for the third year using the 150% declining balance method?
The arguments are:
- cost: 500,000 - What you payed
- salvage: 50,000 - The value at the end of the life span
- life: 8 - The life span
- start_period: 3 - The start period you want to calculate the depreciation for
- end_period: 3 - The end period you want to calculate the depreciation for
- factor: 1.5 - for 150% declining balance method
- no_switch: FALSE
Formula in cell C24:
The formula returns $61,889.65 in cell C24.
The image above shows a chart displaying a blue curve representing the depreciation for each year across all 8 years which is the life span of the machine. Below the formula in cell C24 is a table that shows the depreciation and the total machine value across periods.
The math formula for calculating the depreciation is:
Min( (cost - total_depreciation_from_prior_periods) * (factor / life), (cost - salvage - total_depreciation_from_prior_periods))
Let's plug the values and see what we get for the third period.
MIN(500,000 - 169921.875)*(1.5/8), (500,000 - 169921.875))
MIN(61889.6484375, 280078.125)
The minimum value is $61,889.65, which is the depreciation for the third year using the 150% declining balance method. This matches the calculated value in cell C24.
4. Example 2
A company purchased a computer system for $80,000 with an estimated useful life of 6 years and a salvage value of $10,000. Calculate the depreciation for the fifth year using the double-declining balance method, but do not switch to straight-line depreciation?
The arguments are:
- cost: 80,000 - What you payed
- salvage: 10,000 - The value at the end of the life span
- life: 6 - The life span
- start_period: 4 - The start period you want to calculate the depreciation for
- end_period: 5 - The end period you want to calculate the depreciation for
- factor: 2 - for 200% declining balance method
- no_switch: TRUE
Formula in cell C24:
The formula returns $5,267.49 in cell C24.
The image above shows a chart displaying a blue curve representing the depreciation for each year across all 6 years which is the life span of the computer system. Below the formula in cell C24 is a table that shows the depreciation and the total value across periods.
The math formula for calculating the depreciation is:
Min( (cost - total_depreciation_from_prior_periods) * (factor / life), (cost - salvage - total_depreciation_from_prior_periods))
Let's plug the values and see what we get for the fifth period.
MIN( (80,000 - 64197.5308641975)*(2/6) , (80,000 - 64197.5308641975) )
MIN( 5267.48971193416, 15802.4691358025 )
The minimum value is $5267.48, which is the depreciation for the fifth year using the 200% declining balance method. This matches the calculated value in cell C24.
Functions in 'Financial' category
The VDB function function is one of 27 functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form