Google Sheets SLN Function in Straight Line Depreciation Calculation

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;

  1. The type of the asset whether it’s a car, heavy equipment, truck, etc.
  2. Legal requirements.
  3. 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.

Google Sheets SLN Function

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.

Data for SLN Chart in Google Sheets

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

  1. Select the range E3:H7.
  2. Click on Insert menu Chart command (you can probably use the Alt+I+H keyboard shortcut in Windows).
  3. 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.
Settings in SLN Chart Editor

This will create a straight line depreciation chart in Google Sheets as below.

Google Sheets SLN Chart

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!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.