Depreciation Table in Excel: Methods, Formulas, and Setup

Formulas & functions · Updated November 22, 2026

A depreciation table in Excel tracks an asset’s cost, estimated salvage value, useful life, depreciation expense, and remaining book value over time. For a basic annual schedule, enter those assumptions in cells and use Excel’s SLN function for straight-line depreciation; other built-in functions support declining-balance and sum-of-years’-digits methods.

What a depreciation table shows

Depreciation allocates the depreciable cost of a long-lived tangible asset across the periods in which it is expected to be used. Depreciable cost is generally the asset’s cost minus its estimated salvage value. The schedule records the expense assigned to each period and the asset’s carrying amount, often called book value, after that expense.

A useful table commonly includes the asset name, purchase date, cost, salvage value, useful life, depreciation method, period, opening book value, depreciation expense, accumulated depreciation, and ending book value. The ending amount for one period becomes the opening amount for the next. Accumulated depreciation is the total depreciation recorded to date; it is not the same thing as the period’s expense.

Depreciation is an accounting allocation, not a measure of an asset’s current selling price or physical condition. It is also different from loan amortization, which allocates loan payments between principal and interest. A company may maintain one schedule for financial reporting and another for tax or internal purposes, because applicable rules and estimates can differ.

Choose the assumptions before building the schedule

Before entering formulas, decide what each row represents—usually one year or one month—and keep the units consistent. A five-year useful life paired with annual rows means five depreciation periods. If you use monthly rows, the life and period numbering must also be monthly. Mixing annual life with monthly periods is a common source of incorrect results.

  • Cost: The amount assigned to the asset under the relevant accounting policy. Which costs are included can depend on the circumstances and applicable standards.
  • Salvage value: The estimated amount expected to remain at the end of the useful life. It is an estimate, not necessarily a guaranteed sale price.
  • Useful life: The period over which the asset is expected to provide service for the schedule being prepared.
  • Method and convention: Decide whether the schedule uses straight-line, an accelerated method, a partial first period, or another required approach.

These inputs should be documented with their source and review date. Changing an estimate later can affect future depreciation and may require a documented accounting treatment; do not silently overwrite past figures just to make a schedule match a desired total.

Set up a simple annual schedule in Excel

In Excel for Microsoft 365 on Windows, make an inputs block and a schedule beneath it. For example, enter the asset cost in B2, salvage value in B3, and useful life in years in B4. Put the year numbers 1 through 5 in A9:A13. Set up columns for opening book value, depreciation expense, accumulated depreciation, and ending book value. Use clear labels and a date or period column so someone reviewing the workbook can tell what each row means.

With the cost in B2, salvage value in B3, useful life in B4, and period rows in A9:A13, enter the opening value for the first period in B9:

=$B$2

With the same inputs and annual periods, calculate depreciation for the first period in C9:

=SLN($B$2,$B$3,$B$4)

With accumulated depreciation beginning in D9 and each period’s expense in column C, calculate the first period’s accumulated depreciation in D9:

=SUM($C$9:C9)

With opening book value in B9 and period depreciation in C9, calculate the first period’s ending book value in E9:

=B9-C9

For the next row’s opening value, with the prior period ending value in E9, enter this in B10:

=E9

Copy the depreciation, accumulated-depreciation, ending-value, and subsequent opening-value formulas down through the remaining years. In Windows desktop Excel, Ctrl+D fills down after selecting the formula cell and the cells below it; on Mac, use Command+D. The formulas use absolute references for the assumptions so they remain fixed, while the period references adjust as the formulas are copied.

Worked straight-line example

Suppose equipment costs $12,000, has an estimated salvage value of $2,000, and has a five-year useful life. Straight-line depreciation assigns an equal amount to each year: ($12,000 − $2,000) ÷ 5 = $2,000 per year. With cost in B2, salvage value in B3, and useful life in B4, the formula in the preceding setup returns $2,000.

The resulting schedule is straightforward: Year 1 opens at $12,000, records $2,000 of depreciation, and ends at $10,000. Year 2 opens at $10,000 and ends at $8,000. The same annual expense brings the ending balances to $6,000, $4,000, and finally $2,000 in Years 3 through 5. Total depreciation is $10,000, exactly the cost less salvage value. This reconciliation is a useful check on a straight-line schedule.

Excel’s SLN function calculates the periodic straight-line depreciation amount; it does not create the full table for you. The surrounding opening, accumulated, and ending-value columns make the result auditable and help expose a wrong life, a mismatched period, or a formula copied incorrectly.

Compare Excel’s built-in depreciation methods

The appropriate method depends on the purpose of the schedule and the applicable accounting policy or requirements. Excel can calculate several common methods, but a function’s presence does not establish that a method is appropriate for a particular financial statement or tax filing.

  • SLN: Straight-line depreciation gives an equal amount per period. It is useful when the expected pattern of benefit is even and is easy to explain and check.
  • DDB: Double-declining-balance depreciation is accelerated, generally producing larger expenses earlier. Excel’s DDB function takes cost, salvage value, life, and period, with an optional factor.
  • SYD: Sum-of-years’-digits depreciation is also accelerated. It weights the depreciable amount by the remaining life in each period.
  • DB: The DB function calculates fixed-declining-balance depreciation and includes an optional month argument for the first year. Its conventions differ from a simple full-year schedule.

For the $12,000 asset with $2,000 salvage value and five-year life, and with the first period number in D2, this DDB formula returns $4,800 for period 1:

=DDB($B$2,$B$3,$B$4,D2)

The result is 40% of the $12,000 opening cost: the double-declining rate is 2 ÷ 5, or 40%. The next period’s calculation is based on the reduced book value, so the expense normally falls. DDB may leave book value above salvage at the end of the stated life unless the method is changed or the schedule is otherwise adjusted. If policy calls for switching to straight-line, build and document that switch rather than assuming the function will make it automatically.

With cost in B2, salvage value in B3, useful life in B4, and period 1 in D2, this SYD formula returns about $3,333.33 for the first period:

=SYD($B$2,$B$3,$B$4,D2)

For this example, the first-year fraction is 5 ÷ (5 + 4 + 3 + 2 + 1), or 5/15. Applying that fraction to the $10,000 depreciable amount gives $3,333.33. Later periods receive smaller shares. Compare complete schedules—not just the first-year result—and confirm that the method’s total expense and ending value are consistent with the assumptions and the treatment you need.

Dates, partial periods, and monthly schedules

A simple SLN schedule assumes equal periods and does not calculate a partial first year from purchase and in-service dates. If an asset is placed in service partway through a year, the required convention may call for a partial period, a monthly schedule, or a specific rule. Do not divide an annual amount by 12 automatically unless that matches the policy being followed.

For a monthly schedule, define the useful life in months and number the periods consistently, or use a date-driven design with carefully reviewed formulas. Check whether the method’s Excel function supports the convention you need: for example, DB has an optional month argument, while SLN does not take a start date. Excel’s VDB function supports variable declining-balance calculations, but its periods and optional arguments need to be understood before using it for a reporting schedule.

Excel for the web supports many standard worksheet functions, but workbook features and interface details can vary by platform. The formulas above work as ordinary worksheet formulas; for complex date conventions or accounting templates, confirm the resulting schedule in the version and workflow used by the people who will rely on it.

Checks and common spreadsheet problems

Review the schedule as a connected calculation rather than trusting one formula. These checks catch many practical errors:

  • Confirm that cost is not less than salvage value and that useful life is positive.
  • Check that accumulated depreciation equals the sum of period expenses to date.
  • For a fully depreciated straight-line schedule, confirm total expense equals cost minus salvage and ending book value equals salvage.
  • Make sure opening book value for each row equals the prior row’s ending book value.
  • Verify period units, method, asset start date, and rounding policy against the intended schedule.

Rounding displayed values is not the same as rounding the underlying calculations. Formatting a cell to show two decimal places may hide extra precision while Excel continues to calculate with it. That can leave a small remainder in the final period. If a schedule must round each period, make that choice explicit and reconcile any final-period adjustment to the intended policy; do not use a hidden adjustment without documenting it.

Another frequent mistake is treating tax depreciation and book depreciation as interchangeable. Their permitted methods, lives, conventions, and treatment can differ. A spreadsheet can calculate the assumptions entered, but it cannot determine which rules apply to a real asset. For financial, tax, or legal decisions, check the relevant requirements with a qualified professional.

Make the workbook easier to maintain

Keep assumptions separate from formulas, label units, and use cell references rather than typing the same cost or useful life repeatedly into every row. Add an asset identifier and a source note for important estimates. If the workbook contains several assets, give each asset a clear input record and consider a summary sheet that totals period expense without overwriting the detailed schedules.

Use number formats consistently: currency for cost and book values, dates for placed-in-service dates, and whole numbers for period counts. A separate method column can make it clear why two assets with similar costs have different expense patterns. Protecting formula cells may reduce accidental edits, but it does not replace version control or review. Keep a backup and have significant schedules checked against the organization’s accounting process.

Frequently Asked Questions

Does Excel have a depreciation formula?
Yes. Excel includes SLN for straight-line, DDB for double-declining balance, SYD for sum-of-years’-digits, and DB for fixed-declining balance. Each calculates depreciation for a period; a complete schedule usually needs additional formulas for opening and ending book value.

How do I calculate straight-line depreciation in Excel?
Subtract estimated salvage value from cost, then divide by useful life. Excel’s SLN function performs that calculation when you supply cost, salvage value, and life in matching period units.

Why does my depreciation schedule not end at zero?
A correctly prepared schedule may end at salvage value rather than zero. Accelerated methods can also leave a balance above salvage unless the schedule changes methods or otherwise follows a specified convention. Check the method, useful life, period numbering, and final-period treatment.

Can I use the same depreciation table for tax and accounting?
Not necessarily. Tax and financial-reporting schedules can use different rules and assumptions. Keep separate schedules when required, and confirm the applicable treatment with a qualified tax or accounting professional.

Should I use annual or monthly depreciation periods?
Use the interval required by the purpose of the schedule and the asset’s timing convention. Annual periods are simple for full-year examples; a partial first year or monthly reporting may require a different setup. Keep the useful-life units aligned with the period rows.

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.