Calculating depreciation can be a bit of a pain. For one thing, for many businesses, it’s just one of three ways of accounting for purchases of new fixed assets, besides the investing cash flow and tax depreciation (in Canada, the Capital Cost Allowance, or “CCA”). That means it’s extra work, and according to some, extra work with minimal payoff, since many users of financial statements look at EBITDA (which excludes fixed asset purchases entirely) or Free Cash Flow (which looks only at the investing cash flow). And yet, the Net Income measure, which requires depreciation to be calculated correctly, is a crucial measure of the success and sustainability of any business, even if it’s used alongside EBITDA, Free Cash Flow and other performance metrics. This is all the more the case when we’re modelling what the business’s future looks like, and telling the owner what she/he can expect to make in the upcoming years. In our mind, then, depreciation is something that has to be calculated, and calculated well.
By well, we mean that depreciation should be modelled in such a way that, ideally, each month’s purchases are depreciated separately. This sounds like a lot of work, and if not done correctly, it is. But with one complex formula and a well-structured table, you can calculate depreciation that should satisfy the most detail-oriented of model users.
Below is a sample straight-line depreciation schedule, or “Waterfall”, for a single asset class, with a single useful life. (“Waterfall” refers to the characteristic shape that is generated.) While we said that each month’s purchases should be depreciated separately—and we do this when we’re working on a client project—we’ve simplified the depreciation waterfall here by assuming that asset purchases only occur in the first month of each year (with 2015 starting in September, meaning 4 months of depreciation for 2015). The principles we’re about to describe can easily be extended to a depreciation waterfall that has detail by month.
Let’s walk through this waterfall step-by-step, following the red “landmarks”.
A
If we look at the column going down from A, we see a series of years. The best way to think of this column is to view it as containing purchases in the stated year. We’ll call it the [Capex Year].
B
If we look at the row going right from B, we see a series of years again. This time, though, we can think of the row as containing depreciation in the stated year. We’ll call this the [Depreciation Year].
C
This cell contains the useful life in years of the asset class that we’re depreciating. It will be referenced by the formula in F, which we will get to shortly. We’ll call this [Useful Life in Years]. Multiplying it by 12 gives us [Useful Life in Months].
D
In the row going right from D, we have the number of months in the stated depreciation year over which the asset class is to be depreciated. For the first year, 2015, since the model starts in September and the purchase ($1,018,000) is assumed to occur then, we have 4 months of depreciation. In all other years, we have 12 months. We call this [Months in Depreciation Year].
E
In the column going down from E, we have the actual purchases of assets, or capex, that occurs in the year in column A. We’ll call this [In-Year Capex].
F
Finally, we come to F. For those of you geeky enough to have watched Back to the Future several times, you’ll be familiar with the Flux Capacitor, the device that makes Doc Brown’s time-travelling DeLorean possible. The formula contained in the cells making up the bulk of the Depreciation Waterfall, including the one marked by F, is the Depreciation Waterfall’s Flux Capacitor. Like the movie, the formula is complex; unlike the movie, I didn’t fall and hit my head on the bathroom sink to come up with it—though sometimes it feels like I did. This formula represents the amount of depreciation to be taken in the [Depreciation Year] of the cell (row B, running along the top), based on purchases of this asset class that occurred in this row’s [Capex Year] (column A, running down the left). The best way to teach you how to re-create it in your own models is to use “pseudo-code” to outline the various cases covered by the formula:
Case 1: If [Capex Year] is equal to [Depreciation Year],
Take [In-Year Capex] divided by [Useful Life in Months] times [Months in Depreciation Year].
Here, we’re saying that if the Depreciation Year we’re calculating for in this cell is the same as the Capex Year (i.e., this is the year of purchase), take 1 year’s depreciation, but then pro-rate it based on how many months there are in the Depreciation Year. For the first year, this will be 4 months.
Case 2: If [Depreciation Year] minus [Capex Year] plus 1 is equal to [Useful Life in Years] plus 1,
Take [In-Year Capex] minus [All Depreciation of In-Year Capex To Date].
Here, we’re saying that if the Depreciation Year is (here) 5 years after the Capex Year, plus 1, take the number that results from subtracting all of the depreciation taken to this Depreciation Year from the total In-Year Capex. In other words, in this Depreciation Year, take all of the depreciation that hasn’t been taken yet so that we’ll have now depreciated the Capex Year’s purchases entirely. Notice in the example above that only the 2015 Capex Year (2015 row) has any depreciation in the 6th year after the Capex Year (2020); this is because there were 4 months of depreciation in 2015, and the remaining 8 months must be made up in the 6th year.
Case 3: If [Depreciation Year] minus [Capex Year] plus 1 is more than [Useful Life in Years] plus 1,
OR If [Depreciation Year] is less than [Capex Year],
Take $0.
Here, we’re saying that if the Depreciation Year is more than 6 years after the Capex Year, or if the Depreciation Year is before the Capex Year, no depreciation should be taken. After all, the purchase in this row either hasn’t occurred yet, relative to this Depreciation Year, or it’s already been completely depreciated.
Case 4: Otherwise,
Take [In-Year Capex] divided by [Useful Life in Years].
Here, we’re saying that if the Depreciation Year is the 2nd through the 5th year after the Capex Year, we take the In-Year Capex divided by the Useful Life in Years—i.e., a complete year of depreciation.
G
Finally, to calculate the total depreciation in the Depreciation Year, we sum up all of the Fs falling in the same column. We can also convert this total to a monthly depreciation amount by dividing by the [Months in Depreciation Year].
If we translate F (F7) into an actual Excel formula, we have:
=IF($B7=F$4,($C7/($C$28*12))*F$5,
IF(((F$4-$B7+1)=($C$28+1)),($C7-SUM($D7:E7)),
IF(OR((F$4-$B7+1)>($C$28+1),(F$4<$B7)),0,
$C7/$C$28)))
The beauty of this formula is that it’s one-size fits all—just fill the depreciation table with this formula, and everything will calculate. There’s no need to, say, delete the formula from cells like D7, E8, etc., where the Depreciation Year is before the Capex Year, because the formula already adjusts for this.
Hopefully, this little tutorial has got you on your way to modelling depreciation faster and better. To get a working copy of a Depreciation Waterfall, send us an email.
