How To Calculate Depreciation in your business model

When you calculate depreciation(depn) in your business model, you do not have to create a 36 columns by 36 rows table. All you need is the OFFSET formula and you can have your straight line depn computed in one single row. Here’s how:

1. Create a template as show below. You can also request for a copy of this template from this download link.

2.The depn is calculated on row 18. We will start from the K18, the depn for year 5. Select cell K19.

3. Assume that the depn for the asset is 3 years. If we are to calculate it manually, we will have to sum the new assets from Year 3 to Year 5, i.e. I14:K14 and divide the total by 3, the number of years of depn. In our case, we will use the OFFSET formula to do the same thing automatically.

4. The OFFSET formula can help to identify the range I14:K14. The OFFSET formula is =OFFSET(K14,0,-2,1,3)

1. =OFFSET(K14,0,-2,1,3) – K14 is the cell the OFFSET formula will take reference / benchmark from.
2. =OFFSET(K14,0,-2,1,3) – Zero is used because the range we are referencing is on the same row as K14.
3. =OFFSET(K14,0,-2,1,3) – The next value minus two (-2) is used to indicate that we are starting from the range 2 cells to the left of the OFFSET formula.
4. =OFFSET(K14,0,-2,1,3) – The “1” set the number of rows for the range. 1 means our range is only one row, that is row 14.
5. =OFFSET(K14,0,-2,1,3) – Finally, the 3 instruct the OFFSET formula to take 3 columns for the range.
6. The entire offset formula will define the range as I14 to K14.

5. To sum the range, we embrace the entire OFFSET formula within the SUM formula as shown :Enter your text here…

=SUM(OFFSET(K14,0,-2,1,3))

This is the same as

SUM(I14:K14)

6. To make the OFFSET formula adjust the range according to the number of years of depreciation, we need to change the -2 and the 3 in the OFFSET formula

OFFSET(K14,0,-2,1,3)

Since the number of years of depreciation is 3, we have to replace the number -2 with -3+1, since we know that the range will always start x number of years away from the current year. In our example, it will go back 3 years, taking count of the current year as 1 year. With the OFFSET formula now change to

OFFSET(K14,0,-3+1,1,3)

Wwe can replace the 3 to refer to cell D4 which will be used to determine the number of years of depreciation. The OFFSET formula becomes

OFFSET(K14,0,-$D$4+1,1,$D$4)

The D4 is locked with the dollar sign ($) because we do not want the formula to move. The entire formula becomes

=SUM(OFFSET(K14,0,-$D$4+1,1,$D$4))

7. To calculate depn, we need to divide the total value of the new asset by the number of years of depreciation, i.e. the value in D4. We can include without quotes “/$D$4” at the end of our last OFFSET formula as follows:

=SUM(OFFSET(K14,0,-$D$4+1,1,$D$4))/$D$4

8. Finally, put a minus sign before the sum since depn is a cost in Profit & Loss Statement :

=-SUM(OFFSET(K14,0,-$D$4+1,1,$D$4))/$D$4

9. Copy the formula to the range G18:J18. The formula will work well if nothing is entered into row 14 before column G. ALTernatively, we can also improve on the formula such that it is intelligent enough to sum up the assets only from year 1 i.e. Column G. If we are to do it manually, we know that only one year i.e. G14 should be included to calculate depreciation for the first year and two years (G14 and H14) for year 2. Beyond that, we will take only the 3 years of depreciation. We can find out the duration between the 1st year and the current year by using the COUNT formula. We will take the shorter period of 1 or 2 years if we are in Year 1 & 2 and the shorter period of 3 years (years of depreciation) if we are in year 4 and 5. We can enter the COUNT formula to always start counting from year 1 by locking the first cell reference to G14 with the dollar sign ($). If we are working on cell G18, the COUNT formula is

COUNT($G$14:G14)

Then we can find out which is the longer duration : COUNT($G$14:G14) or $D$4. This can be done with the MIN formula and it should be modified as follows:

MIN(COUNT($G$14:G14), $D$4)

10.  Using our OFFSET formula in G18, we can replace the $D$14 in the numerator of our deprecation formula with the MIN(COUNT($G$14:G14), $D$4). The final formula is

=-SUM(OFFSET(G14,0,- MIN(COUNT($G$14:G14), $D$4)+1,1, MIN(COUNT($G$14:G14), $D$4)))/$D$4

11. With this formula, we can now change the number of years of depreciation to any number and the formula will continue to give us the right amount of depreciation for each year.