To calculate depreciation in Excel, enter an asset’s cost, salvage value, useful life, and period, then use a function such as =SLN(B2,B3,B4) for equal annual depreciation. Excel also provides SYD, DB, DDB, and VDB for accelerated or partial-period calculations; the right choice depends on the depreciation method you need to represent.
What depreciation measures
Depreciation allocates the cost of a tangible asset over the periods in which it is expected to be used. It is an accounting calculation, not a measurement of the asset’s current market price or the cash spent in a particular year. A business might depreciate equipment, vehicles, or machinery; land generally is not depreciated because it is not treated as having a finite useful life in the same way.
Four inputs commonly determine a simple depreciation schedule:
- Cost: The amount used as the asset’s depreciable cost basis.
- Salvage value: The estimated value remaining at the end of its useful life.
- Useful life: The number of periods over which the asset is depreciated.
- Period: The year or other period for which you want the depreciation expense.
In a basic calculation, depreciable basis is cost minus salvage value. For example, an asset costing $12,000 with an estimated $2,000 salvage value has a $10,000 depreciable basis. The depreciation method determines how that $10,000 is allocated over time.
Set up the Excel inputs
For the examples below, use this consistent layout in a Microsoft 365 workbook on Windows: put the asset cost of 12000 in B2, salvage value of 2000 in B3, useful life of 5 years in B4, and the period number in B5. These are illustrative figures, not a recommendation about how to value or account for an actual asset. Use positive numbers for cost, salvage value, life, and period.
Label column A so the sheet remains understandable: A2 can say Cost, A3 Salvage value, A4 Useful life in years, and A5 Period. You can format the input cells as currency or numbers using Home > Number. Formatting changes how a value appears, not the underlying calculation. If the asset has a different useful life or salvage estimate, replace the example inputs with the figures supported by your records and applicable accounting rules.
Calculate straight-line depreciation with SLN
Straight-line depreciation allocates the same amount to each full period. It is often the easiest method to understand and reconcile. With cost in B2, salvage value in B3, and useful life in B4, enter =SLN(B2,B3,B4) in a result cell. Excel returns 2000, or $2,000 per year, because ($12,000 − $2,000) ÷ 5 = $2,000.
To build a schedule, put period numbers 1 through 5 in C2:C6 and place the annual calculation in D2. With the same inputs in B2:B4, enter =SLN($B$2,$B$3,$B$4) in D2, then fill it down. The dollar signs keep the input references fixed as the formula is copied. Each period returns $2,000, so the five periods total $10,000 and the asset’s remaining book value reaches the $2,000 salvage value.
Excel’s SLN function takes cost, salvage, and life in that order. It does not take a period number because the result is identical in each full period. If you want to model a first or final year that covers only part of a year, a straight annual SLN result by itself does not capture that timing; use an appropriate partial-period approach and follow the relevant accounting policy.
Use SYD for sum-of-years’-digits depreciation
SYD is an accelerated method: it assigns more depreciation to earlier periods and less to later ones. It uses the asset’s depreciable basis and a fraction based on remaining useful life. For a five-year life, the denominator is 5 + 4 + 3 + 2 + 1, or 15.
With cost in B2, salvage value in B3, useful life in B4, and the desired period number in B5, enter =SYD(B2,B3,B4,B5). For period 1, set B5 to 1; Excel returns about $3,333.33: ($12,000 − $2,000) × 5 ÷ 15. For period 2, change B5 to 2 and the result is about $2,666.67: $10,000 × 4 ÷ 15. Those figures illustrate why SYD produces higher expense early in the asset’s life.
For a schedule, list periods 1 to 5 in a column and refer to the period cell in each row while keeping the cost, salvage, and life references fixed. Check that the total across all periods equals the depreciable basis, subject to rounding. Use period numbers within the useful life; a period outside the valid range can return an error.
Compare declining-balance methods: DB and DDB
DB calculates depreciation using a fixed declining-balance rate. Its optional fifth argument, month, specifies the number of months in the first year; if omitted, Excel assumes 12. With cost in B2, salvage value in B3, life in B4, and period in B5, enter =DB(B2,B3,B4,B5) for the default full-year assumption. For the example’s first year, Excel rounds the calculated rate to three decimal places, giving a rate of 0.301; $12,000 × 0.301 produces $3,612. The amount for later years is based on the remaining book value, rather than the original cost.
DDB uses the double-declining-balance method by default, applying a factor of 2 to the straight-line rate. With the same input layout and period in B5, enter =DDB(B2,B3,B4,B5). In period 1, the result is $4,800: $12,000 × (2 ÷ 5). In period 2, the remaining book value is $7,200, so the result is $2,880: $7,200 × (2 ÷ 5). DDB also accepts an optional factor if a different declining-balance rate is specified for the calculation.
DB and DDB do not return the same schedule. DB calculates and rounds a rate according to its method; DDB uses the specified factor. Both are declining-balance approaches, so early-period expenses are generally greater than under straight-line depreciation. Do not assume that one method is suitable simply because its result is larger in the first year.
Calculate a changing or partial period with VDB
VDB calculates depreciation over a specified interval and can combine declining-balance depreciation with straight-line depreciation. That makes it useful when a schedule needs to cover a range of periods or switch methods. Its syntax includes the cost, salvage value, life, start period, end period, optional factor, and optional no-switch setting.
With cost in B2, salvage value in B3, life in B4, and the interval start and end entered as 0 and 1, respectively, enter =VDB(B2,B3,B4,0,1). With the example inputs, the default factor is 2 and the result for the first year is $4,800. To calculate the next year, use start period 1 and end period 2; with those interval values, the result is $2,880. VDB permits fractional start and end periods, which can represent portions of a period when the chosen method and assumptions call for them.
By default, VDB can switch to straight-line depreciation when that produces a larger amount for the remaining schedule. Its optional no-switch argument can prevent that switch. For instance, the final arguments can be written as 2 and TRUE to use a factor of 2 and prevent switching. Decide whether a switch is appropriate before using that option: it changes the depreciation pattern and should match the method you intend to model.
Choose a method and check the schedule
The function name is not a substitute for deciding which method applies. Straight-line is a simple, even allocation; SYD, DB, and DDB accelerate depreciation; VDB offers interval-based calculations and a possible switch to straight-line. Accounting policies, the asset’s use, and applicable tax rules can require specific methods or conventions. Excel functions calculate what their inputs and method specify; they do not decide which treatment is permitted for a real set of books or a tax return.
Before relying on a schedule, check these points:
- Confirm that the cost, salvage estimate, useful life, and period numbering are supported by the records and assumptions you need to use.
- Make sure the period values are in the function’s required range and that useful life uses the same unit as the periods.
- Sum the periodic depreciation and compare it with the depreciable basis. Small differences can arise from displayed rounding; avoid rounding each period in the formula unless your method requires it.
- Track accumulated depreciation and book value separately if you need a full schedule. Book value is generally cost less accumulated depreciation, and should not fall below the salvage value in a conventional schedule.
- Keep a note of the method and assumptions next to the schedule so another reader can interpret the results.
These checks help catch common spreadsheet errors, but a mathematically consistent schedule does not by itself establish that the method or assumptions meet accounting or tax requirements. For actual reporting, verify applicable rules with a qualified accounting or tax professional.
Common Excel errors and misconceptions
A frequent mistake is mixing up salvage value and depreciable basis. Excel’s depreciation functions generally expect the salvage value as a separate argument, not the result of cost minus salvage. For the example, enter 2000 as salvage; do not enter 10000 in the salvage cell. The function accounts for the difference internally.
Another issue is entering a life of 5 while using a period number such as 12. For annual periods, use years 1 through 5. If you need monthly depreciation, a five-year life represents 60 monthly periods only if you deliberately set up the calculation and method on that basis; changing the period unit without changing the life and assumptions leads to misleading results.
Do not confuse depreciation expense with cash flow or a decline in resale price. It is an allocation calculation. Nor does a formula automatically implement every tax depreciation system: jurisdiction-specific rules, conventions, and asset classifications may not match these general functions. If you see #NUM! or another error, inspect the input values, period range, and argument order against the function’s requirements.
Finally, displayed cents can make a schedule appear not to sum exactly. For example, SYD amounts may show rounded to two decimal places while Excel retains more precision in the cells. Format results as currency for readability, and use a deliberate rounding policy only when it is required for the schedule you are preparing.
Frequently Asked Questions
Which Excel formula calculates depreciation?
For equal annual depreciation, use =SLN(cost,salvage,life), replacing each word with a cell reference or value. For accelerated depreciation, Excel also provides SYD, DB, DDB, and VDB. Choose the method that matches the calculation you need rather than treating the functions as interchangeable.
How do I calculate depreciation per month in Excel?
First establish whether your intended method and policy call for monthly periods. For a simple monthly straight-line schedule, you can calculate monthly expense by dividing the depreciable basis by the number of months in the useful life, or construct a monthly schedule with consistent period inputs. Do not enter an annual life alongside monthly period numbers without converting the life to months.
What is the difference between SLN and DDB?
SLN allocates the same depreciable amount to every full period. DDB applies a declining-balance rate, which usually creates larger depreciation in earlier periods and smaller amounts later. The methods therefore produce different period-by-period expenses even with identical cost, salvage value, and useful life.
Can Excel calculate tax depreciation such as MACRS?
Excel’s general SLN, SYD, DB, DDB, and VDB functions do not automatically determine a tax depreciation method or its required conventions. Tax rules vary by jurisdiction and asset circumstances, so confirm the applicable treatment using authoritative guidance or a qualified tax professional rather than assuming a general formula is a tax schedule.
Why does my depreciation schedule not end at salvage value?
Check that the period range covers the full useful life, that every row uses the intended method and inputs, and that you have not rounded intermediate calculations. DB, DDB, and VDB can produce different patterns, and partial-period settings affect totals. Review the method’s behavior and the rules governing the schedule before adjusting a final amount manually.
Related
- Convert ZIP to Excel: What Works and What Does Not
- Convert Percentage to Text in Excel: Methods and Tips
- Convert Text to Numbers in Excel: Methods That Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUBSTITUTE and REPLACE in Excel: Differences ExplainedIn Excel, use SUBSTITUTE to find specified text and replace it, or use REPLACE to change characters at…
- Excel Text Left of a Character: Formulas and ExamplesTo return the text before a character in Excel, combine LEFT with FIND: if A2 contains INV-2048, enter…
- Excel Sum a Row Based on a Column Header with SUMIFTo sum values across a row when their column headers match a label, use SUMIF: with headers in…
- Excel IF SUM: How to Combine IF, SUMIF, and SUMIFS FormulasTo test a total and return one result or another, combine IF with SUM: for example, with amounts…
- SQL SPLIT Function Syntax and Examples by DatabaseSQL has no single, universal SPLIT() function: the syntax depends on your database, and some systems return pieces…
- Balance Sheet Equation: Meaning, Formula, and ExamplesThe balance sheet equation is Assets = Liabilities + Equity. It means that everything a business owns or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.