ExcelFunctions.net

Search Site:

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). |

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 Double Dim ddb_yr2 As Double Dim ddb_yr3 As Double Dim ddb_yr4 As Double Dim ddb_yr5 As Double ' Calculate the depreciation during year 1 ddb_yr1 = DDB( 10000, 1000, 5, 1 ) ' ddb_yr1 is now equal to 4000. ' Calculate the depreciation during year 2 ddb_yr2 = DDB( 10000, 1000, 5, 2 ) ' ddb_yr2 is now equal to 2400. ' Calculate the depreciation during year 3 ddb_yr3 = DDB( 10000, 1000, 5, 3 ) ' ddb_yr3 is now equal to 1440. ' Calculate the depreciation during year 4 ' Calculate the depreciation during year 5ddb_yr4 = DDB( 10000, 1000, 5, 4 ) ' ddb_yr4 is now equal to 864. ddb_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.

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

- The supplied Salvage is < 0

- The supplied Life, Period or [Factor] is ≤ 0

- The supplied Period > Life.