Returns the depreciation of an asset for any period using the variable declining balance method. Provides flexible depreciation calculation allowing custom start/end periods and optional switching to straight-line when beneficial. Essential for complex depreciation scenarios requiring period-specific calculations and method flexibility.
Master the fundamentals of Excel VDB function
The VDB (Variable Declining Balance) function calculates depreciation using the variable declining balance method, providing maximum flexibility in depreciation calculations. VDB allows calculation of cumulative depreciation for any period range (not just single periods), supports custom depreciation factors (like DDB), and can automatically switch to straight-line depreciation when it becomes more beneficial. This makes VDB ideal for complex depreciation scenarios, tax optimization strategies, and financial modeling where period-specific depreciation is needed. VDB uses declining balance depreciation but can optimize by switching to straight-line, ensuring the asset reaches salvage value efficiently. The period range flexibility allows calculating depreciation for custom periods (e.g., years 3-7) rather than requiring cumulative calculations.
VDB calculates cumulative depreciation for any period range (start_period to end_period). Not limited to single periods like DDB or DB.
VDB can automatically switch to straight-line when it provides more depreciation. no_switch=FALSE (default) enables this optimization.
Factor parameter allows custom acceleration levels: 2 for double declining (default), 1.5 for 150%, 3 for triple, etc. Same flexibility as DDB.
Automatic straight-line switching ensures asset reaches salvage value efficiently, unlike pure DDB which may not fully depreciate.
Returns cumulative depreciation for entire range in single calculation, eliminating need for multiple period calculations.
Maximum flexibility allows optimal depreciation strategies for tax planning, financial reporting, and cash flow optimization.
Function-specific parameters
Function-specific return type
Calculate depreciation for custom period ranges
Optimize depreciation for specific tax periods
Prepare flexible depreciation schedules
Calculate depreciation for specific asset periods
Plan depreciation for specific time periods
Analyze depreciation for custom periods
Exact matching required
Returns numeric position
Handles missing text gracefully
=VDB(cost, salvage, life, start_period, end_period, factor, no_switch)The initial cost of the asset. Must be positive. This is the original purchase price or acquisition cost.
The value of the asset at the end of its useful life (salvage value). Must be >= 0. Typically lower than cost.
The number of periods over which the asset is depreciated (useful life). Must be positive. Defines total depreciation period.
The starting period for depreciation calculation. Must be between 0 and life. Use 0 for first period start.
The ending period for depreciation calculation. Must be >= start_period and <= life. VDB returns cumulative depreciation for this range.
The rate at which balance declines. Default is 2 (double-declining). Use 1.5 for 150%, 3 for triple, etc. Similar to DDB factor.
Whether to switch to straight-line when more beneficial. FALSE (default) = switch when beneficial, TRUE = never switch (pure declining balance).
The cumulative depreciation expense for the period range (positive value)
Description: Calculates variable declining balance depreciation for a period range
Calculate depreciation for first year of asset
Returns $10,000 for first year (periods 0-1). Uses double declining balance method by default.
Using VDB function in VBA through WorksheetFunction
Sub VDBExample()
Dim result As Double
Dim cost As Double, salvage As Double, life As Integer
Dim startPeriod As Double, endPeriod As Double
cost = 50000
salvage = 5000
life = 10
startPeriod = 0
endPeriod = 1
result = Application.WorksheetFunction.VDb(cost, salvage, life, startPeriod, endPeriod)
Range("A1").Value = result
Range("A1").NumberFormat = "$#,##0.00"
MsgBox "First year depreciation: quot; & Format(result, "#,##0.00")
End Sub
' Calculate depreciation for multiple period ranges
Sub CalculateMultipleRanges()
Dim cost As Double, salvage As Double, life As Integer
Dim i As Integer
Dim depreciation As Double
cost = 50000
salvage = 5000
life = 10
For i = 1 To 10
depreciation = Application.WorksheetFunction.VDb(cost, salvage, life, i - 1, i)
Range("A" & i).Value = "Year " & i
Range("B" & i).Value = depreciation
Range("B" & i).NumberFormat = "$#,##0.00"
Next i
End Sub
' Calculate cumulative depreciation for period range
Sub CalculatePeriodRange()
Dim cost As Double, salvage As Double, life As Integer
Dim startPeriod As Double, endPeriod As Double
Dim depreciation As Double
cost = 50000
salvage = 5000
life = 10
startPeriod = 2 ' Start at year 3
endPeriod = 7 ' End at year 7
depreciation = Application.WorksheetFunction.VDb(cost, salvage, life, startPeriod, endPeriod)
Range("A1").Value = depreciation
Range("A1").NumberFormat = "$#,##0.00"
MsgBox "Depreciation for years 3-7: quot; & Format(depreciation, "#,##0.00")
End Sub
' Custom factor and no_switch options
Sub VDBWithOptions()
Dim cost As Double, salvage As Double, life As Integer
Dim startPeriod As Double, endPeriod As Double
Dim factor As Double
Dim noSwitch As Boolean
Dim depreciation As Double
cost = 50000
salvage = 5000
life = 10
startPeriod = 0
endPeriod = 1
factor = 1.5 ' 150% declining balance
noSwitch = False ' Allow switching to straight-line
depreciation = Application.WorksheetFunction.VDb(cost, salvage, life, startPeriod, endPeriod, factor, noSwitch)
Range("A1").Value = depreciation
Range("A1").NumberFormat = "$#,##0.00"
MsgBox "Custom VDB depreciation: quot; & Format(depreciation, "#,##0.00")
End SubCalculate depreciation for custom period ranges
Optimize depreciation for specific tax periods
Calculate depreciation for reporting periods
Analyze depreciation for specific years
Track depreciation for custom periods
Plan depreciation for planning periods
VDB returns #NUM! error
=VDB(50000, 5000, 10, 0, 1)Solution: Check: 1) start_period >= 0 and start_period < life, 2) end_period > start_period and end_period <= life, 3) life > 0, 4) Salvage >= 0, 5) Factor > 0 if specified, 6) All numeric values are valid. Verify period range is valid.
VDB returns values that seem incorrect
=VDB(50000, 5000, 10, 0, 1)Solution: Verify: 1) Period range is correct (start_period to end_period), 2) Factor is appropriate (default 2), 3) no_switch setting is as intended. VDB returns cumulative depreciation for the range.
Uncertain about start_period and end_period usage
=VDB(cost, salvage, life, 0, 1)Solution: start_period and end_period define the range: VDB returns cumulative depreciation from start_period to end_period. Use 0 for first period start, 1 for first period end. For year 1: start=0, end=1.
VDB returns #VALUE! error
=VDB(A1, B1, C1, D1, E1)Solution: Non-numeric values in parameters. Check: 1) All parameters are numbers, 2) Cell references contain numeric values, 3) No text in any parameter. Verify inputs are valid numbers.
VDB not switching to straight-line as expected
=VDB(cost, salvage, life, start, end, factor, FALSE)Solution: Check no_switch parameter: FALSE (default) allows switching, TRUE prevents it. Switching occurs when straight-line provides more depreciation for remaining periods. Verify no_switch setting.