Straight-Line Depreciation Schedule (Excel Example)

By Gregg Hopfeldt · 2025-05-19

This straight-line depreciation schedule Excel example provides a clear, repeatable framework for calculating equal depreciation charges across an asset's useful life. It gives finance teams a defensible starting point for period-by-period tracking that aligns with GAAP and IFRS requirements.

What is Straight-Line Depreciation?

Straight-line depreciation assumes equal value consumption over an asset's useful life, resulting in a consistent expense each period. This method spreads the asset's cost evenly, producing a predictable charge that is simple for auditors to verify and difficult to dispute.

Purpose

A straight-line depreciation schedule makes your fixed asset records more defensible by establishing consistent, equal depreciation charges that are straightforward to verify and difficult to dispute during audit review.

The Logic

Under the straight-line method, depreciation expense remains constant each period. The calculation logic is simple: annual depreciation equals asset cost divided by useful life (or cost multiplied by the depreciation rate). For monthly reporting, the annual charge is divided by twelve.

This method satisfies the accounting principle of systematic and rational allocation. The asset's cost is spread evenly over its useful life, matching expense to the periods that benefit from the asset's use. There is no acceleration or deceleration of charges—just a stable, predictable pattern that auditors can trace without effort.

An Excel-based straight-line depreciation schedule is safer than ad hoc spreadsheet adjustments because the calculation logic is explicit, repeatable, and visible. Each row shows the same charge each period, making it easy to spot formula errors or unauthorised edits. This reduces the risk of formula drift and maintains audit trail integrity.

In my experience, formula drift is the silent killer of fixed asset registers. One accidental copy-paste that includes a hard-coded number instead of a cell reference can throw off an entire year's tax return.

While Excel is a reliable starting point for a few assets, it eventually becomes a liability as your register grows. We built Simple Fixed Assets to take this exact straight-line logic and lock it behind a verified system, so you never have to worry about a broken cell reference again.

Straight-Line Depreciation Schedule Excel Example

The following example shows how a straight-line depreciation schedule is structured for a single asset. You can copy this layout into your own workbook or adapt it for multiple assets.

Asset Details

  • Asset Identifier: VEH-001

  • Category: Motor Vehicles

  • Cost: 30,000

  • Useful Life: 5 years

  • Depreciation Rate: 20% per annum (Straight Line)

  • Financial Year Start: 1 July

Annual Schedule

| Year | Opening Book Value | Annual Depreciation | Accumulated Depreciation | Closing Book Value |

|--------|--------------------|---------------------|--------------------------|--------------------|

| Year 1 | 30,000 | 6,000 | 6,000 | 24,000 |

| Year 2 | 24,000 | 6,000 | 12,000 | 18,000 |

| Year 3 | 18,000 | 6,000 | 18,000 | 12,000 |

| Year 4 | 12,000 | 6,000 | 24,000 | 6,000 |

| Year 5 | 6,000 | 6,000 | 30,000 | 0 |

Calculation: Annual depreciation = Cost × Rate = 30,000 × 20% = 6,000 per year. Each year, the same amount is charged, and accumulated depreciation increases by 6,000 until book value reaches zero.

Monthly Schedule (Year 1 Example)

| Month | Monthly Depreciation | Year-To-Date Depreciation | Accumulated Depreciation | Book Value |

|----------------|----------------------|---------------------------|--------------------------|------------|

| Month 1 (Jul) | 500 | 500 | 500 | 29,500 |

| Month 2 (Aug) | 500 | 1,000 | 1,000 | 29,000 |

| Month 3 (Sep) | 500 | 1,500 | 1,500 | 28,500 |

| ... | ... | ... | ... | ... |

| Month 12 (Jun) | 500 | 6,000 | 6,000 | 24,000 |

Calculation: Monthly depreciation = Annual depreciation ÷ 12 = 6,000 ÷ 12 = 500 per month. The financial year start date determines which calendar month corresponds to Month 1.

Steps to Build Your Own Schedule

  1. Create a new workbook. Open a blank Excel file and set up columns for Asset Identifier, Category, Cost, Useful Life (Years), Depreciation Rate, Annual Depreciation, Accumulated Depreciation, and Book Value.

  2. Enter asset details. Input the asset identifier, category, cost, and useful life. Use your fixed asset register as the source to ensure consistency.

  3. Calculate annual depreciation. In a dedicated column, enter the formula =Cost*Rate. For the example above, this is =30000*20% which returns 6,000. This formula should be identical for each year of the asset's life.

  4. Build the annual schedule. Create rows for each year of useful life. The Opening Book Value for Year 1 is the asset cost. For subsequent years, Opening Book Value equals the previous year's Closing Book Value. Accumulated Depreciation increases by the annual charge each year. Closing Book Value equals Cost minus Accumulated Depreciation.

  5. Build the monthly schedule (optional). If you require monthly granularity, add twelve columns for Month 1 through Month 12. Each month's depreciation equals Annual Depreciation ÷ 12. Year-to-Date Depreciation is the running total, and Accumulated Depreciation adds any prior-year balance.

  6. Lock formula cells. Protect the calculation logic by locking cells that contain formulas. This prevents accidental edits and maintains the stability of the schedule.

  7. Reconcile to your general ledger. At year-end, confirm that total depreciation expense and accumulated depreciation in the schedule agree with your accounting records. Document the reconciliation for audit purposes.

  8. Archive with a clear naming convention. Save the completed workbook with a filename that includes the asset identifier and financial year (e.g., Straight_Line_Schedule_VEH-001_2024-25.xlsx). Store in a controlled location accessible for review.

Outcome

A stable, verified straight-line depreciation schedule with equal charges across all periods. Each asset shows cost, accumulated depreciation, and book value in a Linen-clean format that auditors can trace without effort. The schedule reconciles to the general ledger, with no ghost assets, no formula drift, and an unshakeable audit trail—quietly confident and defensible.

Already have a schedule? Import your existing template into Simple Fixed Assets.

Stay Depreciated,

Gregg

Related Resources