Excel will continue to be a vital tool for businesses for a variety of tasks and uses, from IT projects to team outings. For instance, you can calculate your product depreciation either manually or can do the job by using the Excel shortcut key. Guess what today’s topic is? That’s right! How to calculate the straight line depreciation formula in Excel
What Is Straight Line Depreciation?
Depreciation is an accounting technique for spreading out the expense over the span of its useful life. In financial accounting, you need to deduct the consumed utility value from its “Purchased Value”.
From an economic standpoint, asset value is steadily decreasing and has ended up with salvage value after operating in a specific useful year. Now you might get confused about salvage value. We will learn about it in the next section. But for your understanding, we can say that this method assumes the assets are functioning and offer the same amount of benefit to the organization over their useful lives. As a result, in the case of straight-line depreciation, the depreciation value is always constant.
As we need to distribute its depreciation over a specific period of time, So we can calculate it as per the following formula-
where,
- Cost of Asset: Actual cost of Acquisition of an Asset, after considering all the direct expenses.
- Salvage Value: The expected realizable worth of an asset at the end of its useful life is known as salvage value. Additionally known as Residual Value.
- Useful Life of Asset: The timescale during in which the asset is used. The asset loses all value when its useful life is completed.
How to Calculate the Straight Line Depreciation Using Formula in Excel: 3 Methods
Let’s assume we have a dataset, namely “Depreciation Schedule for a Car”. You can use any dataset suitable for you.
Here, we have used the Microsoft 365 version; you may use any other version according to your convenience.
Method 1: Using the Arithmetic Formula
In this method, we will calculate the depreciation of the given dataset according to the by-book formula. Later, we will also discuss Excel’s dedication function to do that.
📌 Steps:
- Type the following formula in cell D11.
=($D$5-$D$6)/7Here, the D5 and D6 cells represent the “Initial Cost of the Asset” and “Salvage Value” respectively.
As our useful life is 7 years, we divide the subtracted value by 7.
- Press ENTER to get the straight line depreciation value for the year 2017.
It should be noted that the dollar sign ($) is used to lock the D5 and D6 cells in this case, as those cell values will be required to calculate the value for the years 2018 to 2023 as well. This is called an absolute reference, and you can do that simply by pressing the F4 key after selecting a cell.
- To get the other values, drag the Fill Handle tool from D11 to D17. The relevant results will appear afterwards.
- Now to calculate Accumulated Depreciation, enter the following formula in cell E11.
=E10+D11- Then, hit ENTER.
- Now drag down the Fill Handle tool from E11 to E17 as we did before.
- The last part of this method is to calculate Depreciated Asset Value. To do so, simply enter the following formula in cell F11.
=$F$10-E11Here F10 and E11 cells stand for “Depreciated Asset Value” and “Accumulated Depreciation” respectively.
- Press the ENTER button afterwards.
- Drag down the Fill Handle tool from F11 to F17 to get the following output.
Read More: How to Use Formula to Calculate Car Depreciation in Excel
Method 2: Incorporating the SLN Function
As you have already understood, calculating the straight line depreciation value seems tiresome as you need to input the value according to the formula. However, Excel has incorporated the SLN function to ease your calculation process. Basically, the function returns the straight line depreciation of an asset for one period. Now, we’ll apply the function in the case of our dataset.
📌 Steps:
- First, call the SLN function by entering an Equal sign (=) and then type the function. Next, input the initial cost of asset, salvage value, and useful life. To do so, select D5, D6, and D7 cells respectively.
- Type ENTER, and you will get the depreciation of the asset for 2017.
- Now drag down the Fill Handle tool from D11 to D17.
- To calculate Accumulated Depreciation, Enter the following formula in the E11
=E10+D11- Press ENTER.
- Drag down the Fill Handle tool from D11 to D17.
- To calculate Depreciated Value, Enter the formula in cell.
=$F$10-E11- Press ENTER.
- To get the other value, drag down the Fill Handle tool.
Method 3: Calculating Straight Line Depreciation for Multiple Investments
What if you want to calculate straight line depreciation for multiple investments? You can take the following dataset and adopt the method that we are going to describe below.
Thinking about the dataset at first sight might confuse you, as this makes no sense when you look at the Year Counter column. Here, we invested $100 more compared to the previous year. But as the useful life of each investment is 6 years, the asset value will last for 6 years, and there is a 10 by 10 matrix we have to generate to describe the physics behind the calculation. To solve it manually, you will count the years as 1, 2, 3, 4,… And put the value 6 until the rest, denoting that it will only calculate the previous number of rows in Column D as the Year counter suggests. Sounds confusing? No problem at all! We will add another image by considering a different value for your better understanding. As for now, let’s begin.
📌 Steps:
- Type the following formula in D8 cell
=B8*$C$5- Press ENTER.
- Next, drag the Fill Handle tool to fill the column.
Now we have to calculate the accumulated depreciation for multiple investments in each year. Here, we use the OFFSET function to sum up the last 6 years’ annual depreciation.
- First, Enter the following function
=SUM(OFFSET(D8,,,-C8))You might wonder why we used two arguments despite the fact that there are five arguments. The arguments are: reference, rows, columns, height, and width. The first three arguments are required, while the remaining two are optional. However, we want to sum the numbers as per what the Year Counter column says. That’s why we skip the rows, columns, and height arguments by entering the comma symbol (,). Following that, we assigned a negative sign to the C8 cell. As Year Counter states, negative notation means it will add the reference cell to the number of cells above.
Note: It is worth mentioning that we utilized the width argument as we calculated the sum vertically. If you want to do it for horizontal cells, you have to input the value of the height argument.
- Press ENTER.
- Drag the Fill Handle tool to get the rest of the values.
- So the output for six useful years is as described below.
- However, if we count 7 years rather than 6, the Year Counter and its corresponding Accumulated Depreciation values will look like this.
Read More: Calculate Sum of Years Digits Depreciation with Formula in Excel
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.
Download Practice Workbook
You can download and practice the dataset that we have used to prepare this article.
Conclusion
In this article, we have shown some useful methods to calculate straight-line depreciation calculation in Excel. However, If you have any queries, feel free to comment below, and we will get back to you soon.
Related Articles
- How to Use WDV Method of Depreciation Formula in Excel
- How to Apply Declining Balance Depreciation 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 Double Declining Depreciation in Excel
<< Go Back to Depreciation Formula In Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
