Anatomy of the asset register and depreciation schedule: sheets, columns, formulas, and common traps
The workbook has two main sheets that work together. The Asset Register lists every long-term asset with its key details. The Depreciation Schedule calculates how each asset loses value year by year.
Sheet 1: Asset Register
Each row is one asset. The columns store everything needed to calculate depreciation:
- Asset ID — unique identifier (e.g., A-001)
- Description — what the asset is
- Cost — what you paid for it
- Useful Life — how many years it will last
- Salvage Value — what it will be worth at the end
- Method — SL (straight-line) or DDB (double-declining balance)
- Purchase Date — when you bought it
Sheet 2: Depreciation Schedule
Each row is one year of an asset's life. The columns calculate:
- Annual Expense — depreciation for that year
- Accumulated Depreciation — total depreciation taken so far
- Book Value — cost minus accumulated depreciation
- Check — verifies Book Value = Cost − Accumulated Depreciation
Formula Architecture
Depreciable Base (SL): =(Cost - SalvageValue) / UsefulLife
Accumulated Depreciation (Year N): =PriorYearAccum + CurrentYearExpense
Book Value: =Cost - AccumulatedDepreciation
Check: =IF(BookValue = Cost - AccumDep, "OK", "ERROR")
Common Failure Mode: Hard-Coded Numbers
The most common error is typing the annual expense directly into the schedule instead of calculating it from the register. If cost changes, a hard-coded number does not update. Always use a formula that references the register's cost, life, and salvage value cells.
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | A | B | C | D | E | F | G | |
| 2 | Asset Register | |||||||
| 3 | Row | Asset ID | Description | Cost | Useful Life | Salvage Value | Method | Purchase Date |
| 4 | 3 | A-001 | Delivery Van | 30000 | 5 | 5000 | SL | 1/15/2025 |
| 5 | 4 | A-002 | 3D Printer | 15000 | 4 | 1500 | SL | 3/1/2025 |
| 6 | 5 | A-003 | Server Rack | 8000 | 3 | 800 | DDB | 6/1/2025 |
| 7 | ||||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 |
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | A | B | C | D | ||
| 2 | Depreciation Schedule — A-001 Delivery Van | |||||
| 3 | Year | Annual Expense | Accumulated Depreciation | Book Value | Check | |
| 4 | Start | — | — | 30000 | OK | |
| 5 | 1 | 5000 | 5000 | 25000 | OK | |
| 6 | 2 | 5000 | 10000 | 20000 | OK | |
| 7 | 3 | 5000 | 15000 | 15000 | OK | |
| 8 | 4 | 5000 | 20000 | 10000 | OK | |
| 9 | 5 | 5000 | 25000 | 5000 | OK | |
| 10 |