Excel DB Function – Formula to Calculate Depreciation

In this tutorial, we would learn how to calculate depreciation on a fixed asset using excel formula. Depreciation refers to a decline in the value of the asset over the asset’s useful life. Microsoft has come up with two formulas for calculating depreciation in excel – 1) DB function, and 2) DDB function in excel.

Difference between DB and DDB formula – DB formula calculates depreciation using the fixed-declining balance method, while the DDB formula uses the double-declining balance method.

In this tutorial, we would focus on the DB formula of excel to depreciation calculation in excel.

Here we go 😎

When To Use DB Function in Excel

The Excel DB function is a depreciation formula. The expression DB denotes ‘Declining Balance’.

This function is used to compute depreciation on a fixed asset using the fixed-reducing balance method (declining balance method) in excel. The formula uses a fixed rate of depreciation for calculation purposes.

Syntax and Arguments

=DB(cost, salvage, life, period, [month])

There are five arguments of DB function, as explained below:

• Cost – In this argument, specify the initial cost of the asset.

• Salvage – In this argument, specify the scrap value of the asset at the end of its useful life.

• Life – In this argument, specify the total useful life of the asset.

• Period – In this argument, specify the period of depreciation.

• Month – This is an optional argument. Specify the number of months to depreciate during the first year of useful life. If omitted, the formula assumes the default value as 12.

How Does DB Function Find Depreciation Values

The DB function in excel uses the following mathematical expression to calculate depreciation using the fixed-declining balance method for a particular period (except first and last period):

(Initial Cost – Total depreciation from prior periods) * Rate

For the first and the last period, different calculation is used:

First Period - Cost * Rate * Month / 12

Last Period - ((Initial Cost – Total depreciation from prior periods) * Rate * (12 – month)) / 12

It is important to note that in all the above expressions, Rate = 1 – ((salvage / cost) ^ (1 / life))

Examples of DB Function in Excel

In this section, let us understand how to use the DB function of excel with the help of a formula example.

Surprisingly, this section will give you a practical hands-on the calculating depreciation in excel.

Ex. 1 – Simple Example to Understand Excel DB Formula

Let’s say you want to depreciate an asset having an initial cost of 50,000 for 5 years duration. The salvage value is 5,000.

Use the following formula for first period (year) depreciation:

=DB(50000,5000,5,1)

As a result, the first year’s depreciation will be 18450.

Similarly, use the following formula for second year’s depreciation

 =DB(50000,5000,5,2)

As a result, the formula will return 11641.95.

In the above example, the formula calculates depreciation at a fixed rate:

• Rate of Depreciation = 1 – ((5000/50000) ^ (1/5)) = 36.90%

• First Year’s Depreciation = 50000 * 36.90% * (12/12) = 18450.00

• Second Year’s Depreciation = (50000 – 18450) * 36.90% = 11642.00

Likewise, the formula will work for the third, fourth, and fifth year.

Ex. 2 – Using Optional Month Argument in DB Formula

Let us now use the optional argument – month to calculate the depreciation. The month argument is used when you want to calculate depreciate with different number of months in the first years.

Simply add the month argument to the formula. Let’s say that depreciation starts after 6 months during the first year. Consequently, the formula for the first year goes like this:

 =DB(50000,5000,5,2,6)

As a result, the formula returns the first year’s depreciation as 9,225.

For the next year (i.e. year 2), the value of depreciation gets calculated for 18 months (i.e. 6 months of year 1 and 12 months of year 2).

With this, we have completed with examples of DB formula in excel.

Let us now see some examples that you may encounter while working with excel DB formula.

#NUM! and #VALUE! Error – DB Formula in Excel

• The DB formula returns #VALUE! error code when you specify a non-numeric value any of the formula arguments.

• The DB function returns #NUM! error code when you pass a unrealistic value to any of the arguments, such as:
  • Initial cost or salvage value < 0.
  • Useful life or period <= 0.
  • Month argument <= 0 or > 12.
  • Period argument > Useful life value

Learn more about excel error codes here.

Thank You for Reading 🙂