In last post, we learnt about how to calculate compounded interest in MS Excel. In this post, we shall learn how to calculate different types of depreciation on assets costs in Excel.
Excel has got 5 different methods to calculate depreciation. Let's learn them one by one.
SLN (Straight Line) function is used to return the straight-line depreciation of an assets for one period. In B7 cell, we have '=SLN($B$2,$B$3,$B$4)' formula. Notice that we used Absolute cell reference in the formula so that when we copy-paste it to other relevant cells, the cell reference get sticked. Here are the arguments of SLN function
When we drag the same formula to other B7 to B14 cells, we get following result. When we sum the values of B7 to B14, we get 9,000 (The asset value - residual value) as shown in B15 cell.
SYD (Sum of Years's Digits) function in Excel returns the sum-of-years' digits depreciation of an asset for a specific period. In C7 cell, we have '=SYD($B$2,$B$3,$B$4,A7)' formula that accepts
DB (Declining Balance) function is used to return the depreciation of an assets for a specific period using fixed-decline balance method. In D7 cell, we have '=DB($B$2,$B$3,$B$4,A7)' formulat that accepts
DDB (Double Declining Balance) function is used to return the depreciation of an assets for a specific period using double-declining balance method. In E7 cell, we have '=DDB($B$2,$B$3,$B$4,A7)' that accepts
VDB (Variable Declaring Balance) function is similar as DDB function however we can specify period (starting and ending) as 4th and 5th parameters. F7 cell has '=VDB($B$2,$B$3,$B$4,A7-1,A7)' formula that accepts following parameters
the result is displayed below.
You may notice that there is slight difference in total for DDB and VDB values, this is because VDB function is intelligent enough to switch to Straight Line calculation when it sees that the last period is not reaching to the (Cost - Salvage amount).
Hope you liked this tutorials post. Do share to your friends and colleagues !Views: 4499 | Post Order: 36