Want to find the straight line depreciation of your car or any other asset for a single period? Don’t look further. Use Google Sheets SLN function to easily calculate the depreciation amount using the straight-line method.
Due to many reasons, the value of an asset decreases over the year. The reasons can include wear and tear, market conditions, the introduction of new technologies, etc. Let’s leave that aside.
The fact is that it’s a must to know the depreciation of your asset to sell it later as well as for cost allocation over the period of its use.
Straight Line depreciation aka SLN is the most popular method to find the depreciation in the value of an asset over the year.
There are different methods in practice to find the depreciation and that depends on different factors. They are;
- The type of the asset whether it’s a car, heavy equipment, truck, etc.
- Legal requirements.
- The present conditions of business.
Like different methods there are different functions too in Google Sheets for finding depreciation.
The other functions for finding depreciation in Google Sheets are SYD (sum of years digits method), DDB (double-declining balance method), DB (declining balance method), VDB (variable declining balance) and AMORLINC (depreciation for an accounting period or prorated depreciation).
So decide which method you want to follow. In this post, I am going to elaborate on the use of SLN function in Google Sheets that means the straight line depreciation.
Must Check: Google Sheets Functions Guide.
Finding Straight Line Depreciation Using Google Sheets SLN Function
In Straight Line depreciation (please refer to the chart at the bottom), which is most popular and simple to calculate, a fixed amount is reduced over each period until it reaches its salvage value.
Salvage value? Go through the syntax and its arguments below to understand it.
Syntax of the SLN Function in Docs Sheets
Syntax:
SLN(cost, salvage, life)
Arguments:
cost – The initial cost of the property/asset.
salvage – Also known as residual value or scrap value which is the value of the asset at the end of depreciation or you can say its useful life.
life – The life period of the property/asset, That means the number of periods over which the asset is depreciated.
Example to Straight Line Depreciation Using the SLN Function
In the below example (using mock-up data) I am going to depreciate my car bought under my business name.
cost of car: $29,195.00
salvage: 0
life: 5 years
Here is the SLN formula in Google Sheets to depreciate the above car.
=sln(29195,0,5)
This returns $5,839.00 as the depreciation amount for a single period that means it would write off 20% of my business car’s cost, basis each year.
Manual Calculation of SLN in Sheets
Take into account the above values (please refer to the screenshot) in the manual SLN calculation in Sheets. The formula would be;
=(B2-B3)/B4
That means;
Annual Depreciation Using SLN = (initial cost of the asset – salvage value)/useful life of the asset. So simple, right?
Plot a Straight Line Depreciation Chart in Sheets
Here you can see how to create an SLN chart in Google Sheets.
To create a straight line depreciation chart in Google Sheets, first format the data as below to use in the chart as the source.
I have already explained how to find straight-line depreciation using Google Sheets SLN function. That values and formula occupy column A and B.
Now to the table in the range E2:H7. Just go thru’ that and you can understand how that values are derived.
I have used the below formulas in that table.
Cell F3:
=B2
In cell F4 I have entered the following formula =H3
and dragged down until cell F7.
In the range G3:G7 simply fill the SLN value. Alternatively, you can enter =$B$5
in cell G3 and copy down.
Here is the formula to use in cell H3.
=F3-G3
Don’t forget to copy this formula down. Now our data is ready for creating the straight line depreciation chart in Google Sheets.
Steps to Plot an SLN Chart
- Select the range E3:H7.
- Click on Insert menu Chart command (you can probably use the Alt+I+H keyboard shortcut in Windows).
- Now let’s make some changes to the existing values in the chart editor like chart type, x-axis value, and series. You must only leave the series “Closing value”. See the screenshot below to make the said changes.
This will create a straight line depreciation chart in Google Sheets as below.
In the above chart, I have used the series “Closing Value” (column H). You can refer the just above chart editor settings. If you use “Opening Value” (column F) instead, you must modify the table as below.
Enter the years “2024-2025” in cell E8 and copy the formula in cell F7 to F8. Make sure that these changes are included in the “Data range” in the chart editor. I mean it should be E2:F8 instead of E2:E7,H2:H7.
That’s all about SLN chart and the use of Google Sheets SLN function in straight line depreciation calculation. Enjoy!