ModeldrillDepreciation schedule
Straight-line depreciation = (cost - salvage value) / useful life, the same amount each year until the asset is written down. In Excel, =SLN(cost, salvage, life). A depreciation schedule lays each asset or capex vintage across years and sums the columns to get total depreciation per year.
Syntax
=SLN(cost, salvage, life); =DDB(cost, salvage, life, period)| Part | What it is |
|---|---|
cost | Purchase price including installation |
salvage | Expected residual value at the end of its life (often 0) |
life | Useful life in years |
period | For DDB, the year for which to compute depreciation |
Examples
Cost 10,000, salvage 1,000, life 5 years
=SLN(10000, 1000, 5)Result: 1,800 per year
Double-declining balance, years 1 and 2
=DDB(10000, 1000, 5, 1) =DDB(10000, 1000, 5, 2)Result: 4,000 then 2,400
40% of the remaining book value each year.
Start in the purchase year, stop after the life (year in C2, purchase year in $B$5)
=IF(AND(C$2>=$B$5, C$2<$B$5+$B$7), $B$6/$B$7, 0)Result: cost / life in the live years, 0 otherwise
Common mistakes
- Depreciating below salvage value or past the useful life; cap the schedule with MIN or an IF on the year.
- Forgetting new capex: each year's purchases need their own row (a waterfall) or total depreciation stays flat while PP&E grows.
- Mixing tax and book depreciation: accelerated tax depreciation creates deferred taxes and belongs in the tax schedule, not the income statement.
Practice Depreciation schedule
These drills check your answer on live data, free, no account needed.
- Straight-line depreciation by assetThree assets are depreciated straight line to zero over their useful lives. Row 4 numbers the years of use. Fill D5:H7 with one formula that charges cost divided by life while the asset is still within its life, and zero after. Then total each year in row 8.
- Depreciation from the year of purchaseEach asset starts depreciating in the year it is bought (column C) and runs for its life (column D). Fill E5:I7 with one formula: charge cost divided by life only in years from the first year up to, but not including, first year plus life.
- Fixed asset roll-forwardRoll the fixed asset balance forward. Closing balance (row 8) is opening plus capital spending less depreciation. Each year after the first opens with the prior year's closing balance (row 5).
Questions
How do I depreciate new capex each year in a forecast?
Build a waterfall: one row per capex year, depreciating that year's spend over its life, then sum the rows. A shortcut is depreciation as a percent of opening PP&E.
Does depreciation affect cash flow?
Not directly; it is a non-cash expense. It lowers taxable income, so it reduces cash taxes, which is why it is added back in cash flow from operations.
Related
- PP&E roll-forwardThe fixed asset roll-forward links the balance sheet to capex and depreciation: closing PP&E = opening + capex - depreciation - disposals. Example and layout.
- EBITDA to net incomeNet income = EBITDA - D&A - interest - taxes. Each step of the bridge (EBIT, EBT, tax at the rate, net income) with exact numbers and the tax-on-losses rule.
- Cash flow from operationsCFO = net income + D&A + other non-cash charges - increase in working capital. How the indirect method works line by line, a worked example and sign errors.
- EBITDAEBITDA = EBIT + depreciation and amortization, or net income + interest + taxes + D&A. Exact worked examples, what EBITDA leaves out and the usual mistakes.