📎 This article includes 1 downloadable practice file ↓
Fixed-asset schedules are a yearly chore. Excel turns them into a template you fill once.
In this article
Straight-line method (SLM)
=SLN(cost, salvage, life) ' yearly depreciation
=SLN(500000, 25000, 5) ' = 95,000 per yearThe Companies Act, 2013 (Schedule II) uses useful lives, commonly with a 5% residual value.
Written-down value (WDV)
Each year’s depreciation is a fixed percentage of the opening value. For a rate in B2 and opening WDV in C5:
Depreciation: =C5*$B$2
Closing WDV: =C5-D5 ' becomes next year's openingExcel’s DB(cost, salvage, life, period) calculates the rate from salvage and life if you need the Companies Act-style WDV rate.
Income-tax: block of assets and the 180-day rule
Under the Income-tax Act, assets are grouped into blocks with a rate (for example 15% for general plant and machinery). If an asset is used for less than 180 days in the year of purchase, only half the rate applies that year:
=IF(DaysUsed<180, AdditionCost*Rate/2, AdditionCost*Rate)with DaysUsed = DATE(FYEndYear,3,31) - PutToUseDate + 1.
A complete schedule layout
| Column | Formula idea |
|---|---|
| Opening WDV | Previous year’s closing |
| Additions ≥180 days / <180 days | SUMIFS on the asset register by put-to-use date |
| Deletions (sale value) | SUMIFS on disposals |
| Depreciation | (Opening + Additions≥180 − Deletions) × rate + Additions<180 × rate/2 |
| Closing WDV | Opening + Additions − Deletions − Depreciation |
A worked example: one machine, five years
A machine costs ₹5,00,000, bought on 10 October 2018 (so used for less than 180 days in FY 2018–19), residual value ₹25,000, useful life 5 years, income-tax block rate 15%.
| Year | SLM (Companies Act style) | WDV @ 15% (IT Act) |
|---|---|---|
| FY 2018–19 | ₹95,000 × (days used/365) | ₹5,00,000 × 7.5% = ₹37,500 (half rate, <180 days) |
| FY 2019–20 | ₹95,000 | ₹4,62,500 × 15% = ₹69,375 |
| FY 2020–21 | ₹95,000 | ₹3,93,125 × 15% = ₹58,969 |
The same asset gives very different figures in the books and in the tax computation. That difference is exactly why companies record deferred tax.
Building it so it doesn’t break
- Keep one row per asset in an asset register (date put to use, cost, block, life, disposal date and value).
- Calculate the schedule from the register with SUMIFS by block and date — never type depreciation figures by hand.
- Add a check row: opening WDV + additions − deletions − depreciation must equal closing WDV for every block.
- Pro-rata SLM for the first year:
=SLN(cost, salvage, life) * (DATE(2019,3,31) - put_to_use + 1) / 365.
Common mistakes
- Applying the full rate to an asset used for less than 180 days.
- Forgetting that sale proceeds reduce the block (IT Act) rather than creating a profit/loss per asset.
- Mixing the two methods in one column.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Depreciation practice workbookPractice file from the matching course lesson.⬇ XLSX · 13 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.