Excel DB Function

The Excel DB function has the syntax shown below.

DB (cost, salvage, life, period, month)

Use of the Excel DB Function

The Excel DB function is used to calculate the declining balance depreciation of a long term asset based on a calculated depreciation rate.

Declining Balance Depreciation Rate

The declining balance depreciation rate implicit in the Excel DB function is based on the formula shown below.

So for example, if a business has purchased equipment costing 15,000 and expects it to have a useful life of 5 years and an estimated salvage value of 1,166, then the declining balance depreciation rate calculation using the formula above would be as follows:

Rate = 1 - Years√(Salvage Value / Cost)
Rate = 1 - 5√(1,166 / 15,000) = 40%

This rate will reduce the cost of the asset (15,000) down to its salvage value (1,166) after a period of 5 years.

Excel DB Function Example

If a business purchases an asset costing 15,000 which is estimated to have a useful life of 5 years and a salvage value of 1,166, then the declining balance depreciation for period 1 based on the straight line rate, is calculated using the Excel DB function as follows:

Cost = 15,000
Salvage = 1,166
Life = 5 years
Period = 1
Month = Default = 12
Depreciation = DB (cost, salvage, life, period, month)
Depreciation = DB (15000,1166,5,1)
Depreciation = 6,000

In this example, the depreciation was required for period 1, if the depreciation had been required for period 3, then the period value would be set to 3 as follows:

Cost = 15,000
Salvage = 1,166
Life = 5 years
Period = 3
Month = Default = 12
Depreciation = DB (cost, salvage, life, period, month)
Depreciation = DB (15000,1166,5,3)
Depreciation = 2,160

Acquisition During the Year

In the above example the month argument was left blank and defaults to a value of 12 months, meaning that the asset is purchased at the start of year 1.

If the asset is purchased part way through the year and the business adopts a policy of only charging depreciation from the month of acquisition, then the month argument should be set to the number of months in which the asset was in use during the year.

For example, if the asset was purchased at the end on month five, there are seven months of the year remaining for which depreciation needs to be charged, and the month argument should be set to 7.

The declining balance depreciation for period 1 is then calculated using the Excel DB function as follows:

Cost = 15,000
Salvage = 1,166
Life = 5 years
Period = 1
Month = 7
Depreciation = DB (cost, salvage, life, period, month)
Depreciation = DB (15000,1166,5,1,7)
Depreciation = 3,500

And for month 3

Cost = 15,000
Salvage = 1,166
Life = 5 years
Period = 3
Month = 7
Depreciation = DB (cost, salvage, life, period, month)
Depreciation = DB (15000,1166,5,3,7)
Depreciation = 2,760

Using the Excel DB Function for Monthly Periods

In the above example the Excel DB function was used to calculate the annual depreciation. The function could equally well be used to calculate the monthly depreciation by defining the life in terms of months instead of years.

Suppose for example, the business purchased an asset costing 15,000 with an estimated salvage value of 1,166 and a useful life of 60 months, then using the Excel DB function the declining balance depreciation for say month 9, is calculated as follows:

Cost = 15,000
Salvage = 1,166
Life = 60 months
Period = 9
Month = Default = 12
Depreciation = DB (cost, salvage, life, period, month)
Depreciation = DB (15000,1166,60,9)
Depreciation = 447

The declining balance depreciation for period 9 is calculated as 447 a month.

The Excel DB function is one of many Excel finance functions used in financial calculations, discover another at the links below.

• Excel SYD Function
• Excel EFFECT Function