Cellwise Start free

Excel guide · 5 min read

How to build a fixed asset register in Excel

Quick answerList each asset with its purchase date, cost, salvage value and useful life. Count months in service to an as-of date with DATEDIF(purchased, as-of, "m"), then accumulated depreciation = (cost - salvage) / (life x 12) x MIN(months, life x 12), so it stops at the end of the asset's life. Book value = cost - accumulated depreciation. Leave disposed assets in the register with a disposal date and keep them out of the balance sheet totals.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1Asset IDDescriptionPurchasedCostSalvageLife (yrs)DisposedProceedsAnnual depreciationMonths usedAccumulatedBook value
2FA-001Delivery vanApr 1, 2022320006000552005624266.6666677733.3333333
3FA-002Design laptopSep 15, 2024240003800271800600
4FA-003Large-format printerFeb 1, 20211850015005340070170001500
5FA-004Office furnitureJun 10, 2025780030071071.4285714181607.14285716192.8571429
6FA-005Old laptopMay 1, 2021180003Nov 20, 20251506005418000
7FA-006Vinyl cutterAug 20, 202696006006150045009100

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.

=(D2-E2)/F2

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.

=(D4-E4)/(F4*12)*MIN(J4,F4*12)

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.

=(YEAR(P1)-YEAR(C7))*12+MONTH(P1)-MONTH(C7)+1

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.

=K4-(D4-E4)/(F4*12)*MIN(DATEDIF(C4,DATE(2025,12,31),"m"),F4*12)

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.

=IF(G6="","",H6-L6)

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.

=SUMIFS(D2:D7,G2:G7,"")

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

Related guides

Updated