We have discussed Declining balance method of depreciating the fixed asset in the past. But in this tutorial, we will focus on how we can do it with easy to use Microsoft Excel formula.
Excel Formula Declining Balance
DB( cost, salvage, life, period, [month] )
cost = cost of the asset
salvage = value of the asset at the end of its life
life = usefullife of the asset
period = the year for which you want to calculate the depreciation
month = the number of months for which you want to calculate the depreciation, if it is left empty, Excel will conisder this value as 12 month be default.
LTI company is involved in manufacturing of auto spare parts. It has variety of fixed assets in its factory. Its accountant is not an experienced one and as such finds difficulty in calculating the depreciation using write down value/ declining balance method. The director of the company requested you to prepare an Excel sheet for the accountant so that he could only enter some values in to the Excel sheet cells and the depreciation gets calculated easily. Following are the details of the fixed asset for which your skills are needed.
Initial cost = 400,000
Salvage value = 20,000
Useful Life = 05 Years
Calculate depreciation expense from year 01 to year 05 using Microsoft Excel.
We have created the Excel sheet from the data provided above and calculated the depreciation using DB (Declining Balance) formula:
You can see that the depreciation of 180,400/- is getting calculated from year 1 to year 5. But depreciation from year 2 to 5 is wrong because period 1 is getting copied when we copy the formula from cell B7 ans paste it down the cell. But, in order to calculate the right amount of depreciation from year 2 to 5, we need to take advantage of Row formula. So,we will edit the formula as follows:
Upon doing so,we will get the depreciation figuresover declining balance method as follows:
One we put the above formula in B7, the amount comes 180,400. We just need to copy paste this formula to all the cells below B7 to find out the depreciation expense of the remaining years as shown.