Calculating depreciation under the straight-line method is fairly easy. But modeling it for forecast purposes is quite tricky. Many financial models have a messy straight-line depreciation schedule. Analysts frequently grapple with negative balances in fixed asset accounts due to excessive depreciation. This problem often leads to the use of "plugs" to adjust figures, a less-than-ideal solution.
While the waterfall approach offers a more structured method, it has its drawbacks, as we'll explore. In this blog, we recommend a better approach that is far more simple, scalable, and accurate. And we shall illustrate that with a simple case study.
Case study
Idalco is a new company with no fixed assets in its balance sheet. Its future capex plans by year, are given in the table below. Model the straight-line depreciation assuming the expected life of the asset is four years and the assets have no salvage value.
2024 | 2025 | 2026 | 2027 | 2028 | 2029 | |
Capex | 5,000 | 6,240 | 7,176 | 7,894 | 8,683 | 9,551 |
Waterfall approach and its limitations
The waterfall approach looks at the problem on how to spread the cost of an asset of asset over its life and then stop.
To achieve this, it creates a waterfall section where you create a separate row for each of the years in which the capex is to be incurred. The cost is then spread over the useful life.
The depreciation amount is then computed by summing up the relevant column of the waterfall section.
The model would look like the image below.
The problem with the approach is that it is easy to expand the model to cover more years. For each year that you need to add, you will have to extend the column. In addition, you would also need to add a row for each of the years in the waterfall section and change the formula in the depreciation section.
This makes the model prone to error and reduces the simulation capabilities.
A more robust approach using Sumifs
To build a more scalable solution, we are going to invert the problem statement. Rather than defining the problem as spreading the depreciation over the future years, our problem statement is going to be as follows:
"Depreciation for a year is going to be equal to annual depreciation of all the assets that are not retired yet"
Here is a simple pseudo-code for this depreciation calculation:
Depreciation = Sum(Annual depreciation of all capex, Capex years, greater than current year - useful life)
In the example given below, this is the precise formula that we have used in cell H10
H10 =-SUMIFS($C13:H13,
$C6:H6, ">"&H6 - $E$4)
The logic can also be extended when different capex have different life. In this case, we would have to calculate the retirement year of each capex and apply the following approach:
Depreciation = Sumifs(Annual depreciation, Retirement year of asset, greater than or equal to current year)
In the following table below, this is the precise formula that we have used in cell H8
H8 =-SUMIFS($C13:I13,
$C6:I6, ">="&I4)
In this approach, since the direction of all the calculations is columnar, it is very easy to expand it by just copy and pasting. And we do not need any manual steps.
This approach also can be extended to a situation where a company has multiple classes of assets with different useful life and salvage value.
If you are interested in taking your modelling skills to the next level and be well above the industry standards, do explore our financial modelling, and financial mathematics programs.