Exceljet

Quick, clean, and to the point

Excel DB Function

Excel DB function
Summary 

The Excel DB function returns the depreciation of an asset for a specified period using the fixed-declining balance method. The calculation is based on initial asset cost, salvage value, the number of periods over which the asset is depreciated and, optionally, the number of months in the first year.

Purpose 
Depreciation - fixed-declining balance
Return value 
Depreciation in given period
Syntax 
=DB (cost, salvage, life, period, [month])
Arguments 
  • cost - Initial cost of asset.
  • salvage - Asset value at the end of the depreciation.
  • life - Periods over which asset is depreciated.
  • period - Period to calculation depreciation for.
  • month - [optional] Number of months in the first year. Defaults to 12.
Usage notes 

The Excel DB function returns the depreciation of an asset for a specified period using the fixed-declining balance method. The calculated depreciation is based on initial asset cost, salvage value, the number of periods over which the asset is depreciated and, optionally, the number of months in the first year.

In the example shown, the formula in C6, copied down, is:

=DB(cost,salvage,life,B6)

where named ranges are "cost" = G4, "salvage" = G5, and  "life" = G6.

Fixed-declining balance calculation

To get a rate to use to calculate depreciation based on fixed-declining balance, Excel uses the following formula:

rate=1-((salvage/cost)^(1/life))

To calculate depreciation in each year, Excel uses a formula like this:

=(cost-prior depreciation)*rate

However, depreciation for the first and last year is calculated differently to account for the month argument. The table below shows the calculation used to depreciate an asset over 5 years. If 3 is supplied for month, depreciation the first year is based on 3 months only, and depreciation the last year is based on 9 months.

Year Depreciation Calculation
1 =cost * rate * month / 12
2 =(cost - prior depreciation) * rate
3 =(cost - prior depreciation) * rate
4 =(cost - prior depreciation) * rate
5 =((cost - prior depreciation) * rate * (12 - month)) / 12

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.