Modelling Depreciation: A Straight-Line Depreciation Waterfall

BIf 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. The best way to think of this column is to view it as containing purchases in the stated year. Let’s walk through this waterfall step-by-step, following the red “landmarks”. The first and last rows use unique formulas, so you should not delete those rows, and when you insert a row, insert new rows somewhere between the 2nd row and above the last.

For example, the total depreciation for 2023 is comprised of $60k of depreciation from Year 1, $61k of depreciation from Year 2, and then $62k of depreciation from Year 3 – which comes out to $184k in total. Once repeated for all five years, the “Total Depreciation” line item sums up the depreciation amount for the current year and all previous periods to date. Note that for purposes of simplicity, we are only projecting the incremental new capex. We’ll now move on to a modeling exercise, which you can access by filling out the form below.

Formatting Data Labels

The depreciation amount is then computed by summing up the relevant column of the waterfall section. In closing, the net PP&E balance for each period is shown below in the finished model output. Returning to the “PP&E, net” line item, the formula is the prior year’s PP&E balance, less Capex, and less depreciation. Here, we are assuming the Capex outflow is right at the beginning of the period (BOP) – and thus, the 2021 depreciation is $300k in Capex divided by the 5-year useful life assumption. In our hypothetical scenario, the company is projected to have $10mm in revenue in the first year of the forecast, 2021. The revenue growth rate will decrease by 1.0% each year until reaching 3.0% in 2025.

I usually also distinguished in my models between the “growth” Capex and the “maintenance” Capex to simplify scenario analysis (as-is/base scenario, moderate-growth scenario, high-growth scenario, etc.). If you search on Google, you can find many tutorials and articles that explain how to construct a waterfall chart in Excel using stacked columns or bars. Some tutorials also explain ways to create the connecting lines 1, or how to handle negative values 2.

Straight Line Depreciation Method

• Let’s walk through this waterfall step-by-step, following the red “landmarks”.
• In this approach, since the direction of all the calculations is columnar, it is very easy to expand it by just copy and pasting.
• But with one complex formula and a well-structured table, you can calculate depreciation that should satisfy the most detail-oriented of model users.
• This schedule in a Financial Model is used to forecast Net PPE balances and Depreciation for existing and new assets.

For assets that fall under 5-year property (e.g., computers, vehicles, and machinery), the depreciation schedule would allocate 20% in the first year, 32% in the second year, and so on, until the asset is fully depreciated. The bottom of the depreciation schedule often reflects near-zero depreciation as the asset nears the end of its useful life. Under MACRS, the declining balance method is used for most asset classes, except for real property, which follows the straight-line method. MACRS also employs a mid-year convention, meaning assets are assumed to be placed in service at the midpoint of the tax year, regardless of the actual purchase date.

Financial Statements

When forecasting depreciation, analysts must make key assumptions about a company’s future capex, the useful life of the current asset base, and the useful life of new capex. For GAAP reporting, companies typically use the straight-line method, spreading costs evenly over an asset’s lifespan. For tax purposes, the MACRS method accelerates depreciation, allowing larger upfront deductions. The difference between these methods can create a deferred tax liability (DTL)—a temporary accounting difference where tax depreciation exceeds book depreciation.

Building a CAPEX schedule and Depreciation waterfall in a financial model

• For a complete depreciation waterfall schedule to be put together, more data from the company would be required to track the PP&E currently in use and the remaining useful life of each.
• At the end of the day, the cumulative depreciation amount is the same, as is the timing of the actual cash outflow, but the difference lies in net income and EPS impact for reporting purposes.
• The double declining method (DDB) is a form of accelerated depreciation, where a greater proportion of the total depreciation expense is recognized in the initial stages.
• The recognition of depreciation is mandatory under the accrual accounting reporting standards established by U.S.

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts. If a manufacturing company were to purchase $100k of PP&E with a useful life estimation of 5 years, then the depreciation expense would be $20k each year under straight-line depreciation. By well, we mean that depreciation should be modelled in such a way that, ideally, each month’s purchases are depreciated separately.

Now, try increasing projected capex as a percentage of sales to 5% over the projection period; it should exacerbate the problem. In this article, we’ll break down the steps to create a depreciation schedule, explain straight-line and accelerated depreciation methods, and explore the relationship between depreciation, deferred taxes, and financial reporting. By the end, you’ll have a comprehensive understanding of how to accurately calculate depreciation expense and use it effectively in financial modeling and planning. 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.

Excel Depreciation Waterfall Schedule Calculation

In this blog, we recommend a better approach that is far more simple, scalable, and accurate. Depreciation has a significant impact on a company’s financial performance. Since Capex was input as a negative, the Capex will increase the PP&E amount as intended (otherwise, the formula would have added Capex if the positive sign convention had been used).

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.

Live video-chat tutoring is available with experienced investment bankers for a fee of $800/hour. In addition, your tutoring sessions enable you and your tutor to share models, a virtual blackboard, and any other files via an integrated web and phone platform that enhances the learning experience. The program uses online video lessons, Excel model templates and various financial filings to teach students how to build, analyze, and interpret financial models in a step-by-step fashion at their own pace. Reporting depreciation is an important part of the financial reporting process. From our modeling tutorial, our hypothetical scenario shows the method by which depreciation, PP&E, and depreciation waterfall Capex can be forecasted, and illustrates just how intertwined the three metrics ultimately are. Then, we can extend this formula and methodology for the remainder of the forecast.