How to Calculate Straight Line Depreciation Using a 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”.

To distribute depreciation over a specific period of time, use a formula containing:

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, known as salvage value or Residual Value.
Useful Life of Asset: The timescale during which the asset is used. The asset loses all value when its useful life is completed.

This is the sample dataset: “Depreciation Schedule for a Car”.

Method 1- Using an Arithmetic Formula

Steps:

Enter the following formula in D11.

=($D$5-$D$6)/7

Here, D5 and D6 represent the “Initial Cost of the Asset” and “Salvage Value”.

As the useful life is 7 years, the subtracted value is divided by 7.

Press ENTER to get the straight line depreciation value for 2017.

The dollar sign ($) is used to lock D5 and D6, as these values will be used to calculate the value for 2018 to 2023. This is called an absolute reference, and you can do that by pressing F4 after selecting a cell.

Drag down the Fill Handle to see the result in the rest of the cells.

To calculate the Accumulated Depreciation, enter the following formula in E11.

=E10+D11

Press ENTER.

Drag down the Fill Handle to see the result in the rest of the cells.

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 ENTER.

Drag down the Fill Handle to see the result in the rest of the cells.

Method 2 – Using the SLN Function

Use the SLN function.

Steps:

Call the SLN function by entering an Equal sign (=) and the function.
Enter the initial cost of asset, salvage value, and useful life in D5, D6, and D7.
Press ENTER.

Drag down the Fill Handle to see the result in the rest of the cells.

To calculate the Accumulated Depreciation, enter the following formula in E11

=E10+D11

Press ENTER.

Drag down the Fill Handle to see the result in the rest of the cells.

To calculate the Depreciated Value, enter the formula:

=$F$10-E11

Press ENTER.

Drag down the Fill Handle to see the result in the rest of the cells.

Method 3 – Calculating the Straight Line Depreciation for Multiple Investments

Steps:

Enter the following formula in D8.

=B8*$C$5

Press ENTER.

Drag down the Fill Handle to see the result in the rest of the cells.

To calculate the accumulated depreciation for multiple investments in each year, use the OFFSET function to sum the annual depreciation in the last 6 years.

Enter the following function:

=SUM(OFFSET(D8,,,-C8))

Formula Breakdown

The arguments used are: reference, rows, columns, height, and width. The first three arguments are required, the other two are optional. However, to sum the numbers in the Year Counter column, the rows, columns, and height arguments are skipped by entering the comma symbol (,). A negative sign is assigned to C8. The negative notation adds the reference cell to the number of cells above.

Note: The width argument was used to calculate the sum vertically. To do it horizontally, input the height argument.