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.
- Create a template as show below. You can also request for a copy of this template
- The depn is calculated on row 18. We will start from the
K18, the depn for year 5. Select cell K19.
- 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
- The OFFSET formula can help to identify the range I14:K14. The OFFSET
formula is =OFFSET(K14,0,-2,1,3)
- =OFFSET(K14,0,-2,1,3) - K14 is the cell the OFFSET formula will
take reference / benchmark from.
- =OFFSET(K14,0,-2,1,3) - Zero is used because the range we are
referencing is on the same row as K14.
- =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.
- =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.
- =OFFSET(K14,0,-2,1,3) - Finally, the 3 instruct the OFFSET formula
to take 3 columns for the range.
- The entire offset formula will define the range as I14 to K14.
- To sum the range, we embrace the entire OFFSET formula within the
SUM formula as shown :
This is the same as
- 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
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
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
The D4 is locked with the
dollar sign ($) because we do not want the formula to move. The entire formula
- 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 :
- Finally, put a minus sign before the sum since depn is a cost in Profit &
Loss Statement :
- 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
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 :
- 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
- 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.