top of page

Calculate straight line depreciation in Excel with the SLN function

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:


  1. 10000-2000 = 8000

  2. 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.



Comments


bottom of page