What's the Simplest Way to Build a Fixed Asset Register in Excel — and Where Does It Usually Break Down?

By Gregg Hopfeldt · 2026-09-28

The simplest version is a single spreadsheet with one row per asset and columns for description, purchase date, cost, useful life, depreciation, accumulated depreciation, and current book value. You can include any other columns you feel are relevant to your business. Formulae in the depreciation, accumulated depreciation, and book-value columns recalculate every time something changes. That's really all it takes to get started — and it's also where most registers start falling apart within a year or two, usually around the same place: formula drift as rows get inserted or deleted, no audit trail when someone edits a historical figure, and no clean way to roll over from one financial year to the next.

Building the basic version

A working Excel register needs four things:

  1. One row per asset, with a description, purchase date, cost, useful life, depreciation, accumulated depreciation, and current book value.

  2. A depreciation method column — this can be as simple as the words "reducing" (a fixed percentage of the remaining book value each year) or "straight line" (cost ÷ useful life) to indicate the method used.

  3. A formula that calculates accumulated depreciation and current book value, typically driven off the purchase date, cost, depreciation method, and end date.

  4. A disposal flag or column, so a sold or scrapped asset can be excluded from active totals without deleting its history.

That's enough to produce a defensible depreciation schedule for a handful of assets. This works fine for a while, but the trouble starts as the sheet grows.

Where it usually breaks down

  • Formula drift. Inserting a row in the middle of the range, or dragging a formula down incorrectly, silently breaks the calculation for some assets while leaving others intact. It's rarely obvious until the totals don't reconcile, and then finding the mistakes is tedious and frustrating.

  • No period lock. Nothing stops last year's closed figures from being edited by accident. Once that happens, there's no way to prove to an auditor what the numbers were at year-end.

  • Manual year-end rollover. Carrying forward opening balances into a new year is often done manually, which is a common source of errors, because copying a formula instead of its resulting value could trigger the calculation from the wrong date.

  • Disposals handled inconsistently. Some people delete the row (losing the history), some zero it out, some leave it in the active total by mistake.

  • No audit trail. Excel doesn't log who changed a rate, a useful life, or a cost figure, or when — so when the numbers don't match the general ledger, there's no way to trace why.

  • Scale. A spreadsheet that works cleanly at 40 assets could become unreliable after that, simply because of the volume of manual review needed to catch the errors above before they compound.

None of these are Excel's fault — they're the natural result of using a general-purpose spreadsheet for something that really wants period locking, calculation consistency, and a change log, none of which spreadsheets do natively.

FAQ

Can Excel handle depreciation calculations accurately? Yes, for straight-line and reducing-balance methods the formulas are simple and accurate. The risk isn't the math, it's maintaining the formulas correctly across hundreds of rows and multiple years without something breaking.

How do I prevent formula drift? Use structured Excel Tables (Insert → Table) rather than plain ranges, since formulas auto-extend to new rows more reliably. It reduces the problem but doesn't eliminate it — there's still no lock preventing someone from overwriting a formula with a hard-coded value.

What's the biggest audit risk with a spreadsheet register? The lack of a change history. If a reducing-balance rate was 20% in March and 15% in November with no record of when it changed, that's a finding waiting to happen in any audit.

At what point should a business move off Excel? There's no fixed number, but once disposals, multiple depreciation methods, and more than one person editing the file are all happening at the same time, the manual-review burden usually outweighs the convenience of staying in a spreadsheet. A spreadsheet may seem easy and economical — but it won't seem economical when you're spending hour after hour trying to find mistakes, or worse, paying someone else to find the mistakes. Whenever that time comes, you can skip the spreadsheet entirely.