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)
5. To sum the range, we embrace the entire OFFSET formula within the SUM formula as shown :Enter your text here...
This is the same as
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
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 becomes
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:
8. Finally, put a minus sign before the sum since depn is a cost in Profit & Loss Statement :
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
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:
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,
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.
Share this page:
Would you prefer to share this page with others by linking to it?
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft