How to Use SLN Function in Excel (3 Examples)

The SLN function, an Excel Financial function, calculates the depreciation of an asset for one time period using the straight line method for depreciation calculation. During each period of an asset’s lifetime, the straight line technique simply subtracts a specific amount from its value. The straight line depreciation for one period is determined using the equation;

Straight line depreciation= (cost-salvage)/life.

Excel SLN function applies this equation to calculate the depreciation of an asset.

The above image gives a general overview of the SLN function. Throughout the article, we will know more details and usages of this function.

Introduction to The SLN Function

❑ Objective

Excel SLN function returns the straight-line depreciation of an asset for one period.

❑ Syntax

SLN(cost, salvage, life)

❑ Argument Explanation

❑ Output

The SLN function returns one period straight line depreciation of an asset.

❑ Version

This function has been available in Excel since Excel 2000. So, any newer version of Excel 2000 has the SLN function.

How to Use The SLN Function in Excel: 3 Examples

Now let’s see some examples which show different applications of the SLN function.

1. Calculate Straight Line Depreciation using The SLN Function

The basic function of the SLN function is to calculate straight line depreciation of an asset. Suppose in our dataset we have the initial cost (the beginning price of the asset), salvage (value after full depreciation), and life (number of depreciation periods) of the asset. Now, we will calculate per period depreciation of the asset using the SLN function.

➤ Type the following formula,

Here, cell C4 contains cost, cell C5 contains salvage and cell C6 contains life of the asset. The formula will return the straight line depreciation of the asset using the equation; Straight line depreciation = (cost-salvage)/life.

➤ After that, press ENTER.

As a result, you will get the straight line depreciation of the asset.

2. Excel SLN Function in VBA

We can also use the SLN function to create a Microsoft Visual Basic Applications (VBA) macro in order to calculate the depreciation of an asset. First,

➤ Press ALT+F11 to open the VBA window.

➤ Right click on the sheet name from the left panel of the VBA window and go to Insert > Module.

As a result, a Module (Code) window will be opened.

➤ Insert the following code in this window

Sub Depreciation()
Range("C7").Value = SLN(Range("C4"), Range("C5"), Range("C6"))
End Sub

The code will return the depreciation in cell C7 taking the cost from cell C4, salvage from cell C5 and life from cell C6.

➤ After that, close the VBA window.

Now,

➤ Go to the View tab and Select Macro.

As a result, the Macro window will be opened.

➤ Select Depreciation from the box, Macro name, and click on Run.

Finally, we will get the depreciation of the asset in cell C7.

3. Calculate Depreciated Value for Every Year

In accounting, depreciation is used to calculate the depreciated value of an asset every year. In this example, we will see how we can use the SLN function to calculate the depreciated value in every year of the lifetime of an asset.

Suppose we have the following dataset where the initial cost, salvage, and total life of an asset are given. Now, we will calculate the depreciated value of the asset in every year of its lifetime.

At the beginning (year 0) the asset won’t be depreciated and it will have its full value. So, for year 0, the depreciated value will be equal to the initial cost of the asset.

3.1. Depreciation

For year 1,

we have calculated the depreciation using the following formula,

Here, the formula will return the straight line depreciation of the asset. We have locked the reference cells because the initial cost, salvage, and life of the asset from cells C4, C5, C6 will be the same in each year’s calculations.

3.2. Accumulated Depreciation

Accumulated depreciation is the total depreciation of the asset from the beginning to the current year. So, if we simply add up the accumulated depreciation of the previous year (cell D9) and depreciation of the current year (cell C10), we will get the accumulated depreciation of the current year.

3.3. Depreciated Value

The depreciated value of the asset in a year will be the value found after subtracting the accumulated depreciation of the year from the initial cost of the asset. So if we subtract the accumulated depreciation of a year (cell D10) from the initial cost (cell C4), we will get the depreciated value of the asset (cell E10) in that year (year 1). Here, we have locked the cell C4 as the initial cost will remain the same in every year.

At last, we have simply dragged the cells C10, D10 and E10 to the end of our dataset. As a result, we have got the depreciation, accumulated depreciation, and depreciated value of the asset in every year of its lifetime.

In this similar manner, you can calculate the depreciated value of any asset in every year of its life, if the initial cost, salvage, and life are given.

💡 Things to Remember When Using SLN Function

📌If the life argument is equal to 0, the function will return #DIV/0! error.

📌 If any of the supplied arguments are non-numeric, the function will show #VALUE! error.

📂 Download Practice Workbook

Conclusion

I hope now you have a detailed understanding of Excel SLN function and of its applications. If you have any confusion, please leave a comment.

<< Go Back to Excel Functions | Learn Excel