How to Calculate Fixed Asset Depreciation in Excel: UK Methods, Formulas, and Tax Implications

Depreciation is the systematic allocation of a fixed asset's cost over its useful economic life. When a UK business buys a piece of equipment for £50,000 that will last ten years, recording the entire £50,000 as an expense in year one would overstate costs and understate profit. Instead, the cost is spread across the asset's life, matching the expense to the revenue it helps generate — a core principle of accrual accounting under FRS 102, the UK's primary accounting standard for small and medium enterprises.

For UK businesses, depreciation serves three practical purposes. First, it gives a more accurate picture of annual profits by spreading capital costs over time. Second, it helps business owners plan for asset replacement by tracking how much value has been consumed. Third, while accounting depreciation itself is not tax-deductible, understanding it is essential for reconciling your accounts with HMRC's capital allowances system, which determines the actual tax relief you receive on asset purchases.

The key distinction: depreciation is an accounting concept governed by FRS 102 (or IFRS for larger companies), while capital allowances are a tax concept governed by HMRC. They are calculated separately and will almost always produce different figures. Your accounts show depreciation; your corporation tax return shows capital allowances. Excel can handle both calculations, and this guide covers the accounting side in detail before explaining how the tax side differs. For more on UK business tax obligations, see our dedicated tax guide.

Straight-line depreciation is the simplest and most commonly used method in UK accounting. It allocates an equal amount of depreciation expense to each year of the asset's life. The formula is: (Cost − Residual Value) ÷ Useful Life.

In Excel, the built-in SLN function handles this automatically:

=SLN(cost, salvage, life)

A delivery van purchased for £35,000 with an expected residual value of £5,000 after seven years:

=SLN(35000, 5000, 7) returns £4,285.71 per year

To build a complete depreciation schedule in Excel, set up columns for Year, Opening Book Value, Depreciation Expense, Accumulated Depreciation, and Closing Book Value. In cell B2, enter the asset cost (£35,000). In cell C2, enter =SLN($B$2, 5000, 7). For D2, use =C2 (first year) or =D1+C2 (subsequent years). For E2, use =B2−D2. Copy these formulas down for seven rows.

Straight-line is the default choice for most UK SMEs because it is simple, predictable, and easy to audit. FRS 102 Section 17 requires that the depreciation method reflects the pattern in which the asset's future economic benefits are expected to be consumed — and for many assets (office furniture, fixtures, computer software), a constant annual charge is perfectly reasonable.

The reducing balance method applies a fixed percentage to the asset's remaining book value each year. This produces higher depreciation in early years and lower charges later — useful for assets that lose value rapidly at first, such as vehicles, computers, and technology equipment.

Excel's DB function calculates fixed declining balance depreciation:

=DB(cost, salvage, life, period)

Using the same £35,000 van with a £5,000 residual value over seven years:

• =DB(35000, 5000, 7, 1) returns £9,713 (Year 1)
• =DB(35000, 5000, 7, 2) returns £7,017 (Year 2)
• =DB(35000, 5000, 7, 3) returns £5,069 (Year 3)

The DB function automatically calculates the rate needed to reduce the asset from its cost to its salvage value over the specified life. In this case, the implicit rate is approximately 27.75% per year.

For UK businesses, the reducing balance method is particularly relevant because it mirrors the mechanics of HMRC's writing down allowances, which apply an 18% rate to the main pool or 6% to the special rate pool. While the percentages differ (accounting depreciation rates reflect economic reality, not tax rules), the mathematical structure is identical.

Excel also offers the DDB function for double declining balance depreciation, which uses twice the straight-line rate: =DDB(cost, salvage, life, period). For our van, =DDB(35000, 5000, 7, 1) returns £10,000. DDB is less common in UK practice but can suit assets with very rapid obsolescence.

Excel provides two additional depreciation functions that, while less commonly used in UK accounting, handle specific situations well.

The SYD function (sum-of-years' digits) is an accelerated method producing results between straight-line and reducing balance:

=SYD(cost, salvage, life, period)

For our £35,000 van: =SYD(35000, 5000, 7, 1) returns £7,500 (Year 1), while =SYD(35000, 5000, 7, 7) returns £1,071 (Year 7). The method weights each year in reverse order — year one gets 7/28 of the depreciable amount, year two gets 6/28, and so on (where 28 = 7+6+5+4+3+2+1).

The VDB function (variable declining balance) is the most flexible option:

=VDB(cost, salvage, life, start_period, end_period, factor, no_switch)

VDB is particularly useful for partial-period depreciation — when an asset is purchased midway through the financial year. =VDB(35000, 5000, 7, 0, 0.5) calculates depreciation for the first six months only. Under FRS 102, depreciation must start from the date the asset is available for use, so partial-year calculations come up constantly in practice.

For most UK small businesses, straight-line and reducing balance cover virtually all requirements. SYD and VDB appear more often in international group reporting or when matching depreciation profiles to specific asset usage patterns.

The most common misunderstanding among UK business owners is the relationship between depreciation and tax relief. Depreciation charged in your accounts is not tax-deductible. HMRC provides capital allowances instead — a separate system of tax relief on qualifying capital expenditure.

The key capital allowances rates for the 2026/27 tax year, per GOV.UK:

• Annual Investment Allowance (AIA): 100% relief on the first £1,000,000 of qualifying plant and machinery per year
• Full expensing: 100% first-year relief on qualifying main pool plant and machinery (permanent from April 2023, companies only)
• 40% first-year allowance: New from January 2026 — 40% relief on qualifying plant and machinery not covered by full expensing or AIA
• Writing Down Allowance (main pool): 18% reducing balance on plant, machinery, and vehicles
• Writing Down Allowance (special rate pool): 6% reducing balance on long-life assets, integral features, and high-emission cars
• Structures and Buildings Allowance: 3% straight-line on qualifying non-residential structures
• 100% first-year allowances: Electric cars and zero-emission vehicles (extended to April 2027), EV charging points

Here is what this looks like in practice. A UK limited company buys a £50,000 piece of equipment. The accounts depreciate it straight-line over five years (£10,000 per year). But HMRC allows the entire £50,000 as a tax deduction in year one through the AIA. At the 25% corporation tax rate (for profits above £250,000), that is a £12,500 cash tax saving immediately — instead of £2,500 per year over five years.

The 19% small profits rate (for companies with profits under £50,000) changes this calculation. A micro-business claiming the same £50,000 AIA saves £9,500 immediately rather than £12,500. Marginal relief applies between £50,000 and £250,000 profits. Know your effective rate before planning capital expenditure — a £50,000 purchase decision looks different at 19% versus 25%. If you're tracking how the 2026/27 tax year affects your overall tax position, capital allowances are one lever the government hasn't frozen.

To model both in Excel, create two parallel schedules: one for accounting depreciation (using SLN or DB) and one for capital allowances. For the capital allowances schedule, if the asset falls within the AIA, the calculation is trivial — 100% in year one. For writing down allowances, use the reducing balance formula: =opening_pool_value * 0.18 for the main pool. The difference between the two schedules creates a timing difference that must be tracked for deferred tax purposes.

After reviewing hundreds of small business spreadsheets, these errors cost the most money.

1. Using depreciation figures on the tax return. Depreciation is added back to profits for tax purposes. Only capital allowances reduce your taxable profits. If your accountant has not made this adjustment, you are either overpaying or underpaying tax — neither ends well with HMRC.

2. Forgetting to review useful life estimates annually. FRS 102 requires you to reassess both the useful life and residual value of assets at each reporting date. A laptop originally depreciated over five years that becomes obsolete after three needs adjusting. In Excel, create a review column beside your schedule and update your SLN or DB formulas when circumstances change.

3. Not claiming AIA on year-end purchases. The AIA timing rules are strict: the date of purchase is when you sign the contract (if payment is due within four months) or when payment is due (if later than four months). A machine ordered in March with payment due in June falls into the next accounting period. Miss this, and you lose an entire year of tax relief.

4. Depreciating land. Land does not depreciate under FRS 102 — it has an indefinite useful life. If you buy a commercial property for £300,000 where the land element is £100,000, only the £200,000 building component gets depreciated. Separate the land value from day one. The Structures and Buildings Allowance at 3% straight-line may apply to the building element — a separate claim from the standard capital allowances.

5. Ignoring Making Tax Digital. MTD is now live for sole traders and landlords with income above £50,000 from April 2026, with the £30,000 threshold following in April 2027. Your Excel depreciation schedule still works for calculations, but the figures need to flow into MTD-compatible accounting software for quarterly HMRC submissions. If you haven't set up this integration, read our Making Tax Digital guide — the deadline has already passed.

FRS 102 says the method should reflect how the asset's economic benefits are consumed. In practice:

Use straight-line (SLN) for:

• Office furniture and fixtures (5–10 years)
• Leasehold improvements (lease term or useful life, whichever is shorter)
• Computer software (3–5 years)
• Building plant where usage is constant

Use reducing balance (DB) for:

• Vehicles (3–5 years, commonly 25% DB rate)
• Computer hardware (3–4 years)
• Mobile plant and equipment subject to heavy early wear
• Any asset whose resale value drops sharply in year one

Use VDB when:

• An asset is purchased partway through your accounting period
• Switching from declining balance to straight-line mid-life is required
• Group reporting requires a specific depreciation profile

Two final considerations. First, whatever method you choose for accounts, your capital allowances claim is a completely separate decision. Most qualifying expenditure under £1,000,000 should go through the AIA for maximum immediate tax relief — and from January 2026, the new 40% first-year allowance gives an additional option for qualifying plant and machinery above the AIA threshold. Second, sole traders on cash basis accounting skip depreciation entirely for most assets — the purchase price is claimed as an expense when paid. The exception is cars, which must still go through capital allowances regardless of accounting basis.

For the self-assessment tax return, you will add back depreciation and deduct capital allowances. If these two figures differ significantly — and they usually do — the timing difference creates a deferred tax balance that needs tracking. Build both schedules in Excel, keep them side by side, and your year-end tax computation becomes straightforward. For an overview of all your 2026/27 tax-free allowances, capital allowances sit alongside ISA, pension, and CGT thresholds as part of your annual tax planning.

This article is for informational purposes only and does not constitute financial advice. You should seek independent financial advice before making any investment decisions.