Excel Depreciation Templates

Excel's Four Built-In Depreciation Functions: Syntax and Use Cases

Excel ships with four dedicated depreciation functions, each suited to a different accounting method. SLN(cost, salvage, life) computes straight-line depreciation: =SLN(50000, 5000, 10) returns $4,500 per year for a $50,000 asset with $5,000 salvage value over 10 years. DDB(cost, salvage, life, period, [factor]) calculates declining balance depreciation; omitting the factor parameter defaults to 2.0 for double-declining. =DDB(50000, 5000, 10, 1) returns $10,000 for year one (20% of $50,000). SYD(cost, salvage, life, per) uses the sum-of-years-digits method: =SYD(50000, 5000, 10, 1) returns $8,182 for year one (10/55 × $45,000). VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]) is the most flexible function, computing depreciation for any span of time and optionally switching to straight-line when SL produces a larger deduction — exactly replicating how MACRS works in practice.

Building a Complete Depreciation Schedule in a Spreadsheet

A professional asset depreciation schedule in Excel uses five columns per asset: Year, Beginning Book Value, Depreciation Expense, Accumulated Depreciation, and Ending Book Value. Row 1 headers; Row 2 starts with the purchase cost in the Beginning column. The Depreciation Expense cell uses =DDB($B$1,$B$2,$B$3,A4) (where B1=cost, B2=salvage, B3=life), referencing the row number as the period. The Accumulated Depreciation column uses a running SUM($C$2:C4) formula. The Ending Book Value simply subtracts accumulated depreciation from original cost. Copying these rows down for each year of the asset's life builds the complete schedule automatically. For MACRS compliance, use the VDB function with the applicable MACRS percentages or a VLOOKUP against an IRS percentage table to apply the correct convention adjustments.

Advanced VDB Techniques for Mid-Year Acquisitions and Method Switching

The VDB function's no_switch parameter is its most powerful and least understood feature. When no_switch = FALSE (the default), VDB automatically switches from declining balance to straight-line in the year where straight-line produces a higher deduction — replicating the IRS MACRS switch built into the MACRS percentage tables. When no_switch = TRUE, the function stays on declining balance for the entire recovery period, which is appropriate for pure book depreciation without the IRS switch. For partial-year calculations, VDB handles fractional periods naturally: =VDB(50000, 0, 5, 0, 0.5) returns the depreciation for the first half-year of a 5-year asset — equivalent to the IRS half-year convention's year-one amount. Building a template that uses VDB with start_period and end_period driven by actual placed-in-service dates allows a single model to handle a diverse asset portfolio with mixed acquisition dates and remaining useful lives, producing MACRS-compliant schedules that would otherwise require manual lookups from IRS Publication 946 tables.