Depreciation Schedule in Excel: MACRS & Book Methods (2026)

July 11, 2026 · VeloraAI Team
Formulas Financial Modeling Excel

Depreciation is the quietest line in a financial model and one of the noisiest failure points. It touches all three statements — expense on the income statement, accumulated reserve on the balance sheet, and a non-cash add-back on the cash flow — and it drives book-tax differences that decide whether a deal's after-tax IRR is credible. A depreciation schedule in Excel that hardcodes a single straight-line rate for a mixed CapEx stack is a model waiting to fail an audit.

This guide builds the full stack: a CapEx-driven depreciation waterfall using SLN, DB, DDB, SYD, and VDB; MACRS half-year and mid-quarter tables for U.S. tax accounting; book vs tax reconciliation with deferred tax liability roll-forward; and the diagnostic checks that keep the schedule linked cleanly into a three-statement model or an LBO. Every formula runs in Excel 365 and Excel 2021.

What Is a Depreciation Schedule and Why Build One in Excel?

A depreciation schedule is a period-by-period table that tracks each capital asset (or asset vintage) from purchase to fully-depreciated, showing the cost basis, method, useful life, annual expense, and remaining net book value. In a financial model, it converts a lumpy CapEx stream into a smooth expense line and populates the accumulated depreciation reserve on the balance sheet.

You build one in Excel — rather than accepting a single blended rate — because every real business has multiple asset classes with different useful lives (5-year IT equipment, 7-year machinery, 15-year land improvements, 39-year buildings) and because tax authorities almost never accept book method. A merged rate hides the timing of the deferred tax liability that swings your after-tax cash flow.

The Three Views Every Schedule Must Support

View Purpose Method Typically Used
Book (GAAP/IFRS) Financial reporting; drives EBITDA, net income Straight-line over useful life
Tax (US federal) Cash tax computation; drives IRS Form 4562 MACRS (double-declining then straight-line switch)
Management Internal returns, unit economics, replacement cycles Component / usage-based / sum-of-years-digits

Every well-built schedule reconciles these three side by side. The difference between book and tax depreciation becomes the deferred tax liability roll, and management view is what the CFO actually uses to plan replacement CapEx.

💡 Pro Tip: Never model depreciation as a percentage of gross PP&E in a serious deal model. A single ratio compresses vintage information — it cannot tell you when a 2019 asset finishes depreciating, so post-holding-period expense will be wrong.

How Do You Build a Depreciation Schedule in Excel Step by Step?

To build a depreciation schedule in Excel, list each CapEx vintage as a row, assign a useful life and method, and compute per-period depreciation with SLN, SYD, DDB, or VDB down the columns. Sum vertically for the P&L expense in each period and horizontally for cumulative depreciation per vintage.

Step 1 — Lay Out the CapEx Waterfall

Set up columns for Vintage Year, Asset Class, Cost, Useful Life, Salvage Value, Method, and one column per forecast year (Year 1 through Year N). Each row is a single CapEx event — a purchase in a specific year of a specific asset. This "waterfall" layout is the industry standard because it lets you audit any single vintage in isolation.

| Vintage | Asset Class    | Cost    | Life | Salvage | Method | Y1  | Y2  | Y3  | Y4  | Y5  |
| 2024    | IT Hardware    | 500,000 | 5    | 0       | SL     | 100 | 100 | 100 | 100 | 100 |
| 2025    | Machinery      | 1,200,000 | 7  | 0       | SL     |     | 171 | 171 | 171 | 171 |

Step 2 — Write the Formula That Handles Vintage Timing

The core trick is a formula that returns 0 before the vintage starts, then the appropriate expense, then 0 again after the asset is fully depreciated. Assume the vintage year is in $C4, cost in $D4, salvage in $E4, life in $F4, and the current period year is in the column header row 3.

For a straight-line vintage, place this in the first period column and drag across:

=IF(AND(H$3>=$C4, H$3<$C4+$F4), SLN($D4,$E4,$F4), 0)

SLN(cost, salvage, life) returns the per-period straight-line expense. The AND gate turns depreciation on the vintage year and off the year the asset is fully written down. For half-year convention (common under U.S. GAAP and required under MACRS), see the half-year adjustment in the MACRS section below.

Step 3 — Sum for the P&L Line

Below the waterfall, add a Total Depreciation Expense row that sums each period column. That total is what flows into the income statement's D&A line. A separate Ending Accumulated Depreciation row cumulatively sums the total row across periods — that feeds the balance sheet.

ℹ️ Note: Keep the depreciation schedule on its own tab and pull totals into the three statements with a single named-range reference. Never let a P&L cell reach across five columns of a nested waterfall — the audit trail dies.

Which Excel Depreciation Function Should You Use?

Excel ships with five depreciation functions. Each corresponds to a specific accounting method, and they differ in whether they return per-period expense or cumulative expense, and whether they handle partial periods.

Function Method Returns Use When
SLN(cost, salvage, life) Straight-line Per-period expense (constant) Book depreciation, most intangibles
SYD(cost, salvage, life, period) Sum-of-years'-digits Per-period expense (accelerated) Management view for high-obsolescence assets
DB(cost, salvage, life, period, [month]) Fixed declining balance Per-period expense (accelerated) Rare; uses IRS-specified fixed rate
DDB(cost, salvage, life, period, [factor]) Double-declining balance Per-period expense (accelerated) Aggressive book method; factor defaults to 2
VDB(cost, salvage, life, start, end, [factor], [no_switch]) Variable declining balance with switch Cumulative expense over a range MACRS approximation; switches to SL when advantageous

VDB is the workhorse for U.S. tax modeling because it replicates MACRS behavior: it starts on a declining-balance method, then automatically switches to straight-line in the year that straight-line produces a larger deduction. That switch is exactly what MACRS tables encode.

Straight-Line: The Baseline

For an asset costing $500,000 with a 5-year life and zero salvage:

=SLN(500000, 0, 5)

Returns $100,000 per year for five years. This is the default for GAAP book depreciation of most PP&E and is the method to reach for when there is no operational reason to accelerate.

Double-Declining Balance: The Accelerated Book Method

DDB applies a rate of factor / life to the current net book value each period, then floors at the salvage value. With factor = 2 (the default), a 5-year asset uses a 40% rate:

=DDB(500000, 0, 5, 1)  → 200,000  (Year 1)
=DDB(500000, 0, 5, 2)  → 120,000  (Year 2)
=DDB(500000, 0, 5, 3)  →  72,000  (Year 3)

Notice DDB never fully depreciates unless you switch to straight-line at the crossover point — a trap the VDB function handles automatically.

Sum-of-Years'-Digits: The Middle Ground

SYD allocates expense as a fraction with the remaining useful life in the numerator and the sum of the digits (1+2+3+...+n) in the denominator. For the same $500,000 / 5-year asset:

=SYD(500000, 0, 5, 1)  → 166,667   ( 5/15 × 500,000 )
=SYD(500000, 0, 5, 2)  → 133,333   ( 4/15 × 500,000 )

SYD front-loads expense less aggressively than DDB and is common in manufacturing for equipment that loses value faster in early years.

Example: A robotic assembly cell costs $1,000,000 with 7-year life. SL expense = $142,857/yr. SYD Year-1 expense = 7/28 × $1M = $250,000. DDB Year-1 (factor=2) = 2/7 × $1M = $285,714. Same asset, three legitimate book expenses — the choice is a policy decision, not a formula error.

How Does MACRS Depreciation Work in Excel?

MACRS (Modified Accelerated Cost Recovery System) is the U.S. federal tax depreciation method mandated by IRS Publication 946 for most property placed in service after 1986. In Excel, you can model MACRS in two ways: replicate the exact IRS percentage tables with a lookup, or approximate them dynamically with VDB using the correct convention.

The IRS Percentage Table Approach

For assets under the General Depreciation System (GDS) with the half-year convention, the IRS publishes a fixed percentage of original basis for each year of the recovery period. Store the table once and INDEX into it:

MACRS Half-Year Convention (GDS)
Year | 3-yr   | 5-yr   | 7-yr   | 10-yr  | 15-yr  | 20-yr
  1  | 33.33% | 20.00% | 14.29% | 10.00% |  5.00% |  3.750%
  2  | 44.45% | 32.00% | 24.49% | 18.00% |  9.50% |  7.219%
  3  | 14.81% | 19.20% | 17.49% | 14.40% |  8.55% |  6.677%
  4  |  7.41% | 11.52% | 12.49% | 11.52% |  7.70% |  6.177%
  5  |        | 11.52% |  8.93% |  9.22% |  6.93% |  5.713%
  6  |        |  5.76% |  8.92% |  7.37% |  6.23% |  5.285%
  7  |        |        |  8.93% |  6.55% |  5.90% |  4.888%
  8  |        |        |  4.46% |  6.55% |  5.90% |  4.522%

With the table in $B$40:$H$55, cost in $D4, recovery period in $F4, and vintage year in $C4, the MACRS expense for column period H$3 is:

=IFERROR(INDEX($B$40:$H$55, H$3-$C4+2, MATCH($F4,$B$40:$H$40,0)) * $D4, 0)

The H$3-$C4+2 term is the row offset — period 1 (vintage year) maps to row 41 (the header is row 40). IFERROR gates the formula off once the recovery period ends.

The VDB Approximation

If you do not want to embed a hardcoded table, VDB reproduces MACRS closely for the 3, 5, 7, and 10-year classes under the half-year convention. For a 5-year asset costing $500,000 placed in service on July 1:

=VDB(500000, 0, 5, 0, 0.5, 2, FALSE)  → 100,000  (Year 1, half-year)
=VDB(500000, 0, 5, 0.5, 1.5, 2, FALSE) → 160,000  (Year 2, full year)
=VDB(500000, 0, 5, 1.5, 2.5, 2, FALSE) →  96,000

Setting no_switch = FALSE lets VDB switch to straight-line when it becomes advantageous — exactly matching MACRS logic. The start and end parameters accept fractional periods, which is how you encode the half-year convention (0.5 in Year 1, then one full period per subsequent year).

⚠️ Warning: MACRS 15-year and 20-year property uses the 150% declining balance method (factor = 1.5), not 200%. If you drop a 15-year land improvement into a VDB call with the default factor of 2, your first-year deduction will be roughly 33% too high — and the deferred tax liability will be materially wrong.

How Do Book and Tax Depreciation Create Deferred Taxes?

Book depreciation and tax depreciation compute the same total expense over the asset's life but on different schedules. Because tax methods (MACRS) accelerate expense into early years, taxable income is lower than book income at the start and higher at the end. The difference is booked as a deferred tax liability (DTL) on the balance sheet — a real GAAP line item that reverses over the asset's life.

graph TD
    A[CapEx Purchase] --> B[Book Depreciation - SL]
    A --> C[Tax Depreciation - MACRS]
    B --> D[Book Income Tax Expense]
    C --> E[Cash Taxes Paid]
    D --> F[Deferred Tax Liability]
    E --> F
    F --> G[Reverses as Book Catches Up]

The DTL roll-forward inside the schedule is:

DTL_end = DTL_begin + (Tax_Deprec − Book_Deprec) × Tax_Rate

In Excel, if book depreciation for the period is in H15, tax depreciation is in H16, tax rate in $B$3, and prior-period DTL in G17:

=G17 + (H16 - H15) * $B$3

That single row belongs on the depreciation tab, feeds the deferred tax line on the balance sheet, and is the cleanest way to keep book-tax differences honest across a ten-year forecast.

The Total Life Test

A well-built schedule always satisfies: cumulative book depreciation = cumulative tax depreciation = (cost − salvage) at the end of the asset's life. Add a diagnostic row that computes SUM(book_row) - (Cost - Salvage) and formats red if non-zero. This single check catches nine out of ten depreciation errors.

Depreciation Schedule Audit Checklist

Before signing off on a schedule that will drive an LBO or a DCF, verify:

  1. No vintage exceeds its useful life. Cumulative expense per row equals cost minus salvage. Excess means the switch to straight-line failed or a DDB call is uncapped.
  2. Depreciation starts in the correct period. Half-year convention starts at 50% of full-year expense; mid-quarter starts at 12.5%, 37.5%, 62.5%, or 87.5% depending on the placed-in-service quarter.
  3. Total book expense reconciles to the P&L D&A line. No manual overrides on the income statement side.
  4. Accumulated depreciation reconciles to gross PP&E − net PP&E on the balance sheet. This is the strongest single audit check.
  5. DTL roll reverses to zero when the last vintage fully depreciates. If the DTL persists after every asset is done, book-tax timing is being double-counted.
  6. CapEx additions flow into the schedule automatically. Manual "add a row per year" is a bug factory — use one row per year and drag horizontally.
  7. Salvage value assumptions are documented. Zero is a choice, not a default. Auditors will ask.

💡 Pro Tip: Ask an AI copilot inside Excel to trace the depreciation formula chain from any P&L cell back to the source vintage. VeloraAI users often use this to catch the most common error — a straight-line vintage where the useful life was fat-fingered as 5 instead of 10 — in seconds rather than manually walking the waterfall.

How Do You Handle Impairments and Disposals?

An impairment or disposal breaks the schedule's normal cadence. When an asset is written down or sold before the end of its useful life, three things happen simultaneously: remaining depreciation stops, the loss (or gain) hits the income statement, and gross PP&E and accumulated depreciation both shrink by the asset's original cost and life-to-date depreciation respectively.

Modeling a Mid-Life Impairment

Add an Impairment Year column to the CapEx waterfall. Update the vintage formula to zero out expense after the impairment year:

=IF(AND(H$3>=$C4, H$3<$C4+$F4, H$3<$G4), SLN($D4,$E4,$F4), 0)

where $G4 is the impairment year (blank if none). The impairment amount — remaining net book value at the impairment date — flows to a separate "Impairment Loss" line on the P&L, not depreciation expense. Keeping these distinct preserves EBITDA, since impairments are typically below-the-line, while depreciation is above.

Disposals with Proceeds

For a disposal, the gain or loss is Proceeds − Net Book Value at Disposal. Log the disposal on its own row of a sub-schedule so you can trace the impact on the free cash flow bridge — proceeds are an investing inflow, and the gain/loss reverses on the cash flow reconciliation.

Frequently Asked Questions

What is the difference between the SLN and DB functions in Excel?

SLN returns constant straight-line depreciation each period — (cost − salvage) / life. DB returns declining-balance depreciation using the IRS fixed-rate formula, which produces accelerated expense that decreases each period. Use SLN for GAAP book depreciation of most assets; use DB when a specific IRS-mandated fixed rate is required. VDB is usually the better choice for accelerated methods with automatic switch to straight-line.

Can I model MACRS bonus depreciation in Excel?

Yes. Bonus depreciation (Section 168(k)) is modeled by expensing the qualifying percentage of the asset's basis in Year 1, then running standard MACRS on the remaining basis. In Excel, add a "Bonus %" column, subtract Cost × Bonus% from the depreciable basis, and run your MACRS lookup or VDB formula on the reduced amount. Track bonus expense separately for tax return reporting.

How do I depreciate land in Excel?

Land is not depreciated under U.S. GAAP or IRS rules because it has an indefinite useful life. In an Excel schedule, either exclude land from the CapEx waterfall entirely or include it with a "life" flag that returns zero depreciation. Land improvements — parking lots, fencing, landscaping — are depreciable, typically over 15 years under MACRS.

Should I use SLN or DDB for a startup financial model?

Use SLN for the book view in almost every startup model. Investors want clean, predictable EBITDA that is not distorted by depreciation policy choices. Reserve accelerated methods for the tax view where they belong. If you are modeling a hardware startup with genuinely front-loaded economic obsolescence — like custom silicon or specialized manufacturing tooling — SYD is a defensible middle-ground book policy.

How do I integrate the depreciation schedule with the three-statement model?

Sum the total depreciation row and link the number into a single cell on the P&L D&A line. Link the ending accumulated depreciation row into the balance sheet PP&E section. Add the same P&L depreciation number back on the cash flow statement's operating section — it is a non-cash expense. CapEx from the schedule feeds the investing section. That is the whole linkage — four one-cell references, all sourced from the schedule tab.

Depreciation is one of those model areas where the rules are simple, the mechanics are compact, and the errors compound quietly over ten forecast years. Build the schedule once with vintage rows, sum vertically, reconcile the DTL, and every downstream valuation gets a defensible D&A line for free — and the audit trail that goes with it. If you want the schedule to self-audit as you edit it, VeloraAI's Excel copilot can trace any P&L depreciation cell back to its vintage, flag broken switch-points, and rebuild the deferred tax roll in place. The next step is to plug this schedule into a live three-statement model and watch the balance sheet balance the first time you press F9.