How to Calculate Depreciation in Excel - Practical Guide

Learn to calculate asset depreciation in Excel using straight-line, declining balance, and sum-of-years-digits methods. This tutorial covers setting up depreciation schedules, applying formulas, and tracking asset values over time—essential for accurate financial reporting and tax compliance.

Why This Matters

Depreciation calculations are critical for financial statements, tax deductions, and asset management. Mastering Excel automation saves time and ensures accuracy in accounting workflows.

Prerequisites

•Basic Excel skills (cell entry, formulas, copying)
•Understanding of asset cost, salvage value, and useful life concepts

Step-by-Step Instructions

Set up the depreciation table

Create column headers in Excel: Asset Name (A1), Cost (B1), Salvage Value (C1), Useful Life Years (D1), Year (E1), Annual Depreciation (F1), Accumulated Depreciation (G1), Book Value (H1).

Calculate straight-line depreciation

In cell F2, enter formula: =(B2-C2)/D2 to calculate annual depreciation expense. Copy down for all years in the asset's life.

Calculate accumulated depreciation

In cell G2, enter =F2 for year 1. In G3, enter =G2+F3 and copy down to sum depreciation each year.

Calculate book value

In cell H2, enter =B2-G2 to show remaining asset value after depreciation. Copy down for all years.

Apply alternative depreciation methods (optional)

For declining balance: Use formula =H1*rate (e.g., =H1*0.2). For sum-of-years: Use =(remaining life/sum of years)*depreciable base.

Alternative Methods

Using SYD function (Sum-of-Years-Digits)

Apply =SYD(cost, salvage, life, period) for accelerated depreciation favoring early years. Available in Excel 2016 and later versions.

Using DB function (Declining Balance)

Use =DB(cost, salvage, life, period, [month]) for declining balance method with optional month parameter for partial-year calculations.

Using SL function (Straight-Line)

Apply =SL(cost, salvage, life) for simple straight-line depreciation in a single formula.

Tips & Tricks

✓Always validate that salvage value doesn't exceed asset cost to avoid negative depreciation.
✓Use absolute references ($B$2) for cost to prevent formula errors when copying across periods.
✓Create a separate sheet for each asset class to organize complex depreciation schedules.
✓Round depreciation amounts to 2 decimal places for currency consistency using =ROUND(formula, 2).

Pro Tips

★Build a data validation dropdown to switch between depreciation methods dynamically using IF statements.
★Use named ranges (Formulas > Define Name) for cost and salvage values to make formulas more readable.
★Create a pivot table to summarize total depreciation by asset category for reporting efficiency.

Troubleshooting

Book value becomes negative before salvage value

Check that useful life is correct and salvage value is less than cost. Use MIN formula: =MAX(H2-F2, C2) to prevent negative values.

Accumulated depreciation formula shows #REF! error

Verify that column references are correct and rows haven't been deleted. Re-enter formula referencing the correct previous year cell.

Depreciation calculations differ from prior year spreadsheet

Confirm asset cost, salvage value, and useful life match records. Check for rounding differences and recalculate with consistent decimal places.

Related Excel Formulas

SL DB SYD SUM IF ROUND

Frequently Asked Questions

What's the difference between book value and market value?

Book value is calculated using depreciation formulas in accounting and reflects asset value on financial statements. Market value is the actual selling price in the marketplace. They often differ significantly, especially for real estate or equipment.

Can I change depreciation methods mid-year?

Generally, no—tax authorities and accounting standards require consistent depreciation methods. Changing methods mid-stream requires disclosure and may trigger audit complications. Consult your accountant before making changes.

Should I use straight-line or accelerated depreciation?

Straight-line is simpler and most common for financial reporting. Accelerated methods (declining balance, sum-of-years) are often used for tax purposes. Your choice depends on asset type, business requirements, and tax strategy.

How do I handle asset disposal or sale in the depreciation schedule?

Stop depreciation on the disposal date and calculate gain or loss (sales price minus current book value). Document the transaction separately and adjust accumulated depreciation accordingly.