Accumulated Depreciation Formula in Excel

Formulas & functions · Updated November 21, 2026

For straight-line depreciation, accumulated depreciation is the depreciation recorded to date: annual depreciation multiplied by the number of years used, capped at the asset’s depreciable amount (cost minus salvage value). In Excel, calculate it with =MIN(B2-C2,SLN(B2,C2,D2)*MAX(0,MIN(E2,D2))) when B2 is cost, C2 is salvage value, D2 is useful life in years, and E2 is elapsed depreciation years.

What accumulated depreciation means

Accumulated depreciation is the total depreciation expense recorded for a tangible asset from the time it is placed in service through a particular reporting date. It is a running total, not the asset’s current market value and not a cash balance set aside to replace the asset. On a balance sheet, it is generally shown as a contra-asset that reduces the related asset’s gross carrying amount.

For example, a machine costing $24,000 with an estimated $2,000 salvage value has a depreciable amount of $22,000. If straight-line depreciation is $4,400 each year, then after three complete years the accumulated depreciation is $13,200. The remaining book value is $10,800: the original $24,000 cost less $13,200 accumulated depreciation. These are illustrative figures; the timing and method used for real financial statements depend on the applicable accounting policy.

The basic Excel formula and its assumptions

Excel’s SLN function calculates straight-line depreciation for one period. To calculate a cumulative amount, multiply that annual figure by the elapsed years, then prevent the result from going below zero or above the depreciable amount. This formula assumes that the asset uses straight-line depreciation, the elapsed-years input represents depreciation periods, and the asset has not already been disposed of.

With cost in B2, salvage value in C2, useful life in years in D2, and elapsed full years in E2, enter the cumulative formula in F2:

=MIN(B2-C2,SLN(B2,C2,D2)*MAX(0,MIN(E2,D2)))

The MAX function makes the result zero if E2 is negative; the inner MIN prevents depreciation from exceeding the useful life; and the outer MIN caps total depreciation at cost minus salvage value. For valid inputs, enter cost greater than or equal to salvage value, a positive useful life, and a nonnegative elapsed time. If the asset has completed three years in the example above, enter 24000 in B2, 2000 in C2, 5 in D2, and 3 in E2. Excel returns $13,200.

To see the annual expense separately, with the same cost, salvage value, and useful-life cells in B2, C2, and D2, use:

=SLN(B2,C2,D2)

For the example, the result is $4,400 per year: ($24,000 − $2,000) ÷ 5. SLN returns a period’s depreciation, not the cumulative amount by itself. That distinction is important when a worksheet needs both the current-year expense and the balance accumulated over several years.

Set up an asset worksheet

A clear input table makes the calculation easier to audit and update. One practical layout is:

  • A: Asset name or identification number.
  • B: Original cost, excluding amounts your accounting policy does not capitalize.
  • C: Estimated salvage value at the end of useful life.
  • D: Useful life in years.
  • E: Elapsed depreciation years as of the reporting date.
  • F: Accumulated depreciation calculated by the formula.
  • G: Net book value, calculated as cost less accumulated depreciation.

Put descriptive headings in row 1 and the first asset in row 2. Format the cost, salvage value, accumulated depreciation, and book value columns as currency; keep useful life and elapsed years numeric. Add a note explaining whether elapsed years are complete annual periods or follow another convention. That small detail helps prevent someone from treating a partial year as a full year by mistake.

With cost in B2 and accumulated depreciation in F2, the book value formula in G2 is:

=B2-F2

For the example, this returns $10,800. If the worksheet includes multiple assets, copy the depreciation formula down each row and check that each row refers to its own inputs. Use absolute references only when a formula intentionally points to a shared assumption, such as a single reporting date or a fixed rate.

Build a year-by-year depreciation schedule

A schedule is often more useful than entering elapsed years manually because it shows how each year’s expense contributes to the cumulative balance. Store each year’s depreciation expense in one column and calculate the running total in another. For example, put each period’s expense in F2:F6, with the first period in row 2, and place accumulated depreciation in G2:G6.

With annual depreciation expense in F2:F6 and the first period beginning on row 2, enter this running-total formula in G2 and fill it down through G6:

=SUM($F$2:F2)

In the next row, Excel adjusts the ending reference as the formula is filled down, so the running total includes all expenses from the first period through that row. If each year’s expense is $4,400, the schedule shows $4,400 after year one, $8,800 after year two, and $22,000 after year five. A schedule lets you review each period, spot missing entries, and tie the total back to the asset’s depreciable amount.

For a ledger containing several assets, use a unique asset ID on each depreciation row rather than relying on names that may be spelled differently. With IDs in A2:A100, period depreciation in F2:F100, and the asset ID to summarize in J2, calculate that asset’s total recorded depreciation with:

=SUMIF($A$2:$A$100,J2,$F$2:$F$100)

This adds the depreciation entries matching the ID in J2. It is useful for summarizing a transaction-level schedule, but it does not enforce a cap automatically; the underlying expense entries still need to follow the relevant schedule and not depreciate an asset below its salvage value.

Choosing the depreciation method

The cumulative formula depends on the method used to calculate depreciation each period. Straight-line spreads the depreciable amount evenly over the asset’s useful life, which makes the running total especially simple. Excel’s SLN function calculates this equal period amount. It is appropriate only when straight-line treatment matches the assumptions and policy for the asset.

Other methods allocate expense differently. Excel’s DDB function calculates declining-balance depreciation for a specified period, while SYD calculates sum-of-years’-digits depreciation for a specified period. Those functions return period expense rather than a ready-made lifetime accumulated balance. To get a cumulative figure using one of these methods, calculate the expense for each period in a schedule and sum the entries through the reporting period. Confirm the function’s period and convention arguments against Excel’s function help before building a schedule.

Do not select a method solely because it produces a preferred balance. Useful life, salvage value, the date depreciation begins, and the appropriate accounting or tax rules can affect the method and timing. Book depreciation and tax depreciation can differ, so a general-purpose Excel example should not be assumed to determine a tax deduction.

Handling dates and partial years

The simple example uses elapsed years as an input. Real assets often enter service partway through a year, so the first period may be partial. A company may use a full-month, half-year, actual-days, or other convention depending on its policy and the rules that apply. The correct convention cannot be inferred just from purchase date and useful life; the date the asset is ready and available for use may also matter.

If you need date-based calculations, first decide how a partial year is treated, then make that convention explicit in the schedule. Avoid silently using the number of calendar years between two dates as a substitute for the organization’s depreciation rule. For a simple full-year schedule, manually enter the number of completed depreciation periods as E2 and explain the convention in the column heading. For a detailed schedule, use separate rows for periods and record only the depreciation assigned to each period. The running-total approach then adds the entries actually recorded rather than assuming every year is identical.

Common errors and useful checks

  • Confusing expense with accumulated depreciation: SLN gives one period’s expense. Multiply by periods or sum a schedule to obtain the cumulative amount.
  • Forgetting salvage value: Depreciable amount is cost less salvage value, not necessarily the full purchase cost.
  • Depreciating past the limit: The accumulated total for a fully depreciated asset should not exceed cost minus salvage value in this straight-line example.
  • Using inconsistent units: If useful life is entered in years, elapsed time must also be in years. Do not multiply an annual expense by a count of months.
  • Including disposed assets indefinitely: A schedule should stop or otherwise reflect the applicable treatment when an asset is sold, retired, or written off.
  • Overwriting inputs with calculated values: Keep assumptions and formulas in distinct columns so a change to cost or life updates the result transparently.

For a quick check, compare accumulated depreciation with cost less salvage value, confirm that the total never decreases in a normal schedule, and verify the annual expense against the chosen method. Also check the final period: a rounding difference may leave a small residual, so a schedule may need a final-period adjustment consistent with the organization’s policy. Keep original records and document any manual adjustment instead of hiding it in a formula.

Frequently Asked Questions

What is the simplest formula for accumulated depreciation in Excel?
For straight-line depreciation, multiply the period depreciation by the number of periods recorded and cap the total at cost minus salvage value. The article’s MIN and SLN formula does that when the inputs are in B2:E2 and elapsed periods are full years.

Does Excel have an accumulated depreciation function?
Excel has functions such as SLN, DDB, and SYD for calculating depreciation for a period, but there is not a single built-in function that returns accumulated depreciation for every method and timing convention. A running schedule or a cumulative formula supplies the total through a chosen period.

Is accumulated depreciation the same as book value?
No. Accumulated depreciation is the total depreciation recorded to date. Book value, often called net book value, is generally asset cost minus accumulated depreciation, subject to other relevant adjustments.

Can accumulated depreciation be greater than an asset’s cost?
Under the straight-line example, it should not exceed cost less salvage value. If the worksheet shows a larger number, check for duplicated periods, an incorrect salvage value, mismatched units, or a missing cap. Other accounting entries may affect an asset’s carrying amount, so investigate the records rather than changing a formula blindly.

How do I calculate accumulated depreciation for several assets?
Keep a distinct row for each asset and period, identify assets with consistent IDs, and sum each asset’s period expenses through the reporting date. A summary formula such as SUMIF can total entries for one ID, while the detailed schedule preserves the calculation history.

Can I use this formula for tax depreciation?
Not automatically. Tax depreciation rules and conventions may differ from book depreciation and can depend on jurisdiction and asset type. Treat this as a spreadsheet illustration, and check applicable requirements with a qualified tax professional.

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.