Excel Formula for Depreciation Calculation

Formulas & functions · Updated November 22, 2026

The core Excel formula for straight-line depreciation is =(cost - salvage) / life, where cost is the asset’s purchase price, salvage is its value at the end of its useful life, and life is the number of years it will be used. For example, if a machine costs $10,000, has a $1,000 salvage value, and a 5-year life, the annual depreciation is =(10000-1000)/5, which returns $1,800 per year.

Excel also has built-in functions for more complex methods: SLN (straight-line), SYD (sum-of-years-digits), DB (declining balance), and DDB (double-declining balance). Each serves a different accounting or tax purpose, and this article explains when to use which, how to set them up correctly, and common pitfalls to avoid.

Why Depreciation Matters in Spreadsheets

Depreciation allocates the cost of a tangible asset over its useful life, matching expenses to the revenue the asset helps generate. In Excel, you can track this manually or with functions, but the key is that depreciation is a non-cash expense—it reduces reported profit without an actual cash outflow. Businesses use it for financial statements, tax deductions, and budgeting for asset replacement.

For a small business owner, knowing the formula matters because it directly affects net income and taxable income. The IRS has specific rules (like MACRS) that differ from GAAP accounting, so the Excel formula you use depends on whether you are reporting to shareholders or to the tax authority. For tax purposes, you may need a separate schedule; Excel’s built-in functions are primarily for book depreciation.

The Straight-Line Method (SLN Function)

The simplest and most common method is straight-line, which spreads depreciation evenly. Excel’s SLN function does this automatically: =SLN(cost, salvage, life).

Example: with cost in A1 (10000), salvage in B1 (1000), and life in C1 (5), the formula =SLN(A1,B1,C1) returns $1,800. The manual formula =(A1-B1)/C1 gives the same result. Use SLN when you want a clean, auditable function name, or the manual version if you prefer to see the arithmetic.

Straight-line is best for assets that provide equal value each year, like office furniture, buildings, or software licenses. It is also the easiest to explain to stakeholders.

Declining Balance and Double-Declining (DB, DDB)

Accelerated methods record more depreciation in early years, which is useful for assets that lose value quickly, like vehicles or computers. Excel’s DB function uses a fixed rate: =DB(cost, salvage, life, period) where period is the year number (1, 2, 3…).

The DDB function uses double-declining balance: =DDB(cost, salvage, life, period, [factor]). The default factor is 2 (double). For a $10,000 asset with $1,000 salvage over 5 years, =DDB(10000,1000,5,1) returns $4,000 in year 1, then $2,400 in year 2, and so on. Note that DDB may not reach the salvage value exactly in the last year; you may need to switch to straight-line or use the VDB function to handle the transition.

Use DB when you need a fixed-rate declining balance, and DDB when you want double-declining specifically. Both require the period argument—a common mistake is omitting it.

Sum-of-Years-Digits (SYD Function)

The sum-of-years-digits method is another accelerated option, but it uses a decreasing fraction each year. Excel’s SYD function is: =SYD(cost, salvage, life, period).

For the same $10,000 asset, salvage $1,000, life 5 years, the sum of years is 1+2+3+4+5=15. Year 1 depreciation is =(10000-1000)*(5/15) = $3,000. In Excel, =SYD(10000,1000,5,1) returns $3,000. Year 2 returns $2,400, and so on.

SYD is less common than DDB but is still used in some industries. It is simpler than DDB because it never requires a switch to straight-line—it always reaches salvage exactly at the end.

Choosing the Right Method for Your Data

Pick the method based on the asset’s pattern of economic benefit and the reporting standard you follow:

  • Straight-line (SLN): even benefit over time, simplest, most common for book accounting.
  • Double-declining (DDB): rapid early write-off, good for tech assets, but may require a switch to straight-line.
  • Sum-of-years (SYD): accelerated but smoother than DDB, reaches salvage exactly.
  • Declining balance (DB): custom fixed rate, useful when you want a specific percentage.

For tax purposes in the U.S., MACRS is required, which Excel does not have a built-in function for—you would need a lookup table or a custom formula. Many businesses use the VDB function (variable declining balance) to mimic MACRS with a switch to straight-line, but it is not a perfect match.

Common Errors and How to Fix Them

The most frequent mistakes in depreciation formulas are:

  • Negative salvage: if salvage exceeds cost, the result is negative. Ensure salvage is less than cost.
  • Life as zero: dividing by zero returns #DIV/0!. Life must be a positive number.
  • Period out of range: for SYD and DDB, period must be between 1 and life. Using 0 or life+1 returns an error.
  • Using dates instead of years: SLN expects the life in years, not a date. If you have a start date and end date, calculate years as =(end-start)/365.
  • Forgetting the factor in DDB: the optional factor defaults to 2, but if you want 150% declining balance, you must specify =DDB(cost,salvage,life,period,1.5).

To check your work, sum all depreciation amounts across periods—it should equal cost minus salvage. For example, with SLN over 5 years, 5*1800=9000, which is 10000-1000.

Depreciation with Partial Years

Assets are rarely purchased on January 1. For a mid-year purchase, you need to prorate the first year’s depreciation. The simplest approach is to calculate the full-year amount, then multiply by the fraction of the year the asset was in service.

Example: an asset purchased on July 1, with a 5-year life and $1,800 annual straight-line depreciation. The first year’s depreciation is =1800*(6/12) = $900. For the last year, you would also get $900, and the remaining years get the full $1,800.

Excel’s built-in functions do not handle partial periods automatically. You must either adjust the life or use a separate formula for the first year. A common workaround is to use the VDB function with a start and end period in fractions, but it is easier to just calculate manually.

Frequently Asked Questions

What is the difference between SLN and straight-line manual formula? None—SLN(cost,salvage,life) is just a wrapper for (cost-salvage)/life. Use SLN for readability, especially in large models.

Can I use depreciation formulas for tax returns? No, not directly. The IRS requires MACRS, which has its own tables. Excel does not have a MACRS function; you would need to enter the percentages manually or use a third-party add-in. Always consult a tax professional.

Why does DDB not reach the salvage value? Because double-declining applies a constant rate to the declining book value, it asymptotically approaches zero. To force it to reach salvage, you must switch to straight-line in the year when straight-line depreciation is higher. Excel’s VDB function can do this automatically if you set the switch argument to TRUE.

How do I calculate depreciation for an asset with a residual value of zero? It works the same—set salvage to 0. For example, =SLN(5000,0,4) returns $1,250 per year. Just be aware that some tax rules require a minimum salvage value.

What if my asset’s life is in months, not years? Convert months to years by dividing by 12. For a 60-month life, use 5 in the life argument. Alternatively, you can use a monthly depreciation formula: = (cost-salvage)/months and then sum for the year.

Is there a way to see a depreciation schedule for all years at once? Yes, use the SEQUENCE function (Excel 365) to generate a list of periods, then wrap it in DDB or SYD. For example, with cost in A1, salvage in B1, life in C1, put =SEQUENCE(C1) in E1:E5, then in F1 enter =DDB($A$1,$B$1,$C$1,E1) and drag down. This gives a full schedule.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.