Let’s talk depreciation. Did you know that in Excel we have a number of formulas available to us to assist with calculating the depreciation of assets?

This is one perhaps for the accountants among you but is a great example of how functional Excel can be.

As a very simplistic explanation, when a business purchases an asset, such as a car, it will expect to hold this asset for a number of years. Therefore, rather than absorbing the full cost of the car immediately in the accounts, the car will be given an estimated useful life. The business will then during each accounting period take a proportion of the cost of the asset to its accounts over the course of that useful life, this is what we call depreciation.

There are a number of methods of depreciation and most of which are catered for by Excel. We will focus on the two, probably most common methods in the UK.

Straight line depreciation

This method is simply an equal portion every period over the useful life of the asset until the asset value reaches nil or a predetermined residual (or salvage) value.

The formula function is =SLN(cost,salvage,life)

In this instance, all you need to do is input the original cost of purchase, the residual or salvage value and the life of the asset. The life of the asset is best represented in the number of accounting periods the assets useful life represents.

Reducing or Declining balance depreciation

The formula function is =DB(cost,salvage,life,period,[month])

In this instance again all you need to do is input the original cost of purchase, the salvage value, the life of the asset and also, this time, the current period.

The current period is the same format as the format used for asset life. Thus, if you set an asset life of 5 years the current period will be anywhere between 1-5 years inclusive, to return the value of depreciation for the required year. Of course, you can use months for both and therefore life becomes 60 (12months x 5 years) and then you’re able to select any period in that range.

Additionally, if you use years as the base there is an optional [months] element to this function. This allows you to set how many months are left in the first year from purchase date and will reduce the resultant value by the months omitted. For example, if we use months value of 9, the calculation will be 9/12ths for the first year only. This is great for when an asset is purchased part way through a year, as is more often than not the case.

NB due to the calculation used in this formula the salvage value must be greater than zero for it to work correctly.

You can get the download file here to have a try yourself and see the formulas in action.

Download free now

Other depreciation methods

As mentioned earlier there are other methods of depreciation that Excel provides formulas for. Feel free to take a look at the following yourself:

  • SYD – Sum of Year’s Digits
  • DDB – Double Declining Balance
  • VDB – Variable Declining Balance

That’s all for this blog I hope some of you are able to make use of these formulas and in particular the accountants among you.