Completing the OFFSET series, let’s examine a real-life example on how to use the OFFSET function to model depreciation.
Basically, the depreciation model needs the following inputs:
- Monthly capital expenditures forecasts
- Useful life of the assets
With those inputs we’ll be able to calculate:
- Monthly depreciation
- Accumulated CAPEX
- Accumulated depreciation
The next picture shows you how the model is setup.
Click the picture to enlarge
From my experience, to calculate depreciation most people use a simple SUM across the number of months (e.g.: =SUM(M10:X10)/12). However, this function is error-prone and inflexible.
To build a better formula, we’ll replace the range in the SUM function with OFFSET which will give us a flexible and appropriate width. The next picture illustrates the formula.
The tricky part is highlighted in blue. You want the range to include the capital expenditures of the previous months, but not too much. You are limited by the two elements in the MIN function.
- $D17 which represents the useful life of the assets.
- Q$7 which refers to a helper row with the index of the current month in the model.
So, if we are in the 5th month of the model and the useful life of the assets on that line is 12 months, we would widen it only to get 5 months of capital expenditures. However, from the 12th month on, the second parameter becomes irrelevant.
Download the model
Since this blog is all about developing better models, I strongly suggest you download the file with the example discussed above.
In addition to what is presented here, you’ll see a lot of additional explanations and 2 other uses of the OFFSET function to get yearly totals and also to get year-end balances.
If you still have problems with the OFFSET function, I suggest you read the OFFSET tutorial and download the attached file.
What do you think? Do you have any other use for OFFSET you would like to share?
Other related post:
Improve Table Visualization using OFFSET and Conditional Formatting
3 Comments
Super details with loads of good information, keep up the awesome effort. We shall save the page and retain for subsequent referral.
ej df 4x 8x
great article, so helpfull.
Being fit and healthy is the in thing. Actually, it really never goes out of style. That body is the most valuable asset you could have in your lifetime. Thus it is important that you take good care of it. Give it the proper attention it needs.