How to build a depreciation schedule in Excel and Google Sheets

Step by step

1. 1Enter the asset cost in B2 (e.g. 50000), salvage value in B3 (e.g. 5000), and useful life in years in B4 (e.g. 5).
2. 2For straight-line depreciation, the annual charge is constant: =SLN(B2,B3,B4). Enter this in B5 to show the annual deduction.
3. 3Build a year-by-year table: year numbers 1–5 in column A starting at A8, opening book value in column B (B8=$B$2), annual SLN charge in column C (=$B$5), and closing book value in D (=B8-C8).
4. 4For double-declining balance, replace the C column formula with =DDB($B$2,$B$3,$B$4,A8). DDB automatically switches to straight-line in later years when SL becomes more favorable.
5. 5In the following year rows, set opening book value = prior closing book value (B9=D8), then copy the charge and closing balance formulas down through the final year.

Tips

• DDB's fourth argument (period) must match the scale of your useful-life argument. If life is in years, period is the year number. If life is in months, period is the month number.
• The VDB function is more flexible than DDB — it lets you specify start and end periods for partial-year depreciation, switch from declining balance to straight-line at any point, and handle mid-year acquisitions.
• Confirm that the closing book value in the final year equals the salvage value for SLN. DDB may not exactly reach salvage — cap each year's DDB charge at (book value − salvage) to prevent going below salvage.

Frequently asked