This function calculates the depreciation amounts for an asset using the declining balance (geometric-degressive) depreciation method, accounting for partial years (in complete months) in the first depreciation period.
Syntax
DB(Purchase_Value; Residual_Value; Life; Period; [Months])
Arguments
- Purchase_Value (required)
The purchase cost of an asset (net purchase price plus incidental expenses minus purchase cost reductions).- If a non-numeric value is provided, the #VALUE! error is returned.
- If a negative number is entered, the #NUMBER! error is returned.
- Residual_Value (required)
The value of the asset at the end of its depreciation period.- If a non-numeric value is provided, the #VALUE! error is returned.
- If a negative number is entered, the #NUMBER! error is returned.
- Life (required)
The number of periods over which the asset is depreciated.- Must be a positive integer.
- Period (required)
The specific period within the depreciation duration for which the depreciation amount is calculated.- Must be a positive integer not exceeding the value of Life.
- Months (optional)
Specifies the duration of a partial period in the purchase year (in complete months).- If omitted, Excel assumes a full year (12 months).
Background
Depreciation reflects the loss of an asset’s value over time and makes this loss visible. It should not be confused with wear-and-tear depreciation, which relates to the cost allocation of an asset as an operating expense from a tax perspective.
For the declining balance depreciation rate, the following formula applies:
Depreciation Rate=1−(Residual_ValuePurchase_Value)1LifeDepreciation Rate=1−(Purchase_ValueResidual_Value)Life1
This explains why a residual value of zero is impractical—depreciation would fully occur in the first year. In such cases, a residual value of $1,000.00 is typically assumed.
In Excel:
- Depreciation values are rounded to three decimal places.
- Each period’s depreciation is applied against the book value.
- The resulting depreciation amount reduces the book value for the next period.
- If the first period is shorter than a year, the depreciation rate is adjusted proportionally (divided by 12).
Example
An asset with a purchase cost of $1,000.00 is depreciated over five years to a residual value of $100.00 using the declining balance method. The depreciation amount for each period can be calculated using DB() and subtracted from the previous period’s book value.
Alternatively, a depreciation schedule can be created by applying the formulas described above to compute the first depreciation amount and subsequent values.
