In Excel, calculate straight-line depreciation with =SLN(cost,salvage,life); for example, =SLN(24000,4000,5) returns $4,000 per year. Excel also has built-in functions for declining-balance and sum-of-years’-digits methods, so the right formula depends on how the asset’s cost is expected to be used up.
What depreciation expense means
Depreciation allocates the cost of a tangible long-term asset over the periods in which it is expected to provide value. Instead of treating the entire purchase price as an expense in one period, a business records portions of that cost over time. The asset’s cost, estimated salvage value, and useful life are key inputs to many depreciation calculations.
Depreciation expense is an accounting allocation, not a measure of the asset’s current market value or the cash spent during the period. A machine might lose value faster or slower in the resale market than its accounting depreciation schedule suggests. Similarly, recording depreciation does not itself set aside cash to replace the asset.
Excel can calculate a schedule, but it cannot decide which method or assumptions are appropriate. Those depend on the accounting purpose, the asset, and the applicable rules or policies. Tax depreciation may follow different conventions from book depreciation, so do not assume an Excel method is automatically valid for a tax return.
Set up the worksheet inputs
Use one row for the asset’s assumptions, with a separate cell for each input. For the examples below, enter the asset’s cost in B2, estimated salvage value in C2, useful life in years in D2, and the period number in E2. The example asset costs $24,000, has a $4,000 estimated salvage value, and has a five-year useful life.
- Cost: the amount used as the asset’s depreciable basis for this schedule. Confirm which purchase and setup costs your accounting policy includes.
- Salvage value: the estimated amount the asset will be worth at the end of its useful life, if any. It is generally not depreciated under these methods.
- Useful life: the number of periods over which the asset is expected to be used in the schedule. Use years for an annual schedule and months for a monthly schedule.
- Period: the year or other period for which you want to calculate expense. For an annual five-year schedule, use period numbers 1 through 5.
Check that cost is greater than or equal to salvage value, that useful life is positive, and that the period number falls within the schedule. If you build a monthly schedule, make sure the useful-life input and period numbering are also monthly; mixing months and years produces misleading results.
Straight-line depreciation with SLN
Straight-line depreciation assigns the same expense to each full period. It is often the easiest method to understand and is useful when an asset is expected to provide roughly even benefits over its useful life. Excel’s SLN function takes the cost, salvage value, and useful life, in that order.
With cost in B2, salvage value in C2, and useful life in D2, enter =SLN(B2,C2,D2) to calculate annual depreciation. For the example inputs, the depreciable amount is $24,000 minus $4,000, or $20,000; dividing $20,000 by five years gives $4,000 per year. The function returns $4,000.
The result is the same for each year when the inputs remain unchanged. After five full years, the schedule has allocated $20,000 of depreciation, leaving a book value of $4,000, equal to the estimated salvage value. A schedule can show these amounts in separate rows, but the formula itself returns the expense for one period, not the entire multi-year schedule.
Accelerated methods: SYD and DDB
Accelerated methods record more depreciation in earlier periods and less in later ones. They can be appropriate when an asset is expected to provide more service early in its life or lose usefulness more quickly at first. They do not change the asset’s purchase price; they change how its depreciable cost is allocated across periods.
The sum-of-years’-digits method uses the asset’s depreciable amount and a declining fraction for each year. For a five-year life, the digits add to 15 (5 + 4 + 3 + 2 + 1). In year one, the fraction is 5/15; in year two, it is 4/15. Excel’s SYD function requires a period number as its fourth argument.
With cost in B2, salvage value in C2, useful life in D2, and year number 1 in E2, enter =SYD(B2,C2,D2,E2). The example returns $6,666.67: $20,000 of depreciable cost multiplied by 5/15. Set E2 to 2 for the second year, and the same layout returns $5,333.33, using the 4/15 fraction.
The double-declining-balance method applies a fixed percentage to the asset’s remaining book value, rather than dividing the depreciable amount evenly. Excel’s DDB function uses cost, salvage value, useful life, and period; an optional fifth argument can set the factor, which defaults to 2.
With the same inputs in B2:D2 and period 1 in E2, enter =DDB(B2,C2,D2,E2). The default double-declining rate is 2/5, or 40%, so the first year’s expense is $24,000 multiplied by 40%, or $9,600. The remaining book value is then $14,400. DDB expense falls as the remaining book value falls, and the function does not depreciate the asset below its salvage value.
How DB differs from DDB
Excel’s DB function also uses a declining-balance approach, but it calculates a fixed rate from the cost, salvage value, and useful life, rounding that rate to three decimal places. You can optionally provide the number of months in the first year. This is different from DDB’s default factor-based rate, so DB and DDB can produce different schedules even when they use the same asset inputs.
With cost in B2, salvage value in C2, useful life in D2, and period 1 in E2, enter =DB(B2,C2,D2,E2). For $24,000 cost, $4,000 salvage, and a five-year life, DB’s rate is rounded to 0.301, or 30.1%. The first-period expense is $24,000 × 0.301, which is $7,224; Excel returns $7,224.
Use the methods consistently when comparing schedules. DB’s optional month argument is intended for a first year shorter than 12 months; it does not make the function a general date-based depreciation system. If your organization uses a different convention, verify how that convention should be represented before relying on a basic function.
Build and check a depreciation schedule
A function result for one period is only one line of a schedule. To make the schedule useful, list each period, calculate that period’s expense, and track accumulated depreciation and ending book value. For example, a straight-line schedule for the sample asset would show $4,000 of expense in each of five years, accumulated depreciation rising by $4,000 each year, and ending book value falling from $24,000 to $4,000.
- Record the asset inputs and document where the estimates came from.
- Choose a method that matches the accounting policy and intended use of the schedule.
- Enter one period number per row and use the appropriate function for that period.
- Check that total depreciation over the full life does not exceed cost minus salvage value.
- Compare the final book value with the expected salvage value and investigate any difference.
For a quick arithmetic check on the straight-line example, five years at $4,000 per year equals $20,000, exactly the $24,000 cost less $4,000 salvage value. For an accelerated method, the yearly amounts differ, but the schedule should still respect the total depreciable amount over the useful life. Rounding displayed values can make totals appear slightly different, so distinguish cell values from the number of decimal places shown.
Choosing a method and avoiding common errors
Choose based on the purpose of the schedule, not simply on which function is easiest to enter. Straight-line is straightforward for steady use; SYD and declining-balance methods allocate more cost to early periods. Check your organization’s accounting policy and any applicable reporting requirements. Tax rules and book accounting can use different lives, methods, or timing conventions, so a workbook should clearly label which schedule it represents.
- Reversed arguments: SLN, SYD, DB, and DDB do not all take exactly the same number of inputs. Follow the order shown in the function’s syntax.
- Wrong period units: a life of 5 means five periods, not automatically five years. Align the life and period number with the schedule frequency.
- Salvage omitted or misstated: using zero salvage when the estimate is nonzero increases the depreciable amount.
- Off-by-one period: period 1 is the first period, not period 0, for the examples here.
- Assuming depreciation equals market decline: accounting expense follows the selected method and assumptions; it does not forecast resale price.
Excel for Microsoft 365 on Windows uses the same function syntax shown here. The function names and argument order are also available in current Excel for Mac. Excel for the web can calculate these worksheet functions as well, although ribbon and interface details may differ from the desktop app. If Excel displays an error, check that the inputs are numeric and the period and useful-life values are valid.
Frequently Asked Questions
What is the simplest depreciation formula in Excel?
For straight-line depreciation, use Excel’s SLN function with cost, salvage value, and useful life. With cost in B2, salvage in C2, and life in D2, enter =SLN(B2,C2,D2). It returns the same expense for each full period.
How do I calculate monthly depreciation in Excel?
Use a monthly useful life and monthly period structure, rather than entering a number of years and treating it as months. For instance, a five-year life is 60 months. Confirm that your accounting convention permits the simple monthly allocation you are building; dates, partial periods, and other conventions may require a more specific schedule.
What is the difference between SLN and DDB?
SLN allocates the depreciable amount evenly over the useful life. DDB records larger amounts earlier by applying a declining-balance rate to remaining book value. The functions can therefore give different period expenses for the same asset.
Can depreciation expense be greater than the asset’s cost?
A properly constructed schedule should not allocate more than the depreciable amount, which is generally cost less salvage value under these examples. Check the selected method, period number, inputs, and cumulative totals if your schedule appears to exceed that amount.
Is Excel depreciation automatically correct for tax reporting?
No. Excel calculates according to the function and inputs you provide; it does not determine the tax treatment or confirm that a method, life, or convention is permitted. Check the relevant rules with a qualified tax professional.
Related
- Daily Attendance Register Template Excel: Setup and Tips
- Customer Details in Excel: How to Set Up a Useful List
- Customizable Calendar Template in Excel: How to Make One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Export a Filter List in Excel: Steps and OptionsTo export filtered rows in Excel, apply your filters, select the results, copy the visible cells to a…
- Calculating NPV and IRR in Excel: Formulas and StepsTo calculate NPV in Excel, discount the future cash flows with NPV and add the initial cash flow…
- Excel Formula for Date Sequence: 4 Easy WaysThe simplest Excel formula for a date sequence is =A2+1 entered in cell A3, assuming A2 holds your…
- 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…
- Number Sequence in Excel: Fill Series or Use a FormulaTo create a number sequence in Excel, enter the first two numbers, select both cells, and drag the…
- How to Filter Subtotals in Excel by Group or TotalTo show subtotal rows without their detail rows, use the outline buttons Excel creates with Data > Subtotal,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.