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)/7`

Here, 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-E11`

Here **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))`

**Formula Explanation:**

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**