Using Microsoft Excel's Declining Balance Depreciation Function

Making financial forecasts or performing investment analyses that need to include depreciation? Microsoft Excel can help. Excel's DB, or declining balance, depreciation function lets you calculate fixed-declining balance depreciation.

The DB function calculates fixed declining balance depreciation for an asset given the cost, it’s salvage value, estimated economic life, the accounting period for which depreciation is being calculated, and, optionally, the number of month in first year. (If you don’t include the optional month argument, Excel sets this value to 12.) The DB function uses the following syntax:

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

Suppose, for example, that you must calculate the fixed declining balance depreciation for equipment that costs $50,000, lasts five years, will have a salvage value of $10,000 at the end of the fifth year, and that was placed into service in the third month of the first year. To calculate the depreciation for the first year, you use the following formula:

=DB(50000,10000,5,1,3)

The function returns the value 3437.5. To calculate the depreciation for the second year, you use the formula

=DB(50000,10000,5,2,3)

The function returns the value 12804.69

The distinguishing feature of fixed-declining balance depreciation is that it calculates depreciation at a fixed rate based on the estimated cost, salvage value, and economic life of the asset. Excel calculates this rate using the following formula:

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

and then rounds this value to the nearest three decimal places. To calculate the depreciation for a period, Excel multiplies the rate by the sum of the original cost less the accumulated depreciation to date.

NOTE: The accumulated depreciation equals the original cost minus the previous periods’ depreciation.

Excel uses variations of the standard fixed-declining balance formula for the first and last periods. For the first period, Excel calculates the depreciation by using the following formula:

First-period depreciation=cost * rate * month / 12

For the last period, Excel calculates the depreciation using the following formula (which essentially just depreciates the asset down to its salvage value):

Last-period depreciation=((cost. accumulated depreciation)*rate*(12-month))/12

ABOUT THE AUTHOR