Depreciation Schedule Excel Template (and When It Stops Working)

By Gregg Hopfeldt · 2025-05-19

A depreciation schedule in Excel provides a stable, defensible framework for tracking fixed asset depreciation—particularly useful for organizations beginning their asset register journey or maintaining informal records outside a structured system. This guide explains how to structure your own schedule and what to include for defensible record-keeping.

What is a Depreciation Schedule in Excel?

A depreciation schedule in Excel is a structured document that automates the calculation of an asset's declining value over time, ensuring that the 'boring math' remains consistent across the entire register. Rather than building formulas from scratch for each asset, a well-designed schedule provides pre-built logic that applies your chosen depreciation method uniformly—reducing the risk of calculation errors and making your records defensible under audit.

Why Use a Structured Depreciation Schedule?

Standardized schedules protect financial integrity by ensuring calculations remain consistent across asset classes—something DIY spreadsheets often lack. When depreciation logic varies from one asset row to the next, the resulting schedule becomes difficult to audit and impossible to defend. A verified structure removes that variability, locking in a calculation path that holds up under review.

Purpose

A depreciation schedule makes your fixed asset records more defensible by providing a consistent, repeatable structure for tracking cost, accumulated depreciation, and book value across your register.

The Logic

The depreciation schedule satisfies the accounting principle of systematic allocation under GAAP and IFRS. It spreads the cost of a tangible fixed asset over its useful life, matching expense to the periods that benefit from the asset's use. The schedule must be consistent, repeatable, and audit-ready—meaning every value traces back to a verified input, and no calculation logic shifts silently over time.

The schedule applies one of two depreciation methods:

  • Straight Line — Reduces the asset by an equal amount each year. Annual depreciation equals cost divided by useful life, or cost multiplied by the depreciation rate.

  • Diminishing/Reducing Balance — Applies a fixed percentage to the remaining book value each year, producing higher depreciation in earlier years and a declining charge thereafter.

This approach is safer than an ad-hoc spreadsheet built from memory because a structured schedule enforces column layout, locks formula cells, and separates inputs from calculations. A stable, verified schedule reduces the risk of formula drift—where copied rows reference incorrect cells or inserted rows break dependent formulas—and maintains a clear calculation path from cost to book value.

I've spent years reviewing fixed asset registers, and I can tell you that a spreadsheet is only as good as the person who last touched it. Most spreadsheets aren't 'broken' because the math is wrong; they're broken because a tired accountant accidentally deleted a row or overwrote a formula at 11 PM to make a reconciliation balance.

The Schedule Structure

A defensible depreciation schedule contains the following columns for each asset:

  • Asset Identifier — Unique reference for the asset within your register.

  • Category — Asset classification (e.g., Computer Equipment, Furniture and Fixtures, Office Equipment, Machinery, Buildings) that determines the depreciation rate and method.

  • Cost — Original acquisition cost.

  • Accumulated Depreciation — Total depreciation charged from acquisition to the start of the current period.

  • Depreciation Expense — The charge for the current period (annual or monthly).

  • Book Value — Cost minus accumulated depreciation at period end.

  • Depreciation Method — The method applied: Straight Line or Diminishing/Reducing Balance.

  • Depreciation Rate — The annual percentage rate assigned to the asset category.

  • Financial Year — The reporting period the schedule covers.

Steps to Build Your Own Schedule

  1. Create a new workbook. Open a blank Excel file and set up the columns described above. Reserve the top rows for header information such as your financial year start date and company name.

  2. Enter your financial year start date. Record the date in a dedicated cell at the top of the worksheet. This anchor determines the period boundaries for depreciation calculations.

  3. Enter asset identifiers and categories. Match each row to your fixed asset register. Category names must align strictly with your internal depreciation policies to remain defensible—this ensures the correct rate and method apply to each asset class.

  4. Enter cost and accumulated depreciation. Input the original cost for each asset and any depreciation already charged from prior periods. These values form the starting point for current-year calculations.

  5. Assign depreciation rate and method. Enter the annual depreciation rate and method for each asset category. Ensure the rate and method are consistent with your accounting policy.

  6. Build the depreciation formulas. For Straight Line, use =Cost / Useful Life or =Cost * Rate. For Diminishing/Reducing Balance, use =Opening Book Value * Rate. Enter these formulas consistently across all rows.

  7. Lock formula cells. Protect the calculation logic by locking cells that contain formulas. In Excel, select the formula cells, format them as locked, then protect the worksheet. This prevents accidental changes and maintains a stable structure.

  8. Reconcile period-end totals to your general ledger. Confirm that the accumulated depreciation and book value columns reconcile to your accounting records. Document the reconciliation in a separate worksheet or audit log.

  9. Archive the completed schedule. Save the workbook with a clear naming convention that includes the financial year (e.g., Depreciation_Schedule_2024-25.xlsx). Store the file in a controlled location accessible for audit review.

When the Spreadsheet Stops Working

A spreadsheet schedule serves a purpose—but only up to a point. As your asset register grows, the risks of maintaining it in Excel compound. What begins as a simple tracking tool can quietly become a liability.

Signs the Spreadsheet Is No Longer Defensible

  • Structural risk from manual edits. Every additional row, formula tweak, or copy-paste operation introduces the possibility of cascading errors that remain invisible until an auditor asks for your source data.

  • Untraceable changes and weak audit trails. Excel does not natively track who changed what, or when. A defensible register requires a clear record of modifications—something a flat file cannot provide.

  • Version control breakdown. Multiple saved copies with similar filenames create confusion about which version is current. Without a single source of truth, reconciliation becomes an exercise in detective work.

  • Formula exposure and accidental corruption. Even protected cells can be overridden. A single unlocked formula or an unintended drag-and-drop can invalidate months of calculations without any obvious warning sign.

The Transition Point

A schedule is a great servant but a terrible master. If you find yourself spending more time 'fixing' your Excel file than actually reviewing your assets, you've reached the boundary. Simple Fixed Assets was designed specifically for this moment—to move you from a fragile 'flat' document to a multi-dimensional register where your audit trail is locked and immutable.

Outcome

A stable, verified depreciation schedule with clear traceability from cost to book value. Each asset shows accumulated depreciation aligned with your accounting policy, calculation logic remains intact, and the schedule reconciles to the general ledger. The result is a quietly confident record—defensible under review, free of ghost assets, and audit-ready for the next period.

When the template stops working, move to a register that doesn't break.

Stay Depreciated,

Gregg

Related Resources

For a closer look at building a register in Excel and where it tends to fail, see What's the Simplest Way to Build a Fixed Asset Register in Excel — and Where Does It Usually Break Down?.