How to Build a Depreciation Schedule in Excel

A depreciation schedule spreads the cost of fixed assets over their useful lives and drives the PP&E roll-forward on the balance sheet. The core identity is simple: beginning PP&E plus capex minus depreciation equals ending PP&E. Straight-line depreciation is the most common method, and when several assets are placed in service over time you stack them into a waterfall so each layer depreciates on its own schedule.

What a depreciation schedule does in a model

Depreciation allocates the cost of a long lived asset across the periods it is used, rather than expensing it all at purchase. In a model it does two jobs: it reports depreciation expense on the income statement, and it drives the net PP&E balance on the balance sheet through a roll-forward.

The schedule connects three statements. Depreciation reduces pre-tax income on the income statement, it is added back on the cash flow statement because it is non cash, and it reduces net PP&E on the balance sheet. Capex, the cash spent on new assets, is the other side of the roll-forward and is an investing outflow on the cash flow statement.

Build the PP&E roll-forward step by step

The roll-forward is the spine of the schedule. This single period layout shows the identity that ties capex and depreciation to the ending balance.

1. Set beginning net PP&E equal to the prior period ending balance.
2. Add capex, the cash invested in new fixed assets this period.
3. Subtract depreciation, computed by the straight-line method.
4. Compute ending net PP&E: =beginning + capex - depreciation.
5. Send depreciation to the income statement and the cash flow add-back.
6. Send capex to the investing section of the cash flow statement as an outflow.

The roll-forward identity is begin plus capex minus depreciation equals end, and it must hold every period.

Straight-line depreciation and the asset waterfall

Straight-line depreciation charges the same amount each period: cost divided by useful life. For a single asset that is one formula. When capex is added every year, each year's spend becomes a new asset layer that depreciates over its own life, and you sum the layers to get total depreciation in any period. That stack is the depreciation waterfall.

A waterfall is usually laid out as a triangular grid: each row is a capex vintage, each column is a forecast year, and the cell is that vintage's depreciation in that year. Total depreciation for a year is the column sum.

• Straight-line: =asset_cost / useful_life per period for each asset.
• Existing assets: depreciate remaining net book value over remaining life.
• New capex layer: =capex_year / useful_life, charged only from the year placed in service.
• Total depreciation: =SUM of all active layers in that column.
• Ending PP&E: =beginning_PPE + capex - total_depreciation.

Pitfalls and what reviewers check

Hardcoded depreciation is the most common red flag. If a reviewer cannot trace depreciation back to a cost and a useful life, the number is suspect. Drive every layer from an input, not a typed figure, so the schedule updates when capex assumptions change.

Watch for assets that depreciate past zero. A waterfall layer should stop once the asset is fully depreciated; a common bug keeps charging depreciation after the useful life ends, driving net PP&E negative. Cap each layer's cumulative depreciation at its cost.

Reviewers confirm the roll-forward identity holds in every period, that depreciation on the income statement equals the waterfall column total, and that capex on the cash flow statement equals the capex added in the roll-forward. Mismatches mean a link or a hardcode is off.