Book Depreciation Expense
In the first section of our new depreciation schedule, we bifurcate the total book depreciation expense computed in Step 2 into (a) depreciation of new fixed assets (e.g. PP&E) generated by capital expenditures, and (b) depreciation of existing fixed assets (i.e. those fixed assets on the balance sheet at the most recent historical period). Assuming straight-line depreciation of new fixed assets and the total depreciation expense already projected as a percentage of sales, the depreciation of existing fixed assets can be computed as a plug between the two.
These calculations serve as a reality check. If the depreciation of existing fixed assets implied by the depreciation of new fixed assets and the total depreciation expense becomes negative at any point over the projection period, we may need to refine our assumptions. Specifically, our assumed useful life may be too low, or our assumed capital expenditures over the projection period may be too low. Try setting the useful life assumption to three years and observe that the depreciation of existing fixed assets is negative in the two final years of the projection period. Now, try increasing projected capex as a percentage of sales to 5% over the projection period; it should exacerbate the problem.
Problems with our depreciation and capex assumptions may not manifest themselves until after the five-year period we are projecting. In practice, you would want to extend the projections out a few more years and look for problems. If your assumptions are reasonable, you should observe that the depreciation of existing fixed assets ultimately goes to zero, such that the depreciation of new fixed assets equals the total depreciation expense.
Tax Depreciation Expense
In financial modeling, we often ignore that depreciation schedules for fixed assets are different for accounting and tax purposes. While we have assumed straight-line depreciation for accounting, or book, purposes, tax depreciation often occurs on an accelerated schedule in practice. Accelerated depreciation has an important benefit—the present value of the tax shield from tax-deductible depreciation is larger when the depreciation is accelerated. In the United States, companies can use the Modified Accelerated Cost Recovery System (“MACRS”) schedule to depreciate fixed assets for tax purposes. The MACRS schedule is promulgated by the IRS, and replicated here in our model.
We select a “property class” class available in the MACRS schedule that most closely ties to the useful life we assumed in calculating book depreciation, and compute tax depreciation expense using HLOOKUPs on the MACRS schedule. Next, we add a toggle that allows us to select either book (i.e. straight-line) depreciation or MACRS depreciation as our tax depreciation of new fixed assets. Note that any difference between how we depreciate fixed assets for book and tax purposes will give rise to deferred taxes. To avoid obfuscating the deferred tax impact of other features in our operating model (e.g. undistributed earnings from equity investments), we will set tax depreciation of new fixed assets equal to the book depreciation of such assets for now. You should try toggling on MACRS depreciation after we complete our tax schedule in Step 16 and observe how the net deferred tax liability changes.
Recall from above that our projected book depreciation of existing fixed assets is a plug. Without further diligence or a handful of other assumptions, we have no better way to estimate these figures. Similarly, we have no visibility into the depreciation of existing fixed assets for tax purposes. So, for simplicity, we set the tax depreciation of existing fixed assets equal to the book depreciation of such assets. At this point, our total tax depreciation equals our total book depreciation, and there is no deferred tax impact related to depreciation.