# Straight-line Depreciation

• Tagged with:
• Design,
• Excel,
• MIN,
• OFFSET,
• Straight-line depreciation,

## In reviewing a few models today, I was reminded how rare it is to find straight-line depreciation modelled in a concise, neat and accurate way. Although straight-line depreciation is notionally the simplest depreciation method, it is possibly the most difficult to model — as you’ll see below.

Firstly, let’s review what straight-line depreciation is. In its most basic form, straight-line depreciation expenses the original cost of an asset in equal annual amounts over a given number of years. For example, if an asset is purchased for \$1,000 and you wish to depreciate it using the straight-line method over 5 years, then the depreciation expense will be \$200 for each of the 5 years. Of course, you can introduce complexities such as depreciating less than a full year’s charge in the year of purchase, or taking into account a salvage value, but to illustrate this concept we don’t need to add these complexities.

Calculating the depreciation schedule for one asset or one purchase is easy.♦ The complexity arises when you have numerous purchases over numerous periods, and the schedules overlap. For example, assume you purchase assets totalling \$1,000 in year 1, \$500 in year 2 and \$800 in year 3. And you now want to calculate the total annual depreciation expense in one schedule rather than in three separate schedules. The most common approach to calculating the total depreciation is to do this using a 2-dimensional matrix – as follows: Although this schedule is nicely laid out and the calculations are accurate, this approach suffers from a number of problems. Firstly, it takes up a lot of space. You need a separate row for each purchase period and even more rows to allow for the depreciation tail. It’s bad enough that you have that many columns, but now you have the same number of rows too. Not only does this hinder navigation, but it eats up memory and slows down calculation speed. Also, just imagine that you have various asset classes, with each one having a (potentially) different depreciation rate. Now you need a separate matrix for each of these asset classes. Things can get out of control really quickly. Secondly, and more importantly, complexity leads to errors. While slow speed and visual complexity will make it hard to work with a model; errors will doom it.

Fortunately, there is a better, simpler, safer way. The trick (if you can call it that) is to use the OFFSET formula and model the total depreciation calculation in one line. Instead of going through what OFFSET does and then showing you how it solves this problem, I’ll just show you the solution. Essentially, the OFFSET formula is saying “add up the last X years of purchases, starting from this year”. The MIN function is used to ensure that the OFFSET never “goes left” of column A – as this would result in a #REF error. The end result is a very concise, accurate and efficient implementation of a straight-line depreciation schedule.

Hilariously enough, Excel actually provides a function SLN(cost,salvage,life) which returns the straight-line depreciation of an asset for one period. As you would expect, the underlying equation is: (cost-salvage)/life. Did they really need to provide a function for this?

## 14 Comments so far Join the Discussion

1. ### Nick Crawley

24th August 2009 at about 5pm Hi Darren,

Good blog, keep up the good work!

I think this posting should make it clear that the purchase date is at the beginning of the period, is this what you intended. I would have thought the depreciation starts in the first period after purchase if not.

Overall, although the SUM(OFFSET()) approach is neat and it saves a lot of lines (and its our preferred way) your readers should probably be made aware that its:

1) Not very intuitive
2) Hard to audit (F2 etc)

I slight refinement would be to split out the counter into at least one seperate line.

Nick

2. ### Frank

3rd June 2010 at about 11am How would we apply the half year rule to this formula?
Thanks
frank

3. ### Frank

4th June 2010 at about 3am Yes that is exactly what I meant and thank you as it seems to work fine now. Based on your example I used the following formula:
=-SUM(OFFSET(F7,0,0,1,-MIN(F\$2,\$D4)))/\$D4-SUM(OFFSET(F8,0,0,1,-MIN(F\$2,\$D4)))/\$D4

Again thanks Darren

4. ### frank

5th June 2010 at about 2am Hi Darren
I need to include the first part of the formula in order to fully record the depreciation. Ie in a 3 year straight line, 50% in the first year, 100% in the 2nd & 3rd year and the additional 50% in the 4th year. In order for this to work I had to adjust the formula in the Adjusted purchases row 8 as follows =-F7/2+E7/2
thanks
frank

5. ### Imanto

23rd March 2011 at about 1pm The offset formula works fine if you do not have time limitation.
Is there a way to do the straight line depreciation with adjustment to the period (straight line years) in accordance with the remaining project or contract life?
For example: if we have a ten year project and the straight line depreciation calls for a 5 year straight line depreciation, we need to adjust period for the purchases in year 6 onward to the remaining years.

6. ### RKC

16th January 2014 at about 12am Why is the formula failing if years is 7 or more, I need it go up to 30 years, How can i fix this ?

7. ### Eddie

10th July 2015 at about 1am Hi, This is great help! however your calculation assumes that the depreciation rate is flat throughout the years. Is it possible to calculate it with different depreciation rates within the 5 years? For example, 40% on the first year and 15% for the remaining years

8. ### Joe

4th August 2015 at about 9pm Good posts guys and very helpful, thanks

How easily and neatly can I show an opening balance at Yr1 before any new additions in that year and assuming that the opening balance has lesser years left on it for depreciation?

E.g. An opening balance in Yr1 of \$300 with 3yrs left on it and then new additions of say \$500 with 5yrs depreciation.

9. ### SSS

21st May 2016 at about 3pm =-SUM(OFFSET(M4,0,0,0,-MIN(G\$3,\$C\$6)))/\$C\$6

I am using above shown function, M4 is the asset purchased and value of M4 itself is coming from other cell (i.e. G71)

this function is giving me error #REF

Kindly assist

10. ### Marc

16th January 2017 at about 9am Thanks! Been trying to figure out an elegant way to do this for ages! Really appreciate it.

Popup