The DB(Declining Balance) function returns the depreciation of an asset for a specified period by using the fixed-declining balance method.
The Syntax for the DB function is:
= DB(cost, salvage, life, period, [month])
| Argument | Description |
|---|---|
| Cost | The original cost of the asset |
| Salvage | The value of the asset at the end of its expected useful life |
| Life | The useful life of the asset. It is the number of periods over which the asset is being depreciated |
| Period | The period for which you want to calculate the depreciation. |
| [Month] Optional | The number of months depreciation will be computed for in the first year. If not used the default is 12. |
You must specify a period argument. This is the period for which you want to compute the depreciation. Depreciation doesn’t have to be computed on an annual basis. The period argument must match the type of period used in the Life argument. For example if you specify 5 for the period and the Life is 7 years then the DB function will determine the depreciation for the 5th year of the equipment’s life. If the Life is 30 months and you enter 14 for the period then depreciation will be computed for the 14th month.
If you wanted the depreciation to start in April then enter 4 for the optional month argument.
Practice for using the Fixed Declining Balance Method
A piece of equipment cost $50,000, has a salvage value of $2,000 and a useful life of 5years. You will compute the amount of depreciation for each of the 5 years and the Book value. The book value of an asset is the Cost of the asset minus its accumulated depreciation.
- Create a new worksheet named DB.
- Enter the worksheet data as shown below. Format cell B2 and the cell range B7:C11 using the Accounting format.
- In cell B7 start the formula by typing
=DB(. Excel’s intellisense displays the list of arguments.
The cost is $50,000. Type B2. Press F4 to make the address absolute. Enter a comma to separate the argument.
- The first year’s book value is the original cost minus the first year’s depreciation.
In Cell C7 enter=B2-B7. Press Enter - The second year’s book value is the first year’s book value minus the second year’s depreciation.
In Cell C8 enter= C7-B8. Press Ctrl + Enter. - Drag the AutoFill handle on cell C8 down through cell C11.
Results:
- You can test how changing the Salvage value will affect the depreciation. Change the Salvage value in Cell B3 from 2000 to 0. Change the Salvage value back to 2000.
Type B3 for the salvage value and then Press F4. Enter a comma. T
ype B4 for the life and then Press F4. Enter a comma.
Type A7 for the period and then Press Ctrl + Enter.
Drag the AutoFill handle on cell B7 down through cell B11.
*NOTE: If you enter a value of 0 for the Salvage value in a Fixed Declining Balance the asset will fully depreciate in the first year.
*NOTE: The Book Value for the last year of asset depreciation may not come out exactly to the salvage value when using the Fixed Declining Balance or Double Declining Balance methods. This may be acceptable or you may need to change the formula or adjust the ending entry. There is a declining balance that will come out exactly to the salvage value or 0 if there is no salvage value and that is the VDB (Variable Declining Balance) method. There is a menu item that will take you to the VDB depreciation method at the top of this web page.
Practice for using the Fiexed Declining Balance Method (with Optional Month argument)
You purchased a Bulldozer for $75,000 in May. The Bulldozer has a useful life of 5 years. The depreciation will be calculated for the last 8 months of the first year. Since the Bulldozer will be depreciated over a full 5 years, the year depreciation will extend over the first 4 months of the 6th year.
- Create a new worksheet named FDB
- Enter the data in the worksheet as shown below. Format cell B2 and range B7:C11 using the Accounting format.
Enter the following formula in cell B7. Press Ctrl + Enter
The formula in cell B7 uses the optional month argument. The month argument is the number of months that depreciation will be computed for in the first year. Since we will be computing depreciation in the first year from May through December we will enter 8 for the argument.
- Drag the AutoFill handle on cell B7 down through cell B12. The cells for the Book Value column will be automatically recalculated.
- The first year’s book value is the original cost minus the first year’s depreciation. In Cell C7 enter =B2-B7. Press Enter.
- The second year’s book value is the first year’s book value minus the second year’s depreciation. In Cell C8 enter = C7-B8. Press Ctrl + Enter.
- Drag the AutoFill handle on cell C8 down through cell C12. The sixth year displays depreciation for the first 4 months of the year. This gives us our full 5 years of depreciation.
Results:
