Capital spending (capex) buys assets that last for years, so its cost reaches the income statement a slice at a time, as depreciation. The PP&E schedule keeps track:
- Net PP&E at the end of the year = last year's + capex - depreciation.
- Straight-line depreciation = (cost - salvage value) / useful life, which
SLN(cost, salvage, life) returns.
A model that forecasts capex every year needs a waterfall: one row per year of purchase (a vintage), each depreciated over its own life. Total depreciation is the existing assets' charge plus every vintage row. One formula with mixed references fills the whole grid: C$3 keeps the year row, $A9 and $B9 keep the vintage columns.
Two edge cases catch people out. An asset stops depreciating at its salvage value, so cap the charge with MIN. And assets bought during the year are in use for about half of it, so many models charge half a year in the first year and the other half after the last full year: the half-year convention.
Cost 120,000 Salvage 20,000 Life 5
=SLN(B3,B4,B5) 20,000 a year
Year 6: =MIN(SLN($B$3,$B$4,$B$5),G8-$B$4) 0
After five years the book value is at salvage, so the MIN stops the charge.