A fixed asset register is one row per asset with its cost, life and purchase date, plus a grid of columns for each financial year. One formula, filled down and across, works out each year's depreciation, so adding a new asset means adding a row. This template uses straight-line depreciation and an April to March year.
What columns does the register need?
- A: asset name, such as Laptop
- B: purchase date (a real date)
- C: cost
- D: salvage value
- E: life in years
- F: monthly depreciation = (C2-D2)/(E2*12)
- G: financial year of purchase = YEAR(B2)-(MONTH(B2)<4), which is the starting calendar year of that April to March period
- H: months used in the first year = MOD(3-MONTH(B2),12)+1
- I: total months of life = E2*12
Test H by hand. October gives MOD(3-10,12)+1 = 5+1 = 6 months, from October to March. April gives 12, and March gives 1. The columns F to I are helpers, and they keep the main formula short enough to read.
What is the one formula for the grid?
Put the starting year of each financial year in row 1 of the grid: 2024 in K1, 2025 in L1, 2026 in M1, and so on. A second header row can show the label with =K1&"-"&RIGHT(K1+1,2), giving 2024-25. Then in K2, filled down and across:
=$F2*IF(K$1<$G2, 0, MAX(0, MIN(IF(K$1=$G2, $H2, 12), $I2-IF(K$1=$G2, 0, $H2+12*(K$1-$G2-1)))))
Read it in two parts. The IF picks the number of months to charge: 0 before the asset exists, the first-year months in the purchase year, then 12. The MIN stops charging once the total life of months is used up. The result is that count times the monthly charge.
Does it work on a real example?
Two made-up assets. A laptop costing ₹72,000, salvage ₹6,000, life 4 years, bought on 10 October 2024. A printer costing ₹18,000, no salvage, life 3 years, bought on 12 June 2025.
| Asset | Monthly | 2024-25 | 2025-26 | 2026-27 |
|---|---|---|---|---|
| Laptop | 1,375 | 8,250 | 16,500 | 16,500 |
| Printer | 500 | 0 | 5,000 | 6,000 |
| Total | 1,875 | 8,250 | 21,500 | 22,500 |
The laptop has six months in its first year, 6 x 1,375 = 8,250, then full years. The printer begins in June, so its first year has ten months, 10 x 500 = 5,000, and then twelve months, 6,000. Totals: 16,500 + 5,000 = 21,500 and 16,500 + 6,000 = 22,500.
How do you show book value?
Add a second grid beside the first for the book value at each year end. In its first cell, filled across and down: =$C2-SUM($K2:K2), which takes cost and subtracts every charge up to that year. Salvage should be the floor. A final column showing the total charged so far, =SUM($K2:$Q2), makes it easy to confirm the figure is never more than cost minus salvage.
How do you keep the register tidy?
- Add new assets at the bottom and copy the formulas down. Never insert a row above the formulas without checking the references.
- Add a Disposed date column and a note when an asset is sold or scrapped, and stop its depreciation by hand.
- Colour the first and last year of each asset so changes of year stand out.
- Keep invoices in one folder and name them with the asset name and date.
Annual routine
- Add a new column header for the next financial year.
- Copy the grid formula across, and check the total row.
- Compare the total charge with last year's, and explain any large change.
- Save a copy of the register before filing anything that depends on it.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


