How to Use SLM Method of Depreciation Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

Depreciation is a very common term in the case of assets. In general, it is the decline of an asset over a certain period of time. There are many methods to calculate any asset’s depreciation. In this article, I will describe the SLM method of depreciation formula in Excel.

Actually, Excel has many wonderful functions related to depreciation. Here, for conducting the session, I will use Microsoft 365 version.


What Is SLM Depreciation?

SLM depreciation denotes Straight Line Method depreciation. Basically, SLM depreciation means a fixed amount of deduction from the assets after each period. Here, the period should be yearly based.

The basic 3 terms of SLM depreciation are Initial Cost, Salvage value, and Useful Life. Firstly, the Initial cost is the total cost for an asset. Secondly, the Salvage value is the value of the asset at the end of the depreciation period. Thirdly, the lifetime of that asset is called the Useful Life.


How to Use SLM Method of Depreciation Formula in Excel: 2 Methods

Here, I will demonstrate 2 suitable methods with detailed steps about the Straight Line Method(SLM) of depreciation formula in Excel. For your better understanding, I am going to use the following dataset. Which contains some information like initial cost, salvage value, and useful life. The dataset is given below.

Dataset for SLM Method of Depreciation Formula in Excel


1. Generic Formula to Find SLM Depreciation in Excel

Here, you can use a generic formula to find out the straight-line method (SLM) depreciation in Excel. Basically, the traditional and simplest formula is:

SLM Depreciation = (Initial Cost – Salvage Value)/Useful Life

Now, using the above formula, I will calculate the straight-line method (SLM) depreciation in Excel. So, let’s see the steps.

Steps:

  • Firstly, you must select a new cell E5 where you want to keep the result.
  • Secondly, you should use the formula given below in the E5 cell.
=(C4-C5)/C6

Where C4 is the initial cost, C5 is the salvage value and C6 is useful life.

Generic Formula to Find SLM Method of Depreciation in Excel

  • Then, press ENTER to get the SLM depreciation.

Now, I will find the ending value after each period of the asset.

  • So, prepared a table like below.
  • Then, write the SLM depreciation in the column named Depreciation.
=$E$5

 Here, you must use the Dollar sign ($) as the type of depreciation is SLM depreciation.

  • Subsequently, enter the following cell reference in the E9 cell.
=D9

In this cumulative depreciation column, I will add all the depreciation of the previous period.

  • Then, use the corresponding formula given below to find the ending value after 1 year.
=C9-E9
  • After that, press ENTER.

Now, I will the ending value after 2 years.

  • Firstly, write the following formula in the C10 cell.
=F9

Here, the ending value of previous year will be the starting value of present year.

  • Secondly, press ENTER.

  • Then, enter the SLM depreciation in the D10 cell and press ENTER.
=$E$5

  • Then, use the following formula in the E10 cell to find the cumulative depreciation.
=E9+D10
  • Subsequently, press ENTER.

  • Then, write down the corresponding formula in the F10 cell.
=C10-D10
  • Subsequently, press ENTER.

Finding End Value Using SLM Method of Depreciation in Excel

  • Now, select the cells C10:F10.
  • Then, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells C11:F13.

Lastly, you will get per-year ending values.

Here, if you notice, then you will see that the last cumulative depreciation is actually the difference between initial cost and salvage value. Furthermore, the last ending value of the depreciation period is the salvage value.

Finding SLM Method of Depreciation by Generic Formula in Excel

Read More: How to Calculate Accumulated Depreciation in Excel


2. Employing Formula of SLN Function to Get Depreciation in Excel

Basically, Excel has its own function to find out the straight-line method (SLM) depreciation which is the SLN function. Now, I’ll show you how to use this SLN function. So, let’s see the steps.

Steps:

  • Firstly, you must select a new cell E5 where you want to keep the SLM depreciation.
  • Secondly, you should use the formula given below in the E5 cell.
=SLN(C4,C5,C6)

Where the initial cost is C4, the salvage value is C5, and useful life is C6.

  • Thirdly, press ENTER.

Use of SLN Function as SLM Depreciation Formula in Excel

  • Now, to find the details of each period prepared a table like below.
  • Then, write the SLM depreciation in the column named Depreciation.
=$E$5

 Here, you must use the Dollar sign ($) as the type of depreciation is SLM depreciation.

  • Consequently, enter the following cell reference in the E9 cell.
=D9

In this cumulative depreciation column, I will add all the depreciation of the previous period. As this is the 1st period so I have to use only the D9 cell value.

  • Then, use the corresponding formula given below to find the ending value after 1 year.
=C9-E9
  • After every formula, you must press ENTER.

  • Then, do the same for the 2nd year. Here, just you have to add the previous cumulative depreciation.
  • Lastly, after completing 2nd row, you may use the Fill Handle icon for the rest of the cells.

Calculating SLM Depreciation by Using SLN Formula in Excel

Read More: How to Use Formula to Calculate Car Depreciation in Excel


How to Use WDV Method of Depreciation Formula in Excel

Actually, for calculating the depreciation, you can use two methods. They are Straight Line Method (SLM) depreciation and Written Down Value (WDV) depreciation. Basically, you may apply this WDV method for a constant rate of depreciation to the net value in respect of calculating assets each year. This formula is also named as Diminishing Balance Method or Declining Balance Method.

So, in this section, I will explain how to calculate this WDV method of depreciation in Excel. To do so, I will use the DB function. Now, let’s see the steps.

Steps:

  • First, in cell D9 write the formula.
=DB($C$4,$C$5,$C$6,B9)

Where the initial cost is C4, the salvage value is C5, useful life is C6, and the period is B9.

  • Then, press ENTER.

WDV Method of Depreciation Formula in Excel

  • Then, follow the steps of method-1 and you will get the depreciation details.

If you notice, then you will get the difference between SLM and WDV methods. Actually, the depreciation is not fixed here. Instead of being fixed, the depreciation is decreasing at a fixed rate.

Read More: How to Apply Declining Balance Depreciation Formula in Excel


Practice Section

Now you can practice the explained methods by yourself.

Practice Section to Find SLM Method of Depreciation in Excel


Download Practice Workbook

You can download the Practice Workbook that we used to prepare this article. Furthermore, you can change or modify data & find new outputs accordingly.


Conclusion

I hope you found this article helpful. Here, I have explained 2 suitable methods of the SLM Depreciation formula in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has been working with the ExcelDemy project for 1.5 years. She has written over 97+ articles for ExcelDemy. Currently, she is working as the Excel and VBA Content Developer, creates contents, solves user problems, writes articles etc. Her work and learning interest varies from Microsoft Office Suites, and Excel to Data Analysis, and developing Excel Applications with VBA codes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo