Calculate straight line depreciation in Excel with the SLN function

Excel Navigator
Apr 27
2 min read

The straight line method is one of the most common ways to depreciate an asset.

Excel has an inbuilt function to calculate deprecation this way, SLN.

Download File

If you would like to follow along download the attachment below.

What is Straight Line Depreciation?

With the straight line depreciation method an asset loses an equal amount of value each period (could be monthly or yearly) over its useful life.

The total value of the asset that is depreciated is the cost of the asset minus the salvage value, this is what you would expect to receive for the asset at the end of its useful life e.g. if it were sold.

So if you:

Bought a machine worth £5,000
Expected to receive £1,000 for it at the end of its useful life
Depreciated it over 4 years

Then the annual depreciation would be £1,000.

The calculation would look like:

Straight Line Depreciation = (£5,000-£1,000)/4

The SLN Function in Excel

Excel’s SLN function helps you calculate straight-line depreciation by applying the same logic:

It’s syntax is:

=SLN(cost, salvage, life)

cost: The asset’s initial purchase price.
salvage: The value of the asset at the end of its useful life
life: The number of periods the asset will be used, can be months of years depending on the output you want to see.

Using SLN to calculate annual depreciation

If you purchase a vehicle with the following assumptions:

It costs £10,000
Expect to sell it at the end of five years for £2,000
Expect to use it for 5 years

You can use SLN to calculate the annual depreciation you will pay for the vehicle by entering the assumptions into cells in Excel and refer to them in the function.

This will return 1,600, which is what will need to be paid each year for five years to fully depreciate the vehicle.

A breakdown of how SLN calculated the deprecation is:

10000-2000 = 8000
8000/5 = 1600

Using SLN to calculate Monthly depreciation

As SLN only requires you to enter the number of periods for the assets useful life you can enter the number of months instead of years to know how much you will pay each month to fully depreciate the asset.

Five years is made up of sixty months so the useful life can be changed to this amount to see the monthly, rather than annual payments.

Impact of adding zero periods for depreciation

There needs to be a value of 1 or higher entered in the life parameter, if this is zero or blank the function will return the ‘#DIV/0!’ error.

Conclusion

The SLN function can be used to calculate straight line depreciation by subtracting the salvage price from the cost and then dividing it by the useful life.

A value of one or more needs to be added to the useful life, otherwise the function will return the ‘#DIV/0!’ error.