Purpose
Return value
Syntax
=DB(cost,salvage,life,period,[month])
- 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.
Using the DB function
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 |
Purpose
Return value
Syntax
=DDB(cost,salvage,life,period,[factor])
- 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.
- factor - [optional] Rate at which the balance declines. If omitted, defaults to 2.
Using the DDB function
The DDB function calculates the depreciation of an asset in a given period using the double-declining balance method. The double-declining balance method computes depreciation at an accelerated rate – depreciation is highest in the first period and decreases in each successive period. To calculate depreciation, the DDB function uses the following formula:
=MIN((cost-pd)*(factor/life),(cost-salvage-pd))
where pd = total depreciation in all prior periods.
The factor argument is optional and defaults to 2, which specifies the double-declining balance method. You can change factor to another value to influence the rate of depreciation. This is why DDB is sometimes defined as “double-declining method” or “other method”. In the example shown, the formula in D7 copied down, is:
=DDB(cost,salvage,life,B7)
