Welcome to this tutorial on how to calculate depreciation in Excel. Depreciation is the gradual reduction in the value of an asset over time. During this tutorial, you will learn how to calculate depreciation with Excel using five different functions: the straight-line method, the declining balance method, the sum-of-the-year’s-digits method, the double-declining balance method, and the variable declining balance method.
In our example, we will have a depreciating asset with a cost of $30,000. The residual value will be $3,000, and the useful life will be 20 years. We will use this example when calculating each method.
Straight Line Method
The straight-line method of depreciation is a way to figure out how much an asset, like a building or a machine, decreases in value each year. It’s done by taking the cost of the asset and subtracting its value at the end of its useful life, then dividing that number by the number of years the asset will be used. This method is considered the easiest and most common way of calculating depreciation.
To use the straight-line method in Excel, we will use the SLN function. The syntax of the function is as follows:
=SLN(cost, salvage, life)
- cost is the initial cost of the asset
- salvage is the value of the asset at the end of its useful life
- life is the total number of periods over which the asset will be depreciated
Using the example, we will use the formula =SLN($B$2,$B$3,$B$4), and copy and paste it to the other years.
Note: We are locking the reference of the cells with the dollar sign ($), e.g., $B$2, so that when we copy the formula to other cells, the reference doesn’t change.
Sum-of-the-years’-digits Method
The sum-of-the-years’-digits method of depreciation is a way to calculate how much an asset decreases in value over time. It is different from the straight-line method because it considers that an asset loses more value in the first years of its life and less value in later years. This method is also called “accelerated depreciation” because it allows for bigger deductions in the early years and smaller deductions in the later years.
To use the sum-of-the-years’-digits method in Excel, we will use the SYD function. The syntax of the function is as follows:
=SYD(cost, salvage, life, period)
Where:
- cost is the initial cost of the asset
- salvage is the value of the asset at the end of its useful life
- life is the total number of periods over which the asset will be depreciated
- period is the specific period for which you want to calculate the depreciation
Using the example, we will use the formula =SYD($B$2,$B$3,$B$4,A7), and copy and paste it to the other years.
Note: We are locking the reference of the cells with the dollar sign ($), e.g., $B$2, so that when we copy the formula to other cells, the reference doesn’t change.
Declining Balance
The declining balance method is a depreciation method that uses a fixed percentage. The percentage is calculated using this formula: . Since the amount of the depreciation is based on the percentage, in the earlier years, the cost is higher, and in later years, the cost is lower, resulting in accelerated depreciation compared to the straight-line method.
To use the declining balance method in Excel, we will use the DB formula. The syntax of the function is as follows:
=DB(cost, salvage, life, period)
- cost is the initial cost of the asset
- salvage is the value of the asset at the end of its useful life
- life is the total number of periods over which the asset will be depreciated
- period is the specific or current period for which you want to calculate the depreciation
Using the example, we will use the formula =DB($B$2,$B$3,$B$4,A7), and copy and paste it to the other years.
Note: We are locking the reference of the cells with the dollar sign ($), e.g., $B$2, so that when we copy the formula to other cells, the reference doesn’t change.
Double-Declining Balance Method
The double declining balance method (DDB) of depreciation is an accelerated depreciation method that is similar to the declining balance method. The DDB method uses a fixed percentage, like the declining balance method, but this percentage is double the rate used in the declining balance method.
To use the double declining balance method in Excel, we will use the DDB formula. The syntax of the function is as follows:
=DDB(cost, salvage, life, period, [factor])
- cost is the initial cost of the asset
- salvage is the value of the asset at the end of its useful life
- life is the total number of periods over which the asset will be depreciated
- period is the specific period for which you want to calculate the depreciation
- factor is the fixed rate of depreciation, expressed as a decimal [optional]
Using the example, we will use the formula =DDB($B$2,$B$3,$B$4,A7), and copy and paste it to the other years.
Note: We are locking the reference of the cells with the dollar sign ($), e.g., $B$2, so that when we copy the formula to other cells, the reference doesn’t change.
Variable Declining Balance Method
The variable declining balance method (VDB) is a method of calculating the decline in value of an asset over time that combines elements of both the declining balance method and the straight-line method.
To use the variable declining balance method in Excel, we will use the VDB formula. The syntax of the function is as follows:
=VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
- cost is the initial cost of the asset
- salvage is the value of the asset at the end of its useful life
- life is the total number of periods over which the asset will be depreciated
- start_period is the starting period for which you want to calculate the depreciation
- end_period is the ending period for which you want to calculate the depreciation
- factor is the fixed rate of depreciation. [optional]
- no_switch is a logical value specifying whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation. [optional]
Using the example, we will use the formula =VDB($B$2,$B$3,$B$4,A7-1,A7), and copy and paste it to the other years.
Note: We are locking the reference of the cells with the dollar sign ($), e.g., $B$2, so that when we copy the formula to other cells, the reference doesn’t change.
