Sumwise’s Blog

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?

This entry was posted on Tuesday, July 28th, 2009 at 5:14 pm and is filed under Financial Modelling. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.