How to Keep a Fixed Asset Register in Excel

A fixed asset register in Excel is a list of everything your business owns and uses for more than a year, such as laptops, vans and machinery, with one row per asset. Each row holds the facts (purchase date, cost, useful life) and a few formulas that work out months in service, depreciation to date and net book value. This guide builds a small register step by step, for bookkeepers, office managers and anyone practising for an accounting job test.

The depreciation method, the useful lives and the cost above which an item counts as an asset are policy choices. The numbers below are an example; follow your company's accounting policy and your accountant's advice for real records.

What goes in a fixed asset register?#

A register needs enough detail to find each asset and to value it. These columns cover both:

Type these as inputs and don't overwrite them with formulas. Turn the range into an Excel table with Ctrl+T (Mac: ⌘T) so new rows pick up the formulas automatically. Keeping track of who has an asset on loan is a different job; see the equipment checkout tracker for that.

The example register#

Here are six assets. The report date, 31 Dec 2026, sits in B1, and the table headers are in row 3, so the first asset is in row 4. The calculated columns are explained in the next sections.

The table runs from column A (ID) to column J (Net book value).

Months in service with DATEDIF#

DATEDIF counts complete months between two dates. In H4, type:

=DATEDIF(D4,$B$1,"m")

For the laptop, bought on 15 March 2024, it returns 33 at 31 December 2026. The $ signs lock the report date, so every row measures to the same day when you fill down. Change B1 at each month end and the whole register updates.

This example counts only complete months. Some companies charge a full month in the month of purchase, or start the month after; adjust the formula to match your policy. DATEDIF doesn't appear in Excel's function list or autocomplete, but it works in every version. The DATEDIF function guide covers its other units, and the days between dates lesson practises date differences. If you prefer years with decimals, =YEARFRAC(D4,$B$1) gives them; see how YEARFRAC works.

Depreciation to date and net book value#

Straight-line depreciation spreads cost minus salvage evenly over the useful life. Work it out monthly, multiply by months in service, and cap it so an old asset never goes below its salvage value. In I4:

=MIN((E4-G4)/F4/12*H4,E4-G4)

For the forklift: (18,000 − 2,000) / 8 / 12 = 166.67 a month, times 67 months, gives 11,166.67. The MIN cap only matters once an asset is fully depreciated, but without it a 10-year-old laptop would show a negative value.

Net book value (NBV) is what remains on the books. In J4:

=E4-I4

The forklift's NBV is 18,000 − 11,166.67 = 6,833.33. For yearly figures, declining-balance methods and the SLN function, see our full guide to depreciation in Excel. Note that tax rules often use different methods and lives from your accounts, so many businesses keep separate columns for each.

Across all six assets, cost is 64,600, accumulated depreciation 40,479.17 and net book value 24,120.83.

Totals by category with SUMIF#

SUMIF turns the register into the summary a manager or auditor asks for. Put the categories in a small table, say L4:L7, and type in M4:

=SUMIF($C$4:$C$9,L4,$E$4:$E$9)

Fill it down. IT shows 10,400 (1,500 + 8,000 + 900). Point the last range at column J instead to total net book value: IT comes to 3,712.50. The SUMIF function guide and our article on how SUMIF adds matching numbers cover the syntax, and the SUMIF lesson lets you practise it.

Once assets are disposed of, add a second condition so they drop out of the active totals:

=SUMIFS($E$4:$E$9,$C$4:$C$9,L4,$K$4:$K$9,"In use")

Here column K is Status. Keep disposed assets in the register with their disposal date and proceeds rather than deleting the rows, so the history stays complete.

Checks that keep the register reliable#

A register is only useful if it agrees with the accounts. Add a few checks:

1. Duplicate IDs: =COUNTIF($A$4:$A$9,A4)>1 returns TRUE for any repeated ID.
2. Missing inputs: =COUNTBLANK(D4:G4) should be 0 on every row.
3. Fully depreciated assets: =IF(H4>=F4*12,"Fully depreciated","") flags assets still in use after their useful life.
4. Reconcile: total cost and total accumulated depreciation should match the fixed asset balances in your general ledger.

These are the same habits a budget or job test checks: inputs in one place, formulas that fill down, and totals you can prove. The budget practice lesson works the same way, and what Excel tests for accountants cover lists the related skills.

Practise it#

Start with the SUMIF exercise on this page. Then practise SUMIF in a live spreadsheet, practise date differences in the days between dates lesson, and try the department budget practice to combine totals and formulas. Each lesson checks your answer instantly. For a timed run, the finance Excel mock test covers similar tasks.