Excel guide · 5 min read
How to build a fixed asset register in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Asset ID | Description | Purchased | Cost | Salvage | Life (yrs) | Disposed | Proceeds | Annual depreciation | Months used | Accumulated | Book value |
| 2 | FA-001 | Delivery van | Apr 1, 2022 | 32000 | 6000 | 5 | 5200 | 56 | 24266.666667 | 7733.3333333 | ||
| 3 | FA-002 | Design laptop | Sep 15, 2024 | 2400 | 0 | 3 | 800 | 27 | 1800 | 600 | ||
| 4 | FA-003 | Large-format printer | Feb 1, 2021 | 18500 | 1500 | 5 | 3400 | 70 | 17000 | 1500 | ||
| 5 | FA-004 | Office furniture | Jun 10, 2025 | 7800 | 300 | 7 | 1071.4285714 | 18 | 1607.1428571 | 6192.8571429 | ||
| 6 | FA-005 | Old laptop | May 1, 2021 | 1800 | 0 | 3 | Nov 20, 2025 | 150 | 600 | 54 | 1800 | 0 |
| 7 | FA-006 | Vinyl cutter | Aug 20, 2026 | 9600 | 600 | 6 | 1500 | 4 | 500 | 9100 |
Download this sheet as an Excel practice file (.xlsx)
The register layout
A fixed asset register is the list behind the equipment line on a balance sheet: what you own, what it cost, how much has been depreciated and what it's worth on the books today. A spreadsheet works well for a small business with a few dozen assets.
The sample belongs to a small print shop. Each row is one asset with an ID, a description, the purchase date, cost, salvage value (what you expect to get at the end) and useful life in years. G and H stay blank until an asset is sold or scrapped. The as-of date sits in P1, so every figure recalculates when you change it.
Useful lives, salvage values and the depreciation method are accounting decisions. The register only does the arithmetic: put the values your accountant gives you in the input columns, and don't take the sample lives as advice.
Annual straight-line depreciation
Straight-line spreads cost minus salvage evenly over the life. The delivery van cost 32,000, should be worth 6,000 after 5 years, so it depreciates 26,000 / 5 = 5,200 a year, or 433.33 a month. Excel's SLN function returns the same annual figure, =SLN(cost, salvage, life); the plain division shows what's going on and is just as correct.
For one asset's full year-by-year table and other methods, see how to calculate depreciation. The register needs one more thing that a single schedule doesn't: depreciation to an exact date.
Result: 5200
5,200 a year for the delivery van: (32,000 - 6,000) / 5.
Months in service and accumulated depreciation, capped at the end of life
DATEDIF with "m" counts complete months between two dates. The van was bought on 1 April 2022, so by 31 December 2026 it has 56 complete months in service. The IF returns 0 for anything bought after the as-of date, because DATEDIF returns #NUM! when the start date is later than the end date. For disposed assets, the count stops at the disposal date.
Accumulated depreciation is the monthly amount x months, but never more than the whole life. MIN(J4, F4*12) is the cap. The large-format printer has been in use for 70 months on a 60-month life. Without the cap it would show 19,833 of depreciation on a printer that can only depreciate 17,000 (18,500 cost - 1,500 salvage), and its book value would fall below salvage.
Result: 17000
17,000: the printer is fully depreciated down to its 1,500 salvage value, and stays there however many more months pass.
My policy counts the month of purchase as a full month. How do I change it?
DATEDIF counts complete months, so the vinyl cutter, bought on 20 August, has 4 months by 31 December. To count the purchase month in full, use the calendar-month difference plus 1 instead. Which convention to use (full month, half month, half year) is an accounting policy, so match your accountant's.
Result: 5
5 months for the vinyl cutter (August to December counted in full), instead of DATEDIF's 4.
Book value and this year's charge
Book value is cost minus accumulated depreciation: 32,000 - 24,266.67 = 7,733.33 for the van at 31 December 2026. It's an accounting number, not what the van would sell for.
The depreciation expense for a year is the accumulated figure at the end of the year minus the accumulated figure at the start. For the printer, 2026 only carries 2 more months of depreciation before it reaches the end of its life: 566.67, not a full year's 3,400. A register that just multiplies the annual figure by 1 for every year keeps charging depreciation on assets that are already fully written down.
Result: 566.66666667
566.67: the printer's 2026 depreciation, 2 months at 283.33 before its 60-month life ran out.
Disposals: keep the row, add the date
When an asset is sold or scrapped, don't delete it. Fill in the disposal date and any sale proceeds. The months count stops at the disposal date, and M shows the gain or loss: proceeds - book value at disposal. The old laptop was fully depreciated to 0 and sold for 150, a gain of 150.
Keeping the row gives you an audit trail and lets you see what was sold when. How a gain or loss is reported is a question for your accountant; the register just gives them the numbers.
Result: 150
150: the old laptop sold for 150 with a book value of 0.
Totals for the balance sheet
The balance sheet needs the cost of the assets you still hold and their accumulated depreciation. SUMIFS with "" as the condition adds only rows where the disposal date is blank: 70,300 of cost (the 72,100 total minus the sold laptop) and 45,173.81 of accumulated depreciation, for a net book value of 25,126.19.
The check in P6 compares the net figure with the sum of the individual book values for assets held; it should be 0. These two totals feed the equipment and accumulated depreciation lines of a balance sheet, and the year's depreciation is the add-back at the top of a cash flow statement.
Result: 70300
70,300: the cost of every asset still held. The sold laptop's 1,800 is left out.
Common mistakes
No cap at the end of the asset's life
Use MIN(months, life x 12). Without it, depreciation runs past cost minus salvage and book value goes below salvage, or negative.
DATEDIF errors for assets bought after the as-of date
DATEDIF returns #NUM! if the start is later than the end. Wrap it in IF(purchased > as-of, 0, ...).
Deleting disposed assets
Add a disposal date and proceeds instead. Totals for the balance sheet use SUMIFS on a blank disposal date.
Charging a full year's depreciation every year
Take the year's charge as accumulated at year end minus accumulated at the start, so part years and fully depreciated assets come out right.
FAQ
What should a fixed asset register include?
At minimum: an asset ID, description, purchase date, cost, salvage value, useful life, accumulated depreciation, book value, and a disposal date and proceeds. Many registers add location, serial number and the person responsible.
How do I calculate accumulated depreciation to a date in Excel?
Count complete months with DATEDIF(purchase date, as-of date, "m"), then multiply the monthly depreciation by MIN(months, life x 12) so it stops at the end of the useful life.
Can I use the SLN function instead?
Yes, SLN(cost, salvage, life) gives the same annual straight-line amount as (cost - salvage) / life. You still need the months and the cap to get the figure at a date.
What happens to a fully depreciated asset that's still in use?
It stays in the register at its salvage value (or 0) with no further depreciation, and counts in both cost and accumulated depreciation until it's disposed of.
Functions used
DATEDIFWhole years, months or days betweenMINSmallest numberIFPicks a result from a testSUMIFSAdds with several conditionsROUNDRounds to digitsYEARYear of a dateMONTHMonth numberDATEBuilds a date
Related guides
- How to calculate the asset turnover ratio in Excel
- How to calculate depreciation in Excel
- How to make a balance sheet in Excel
- How to make a cash flow statement in Excel
- How to calculate EBITDA in Excel
- Excel formulas cheat sheet: the 25 everyone should know
Updated