Straight-line Depreciation

  • Tagged with:
  • Design,
  • Excel,
  • MIN,
  • OFFSET,
  • Spreadsheets,
  • 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:

Depreciation Matrix

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.

Depreciation Offset

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. Darren Miller

    3rd June 2010 at about 11am

    I assume by “half year rule” you mean only depreciate the asset for 1/2 of the year of purchase then full years from then on and then 1/2 the year in the last year of depreciation? I would do this by inserting a row under the purchase row and calling this “adjusted purchase”. In this row write a formula that takes 1/2 of this year’s purchases and 1/2 of last year’s, and then base the depreciation calculation off this adjusted purchases row. Does this answer your question?

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

  5. Darren Miller

    4th June 2010 at about 11am

    Frank, it should only be the second half of your formula i.e. =-SUM(OFFSET(F8,0,0,1,-MIN(F$2,$D4)))/$D4. This is on the basis that row 8 now reflects 50% of purchases from current period and 50% from prior period.

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

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

  8. Darren

    23rd March 2011 at about 2pm

    Daniel, this can be done if you use an array formula in the form of =SUM(IF(…,…)). For each year of capex you need to calculate the straight line depreciation years for that year of capex, so as you approach project completion the number of years over which you depreciate the capex reduces. Then your array formula can apply the specific number of years to each year of capital expenditure. This is probably best explained in a working model – see here.

  9. 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 ?

  10. Darren

    6th May 2014 at about 3pm

    Probably because you left out or made an error with the MIN part of the formula. Given that you’re in column F (the seventh column) you need to make sure that the OFFSET only looks left up to the number of years you are in.

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

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

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

  14. Marc

    16th January 2017 at about 9am

    Thanks! Been trying to figure out an elegant way to do this for ages! Really appreciate it.

Join the discussion

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

pregnancy ultrasound week by week