Fixed-asset schedules are a yearly chore. Excel turns them into a template you fill once.
In this article
Straight-line method (SLM)
“`excel
=SLN(cost, salvage, life) ‘ yearly depreciation
=SLN(500000, 25000, 5) ‘ = 95,000 per year
“`
The 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:
“`excel
Depreciation: =C5*$B$2
Closing WDV: =C5-D5 ‘ becomes next year’s opening
“`
Excel’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:
“`excel
=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 |
Stuck on a step? Ask a question and the AI answers using this article.