The VBA DDB Function

Related Functions:
VBA SLN
VBA SYD

Description

The VBA DDB function calculates the depreciation of an asset during a specified period, using the Double Declining Balance Method, or another specified depreciation rate.

The syntax of the function is:

DDB( Cost, Salvage, Life, Period, [Factor] )

Where the function arguments are:

 Cost - The initial cost of the asset. Salvage - The value of the asset at the end of its useful life. Life - The number of periods over which the asset is to be depreciated. Period - The period number for which you want to calculate the depreciation. [Factor] - An optional argument that is used to specify the rate of depreciation.If the [Factor] argument is omitted from the function, it uses the default value 2 (denoting the double declining depreciation method).

VBA DDB Function Example

In the example below, the VBA DDB function uses the double declining depreciation method to calculate the yearly depreciation of an asset that cost \$10,000 at the start of year 1, and has a salvage value of \$1,000 after 5 years.

 ' Calculate the yearly depreciation of an asset that cost \$10,000 at' the start of year 1, and has a salvage value of \$1,000 after 5 years.Dim ddb_yr1 As DoubleDim ddb_yr2 As DoubleDim ddb_yr3 As DoubleDim ddb_yr4 As DoubleDim ddb_yr5 As Double' Calculate the depreciation during year 1ddb_yr1 = DDB( 10000, 1000, 5, 1 )' ddb_yr1 is now equal to 4000.' Calculate the depreciation during year 2ddb_yr2 = DDB( 10000, 1000, 5, 2 )' ddb_yr2 is now equal to 2400.' Calculate the depreciation during year 3ddb_yr3 = DDB( 10000, 1000, 5, 3 )' ddb_yr3 is now equal to 1440.' Calculate the depreciation during year 4ddb_yr4 = DDB( 10000, 1000, 5, 4 )' ddb_yr4 is now equal to 864.' Calculate the depreciation during year 5ddb_yr5 = DDB( 10000, 1000, 5, 5 )' ddb_yr5 is now equal to 296.

The above VBA code calculates that:

 Depreciation during year 1   =   \$4,000;Depreciation during year 2   =   \$2,400;Depreciation during year 3   =   \$1,440;Depreciation during year 4   =   \$864;Depreciation during year 5   =   \$296.

Note that the five calculated depreciation values add up to \$9,000, which, as expected, is the difference between the cost, \$10,000 and the salvage value, \$1,000.

VBA DDB Function Error

The VBA DDB function produces the Run-time error '5': Invalid procedure call or argument if either:

• The supplied Salvage is < 0
or
• The supplied Life, Period or [Factor] is ≤ 0
or
• The supplied Period > Life.