Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will show you how to calculate depreciation in Excel.
Introduction to Depreciation
Depreciation is a measure of how much the value of an asset decreases over time. A business or individual may buy an asset, for example, a vehicle, a computer, or another piece of equipment, and then work out the value of that asset over a period of time using depreciation, for accurate reflections on accounting budgets.
How to Calculate Depreciation in Excel: 5 Easy Methods
This is the dataset for today’s article. We have the initial cost, salvage value, and useful life of an instrument. I will use this data to calculate depreciation in Excel.
1. Apply SLN Function to Calculate Depreciation
The first method is to apply the SLN function. This determines the single-line depreciation. To do so,
Steps:
- Go to C7 and write down the following formula
=SLN(C4,C5,C6)
- Then, press ENTER to get the output.
2. Use SYD Function to Calculate Depreciation
Assets do not depreciate linearly over time. The number of depreciation changes every year. To consider this fact, Excel has the built-in SYD function to calculate depreciation.
Steps:
- Go to C8 and write down the following formula
=SYD(C4,C5,C6,C7)
- Then, press ENTER to get the output.
Explanation:
- The amount of depreciation for the 3rd year is $5,833.33. The amount of depreciation will change if you change the per argument.
- The accumulated depreciation after 8 years will be $(50,000-15,000) or $35,000.
- Using the formula, you will get the amount of depreciation from the 1st to 8th year. The values are $7,777.78 , $6,805.56 , $5,833.33 , $4,861.11, $3,888.89, $2,916.67, $1,944.44, $972.22 respectively. The sum of these values will be $35,000.
3. Apply DDB Function to Calculate Depreciation
The next method is the use of the DDB function. This function will calculate depreciation using the double-declining method.
Steps:
- First of all, go to C8 and write down the following formula.
=DDB(C4,C5,C6,C7)
- Then, press ENTER to get the output.
4. Use the DB Function to Calculate Depreciation
The next method is the use of the DB function. This function will calculate the depreciation using the declining balance method.
Steps:
- First of all, go to C8 and write the following formula
=DB(C4,C5,C6,C7,C8)
- Then, press ENTER to get the output.
Note:
The month argument indicates the number of the month in 1st year. In this case, it is 10. That means, Excel will consider 10 months for the 1st year while calculating depreciation.
5. Apply VDB Function to Calculate Depreciation
Now, I will explain another method to calculate depreciation. This time, I will use the VDB function to do so. This function will determine the depreciation of an instrument for a specific time span. In this case, the time span will be 48th to 52nd month.
Steps:
- Go to C10 and write down the formula
=VDB(C4,C5,C6,C7,C8,C9,TRUE)
- Then, press ENTER to get the output.
Explanation:
- Since we are calculating the depreciation from the 48th to the 52nd month, the useful life is converted to the month.
- The Factor defines that the rate at which the balance declines is 5.
- The No_switch is used to determine the nature of the depreciation method. If the value is TRUE, Excel will not switch to a straight-line depreciation method.
Things to Remember
- Assets do not depreciate linearly.
- If not mentioned, Excel will consider Factor 2.
Download Practice Workbook
Download this workbook and practice while going through the article.
Conclusion
In this article, I have explained how to calculate depreciation in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.
Depreciation Formula In Excel: Knowledge Hub
- How to Calculate Straight Line Depreciation Using Formula in Excel
- How to Use WDV Method of Depreciation Formula in Excel
- How to Apply Declining Balance Depreciation Formula in Excel
- Calculate Sum of Years Digits Depreciation with Formula in Excel
- Units of Production Depreciation Method with Formula in Excel
- How to Use SLM Method of Depreciation Formula in Excel
- How to Use MACRS Depreciation Formula in Excel
- How to Calculate Accumulated Depreciation in Excel
- How to Use Formula to Calculate Car Depreciation in Excel
- How to Calculate Double Declining Depreciation in Excel
<< Go Back to Excel Formulas for Finance|Excel for Finance|Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
