Capex Schedule in Excel: PP&E and Depreciation Model (2026)

May 29, 2026 · VeloraAI Team
Financial Modeling Formulas Excel

Ask any senior analyst what breaks a three-statement model first, and the answer is almost always the same: the capex schedule. Hard-coded depreciation, plug-fixed PP&E, disposals that vanish into thin air — these errors compound across every forecast year and silently corrupt EBITDA, free cash flow, and ultimately valuation. A clean capex schedule in Excel is the single most important sub-schedule you will build, because it is the only place where the income statement (depreciation), balance sheet (net PP&E), and cash flow statement (capex outflow) all converge.

This guide walks through how to build a production-grade capital expenditure schedule from scratch — the gross PP&E rollforward, the depreciation waterfall, asset disposals, and the formulas that link it all back to the financials.

💡 Pro Tip: Build your capex schedule as a separate tab with a clear rollforward structure (BoP → additions → disposals → EoP). Embedding it inside the balance sheet is the #1 cause of broken three-statement models.

What Is a Capex Schedule and Why Does It Matter?

A capex schedule (also called a PP&E schedule or fixed asset schedule) is a sub-model that tracks gross property, plant, and equipment from period to period, layers on a depreciation waterfall for each vintage of capital spend, and produces three outputs: depreciation expense for the income statement, net PP&E for the balance sheet, and capex for the cash flow statement.

It matters because depreciation is mechanical, not assumed. If you simply forecast "D&A as % of revenue," you are guessing — and you are guaranteed to mis-state book value, deferred tax, and free cash flow over a multi-year horizon. A vintage-based capex schedule fixes this.

The three outputs of a capex schedule

Output Flows To Driven By
Depreciation expense Income statement (above EBIT) Useful life × prior + current capex
Net PP&E (book value) Balance sheet (non-current assets) Gross PP&E − accumulated depreciation
Capital expenditure Cash flow statement (investing) Maintenance capex + growth capex
Disposal proceeds Cash flow statement (investing) Gain/loss × NBV of disposed assets
Deferred tax (advanced) Balance sheet (liabilities) Book vs. tax depreciation difference

ℹ️ Note: For most operating models, a single asset class with one useful life is sufficient. Multi-class schedules (buildings, machinery, IT, vehicles) are only needed when assets have materially different useful lives or tax treatments.

How Do You Build a Capex Schedule in Excel?

To build a capex schedule in Excel, set up a horizontal time-series layout with rows for opening gross PP&E, additions (capex), disposals, and closing gross PP&E. Below that, build a depreciation waterfall where each row represents a vintage of capex and each column applies one year of straight-line depreciation. Sum the waterfall vertically to get total depreciation per period.

Step 1: Lay out the time axis and inputs

In a new tab named Capex, put years across row 5 (=Assumptions!B5# if you use dynamic arrays, or hard-code 2024, 2025, ... for historicals). Reserve rows 7-15 for the gross PP&E rollforward, rows 17-40 for the depreciation waterfall, and rows 42-50 for the net PP&E summary.

Inputs you will need from the assumptions tab:

  • Maintenance capex (often as % of revenue or % of prior-year PP&E)
  • Growth capex (driven by capacity expansion plans or strategic investment)
  • Useful life (commonly 5-10 years for equipment, 25-40 for buildings)
  • Depreciation method (straight-line for most operating models)
  • Salvage value (often $0 for simplicity unless materially different)

Step 2: Build the gross PP&E rollforward

The gross PP&E rollforward is a simple BoP → EoP structure. In column C (first forecast year), enter:

Opening Gross PP&E      =B11        // prior period closing
(+) Capex additions     =Assumptions!C20
(−) Disposals at cost   =Assumptions!C21
Closing Gross PP&E      =C7+C8-C9

Drag right across the forecast horizon. The opening balance always equals the prior period's closing balance — never plug it.

⚠️ Warning: Never let "closing gross PP&E" be a free input. It must be a calculated output of opening + additions − disposals. If a reviewer can overwrite the closing balance, your schedule is broken.

Step 3: Build the depreciation waterfall

This is the heart of the schedule. Each row of the waterfall represents the depreciation stream for capex spent in one specific year. Each column applies the depreciation rate for that year.

Assume a 10-year useful life and straight-line depreciation. For capex of $100M in 2024, you depreciate $10M per year for 10 years. For capex of $120M in 2025, you depreciate $12M per year — and that stream is offset one column to the right.

// Row 19: Depreciation of 2024 capex vintage
// In column C (2024), formula:
=IF(AND(C$5>=$B19, C$5<$B19+UsefulLife), $A19/UsefulLife, 0)

// Where:
//   C$5 = current year header
//   $B19 = vintage year (2024)
//   $A19 = capex amount for that vintage
//   UsefulLife = named range (e.g., 10)

The $ anchors are critical: lock the row reference for the year header (C$5), the column for vintage year ($B19), and the column for capex amount ($A19). This lets you drag the formula across years and down vintages without breaking.

Example: If 2024 capex = $100M and useful life = 10 years, then row 19 shows $10M in columns 2024 through 2033 and $0 elsewhere. Row 20 (2025 vintage of $120M) shows $12M in columns 2025 through 2034.

Step 4: Sum the waterfall for total depreciation

In row 38 (or wherever your waterfall ends), sum each column:

=SUM(C19:C37)

This gives total depreciation expense for the period — every vintage that is still being depreciated in that year contributes its annual slice. This is the number that flows to the income statement.

Step 5: Calculate net PP&E

Below the waterfall, compute accumulated depreciation as a running sum, then net book value:

Accumulated depreciation  =B43+C38      // prior accum + current period D&A
Net PP&E (book value)    =C11-C43       // gross PP&E - accumulated depreciation

Sanity check: net PP&E should never go negative. If it does, you have either depreciated assets past zero (useful life mismatch) or your disposals are inconsistent with your accumulated depreciation removal.

graph TD
    A[Capex Assumptions: Maintenance + Growth] --> B[Gross PP&E Rollforward]
    B --> C[Vintage-Based Depreciation Waterfall]
    C --> D[Total Depreciation per Period]
    D --> E[Income Statement: D&A Expense]
    B --> F[Accumulated Depreciation]
    F --> G[Net PP&E on Balance Sheet]
    B --> H[Cash Flow Statement: Capex Outflow]

What Is the Best Depreciation Method for a Financial Model?

For nearly all operating models and DCF valuations, straight-line depreciation is the right choice because it matches GAAP/IFRS book reporting, produces stable EBITDA, and avoids over-engineering. Use accelerated methods (MACRS, double-declining) only when you are explicitly modeling tax depreciation for a tax equity, project finance, or deferred tax build.

Straight-line vs. accelerated depreciation

Method Use Case Excel Formula Pros Cons
Straight-line Operating models, DCFs, M&A =Cost / UsefulLife Simple, matches book reporting Doesn't reflect tax timing
MACRS Tax depreciation, project finance =VDB() or lookup table Matches IRS tax rules Complex, US-specific
Double-declining Aggressive book methods, certain industries =DDB(cost, salvage, life, period) Front-loads expense Diverges from cash
Units-of-production Mining, oilfield, manufacturing =Cost × (Units / TotalUnits) Matches asset use Needs production forecast
Sum-of-years-digits Rare in modern models =SYD(cost, salvage, life, per) Front-loaded but smoother than DDB Outdated

When to model two depreciation streams

If you are building a model that needs to capture deferred tax liabilities, you must run two parallel depreciation schedules: one on a book basis (straight-line) and one on a tax basis (MACRS or local equivalent). The difference in cumulative depreciation × tax rate = deferred tax liability.

💡 Pro Tip: For DCF valuations, use straight-line book depreciation for EBIT and a separate tax depreciation schedule to compute cash taxes. This is the single biggest source of valuation differences between bankers and corporate finance teams.

How Do You Forecast Capex in a Financial Model?

Capex forecasts should always be split into maintenance capex (the spend required to keep existing capacity running) and growth capex (the spend that adds new capacity or capability). The two have different drivers, different sensitivities, and very different impacts on free cash flow at terminal.

Three common forecasting methods

  1. % of revenue — Simple, works for stable industries. Use historical capex / revenue as the benchmark.
  2. % of prior-year gross PP&E — Better for asset-heavy businesses where capex tracks the existing asset base.
  3. Bottom-up project-level capex — For project finance, real estate, or capacity expansion models. Each project has its own timing, amount, and useful life. In infrastructure and energy deals, this approach underpins the entire construction budget in a project finance model in Excel, where capex timing drives debt drawdowns and IDC calculations.
// Method 1: Maintenance capex = % of revenue
Maintenance_Capex = Revenue × MaintCapexPct

// Method 2: Maintenance capex = % of gross PP&E
Maintenance_Capex = OpeningGrossPPE × MaintCapexPct

// Method 3: Growth capex = additive project amounts
Growth_Capex = SUMIF(ProjectYear, ForecastYear, ProjectCapex)

⚠️ Warning: At terminal year in a DCF, maintenance capex should approximately equal depreciation. If your terminal capex is materially lower than D&A, you are implicitly assuming the asset base shrinks — which is inconsistent with perpetual growth. Reviewers will flag this immediately.

Modeling disposals

Asset disposals are often ignored in simple models, but for mature businesses they matter. When an asset is sold or retired, you must:

  1. Remove its original cost from gross PP&E
  2. Remove its accumulated depreciation from the contra-asset
  3. Recognize the difference between proceeds and NBV as a gain/loss on the income statement
  4. Show cash proceeds in the investing section of the cash flow statement
// Disposal at cost (column reduction in gross PP&E)
Disposal_Cost = SUMIF(DisposalYear, ForecastYear, DisposalAtCost)

// Accumulated depreciation removed
AccDep_Removed = Disposal_Cost - NBV_at_Disposal

// Gain/loss on disposal
GainLoss = Proceeds - NBV_at_Disposal

Linking the Capex Schedule to the Three Statements

A capex schedule that isn't linked to the financials is just an exhibit. The whole point is to drive three line items dynamically in your three-statement financial model.

Income statement linkage

// On the IS tab:
D&A expense = Capex!Row38  (total depreciation per period)
Gain/(loss) on disposal = Capex!Row45  (if modeling disposals)

Balance sheet linkage

// On the BS tab:
Gross PP&E = Capex!Row11        (closing gross PP&E)
Accumulated depreciation = Capex!Row43  (running accumulated D&A)
Net PP&E = Capex!Row44          (NBV = gross - accumulated)

Cash flow statement linkage

// On the CFS tab:
Depreciation (add-back in CFO) = Capex!Row38     (non-cash)
Capital expenditures (CFI) = -Capex!Row8         (cash outflow, negative)
Proceeds from disposals (CFI) = Capex!Row47      (cash inflow, positive)

graph LR
    A[Capex Schedule Tab] -->|D&A expense| B[Income Statement]
    A -->|Capex outflow| C[Cash Flow Statement]
    A -->|Disposal proceeds| C
    A -->|Gross & Net PP&E| D[Balance Sheet]
    A -->|Accumulated depreciation| D
    B -->|Net income| C
    D -->|Closes the loop| C

ℹ️ Note: The balance sheet check (assets = liabilities + equity) will only balance if the depreciation flowing through the IS equals the depreciation reducing net PP&E on the BS, and equals the non-cash add-back on the CFS. One number, three places. Trace it carefully.

Common Capex Modeling Mistakes (and How to Avoid Them)

After auditing hundreds of models, these are the errors that show up most often. None are technically hard to fix — they exist because analysts treat the capex schedule as an afterthought.

Mistake 1: Plugging closing PP&E instead of calculating it

Closing gross PP&E must always equal opening + additions − disposals. If anyone can overwrite the closing balance directly, the rollforward is broken. Lock the formula and protect the cell.

Mistake 2: Depreciating disposed assets

When an asset is disposed, its remaining depreciation stream must stop. In a vintage waterfall, this usually means flagging the disposal year and zeroing out the row from that column forward. A common shortcut:

=IF(AND(C$5>=$B19, C$5<$B19+UsefulLife, C$5<$DisposalYear19), $A19/UsefulLife, 0)

Mistake 3: Useful life inconsistencies

If your assumptions say 10 years but your waterfall depreciates over 8, you will under-state book value and over-state EBIT. Drive useful life from a single named range and reference it everywhere.

Mistake 4: Capex flowing wrong direction in CFS

Capex is a cash outflow and must appear as a negative number under cash flow from investing. The IS shows depreciation (non-cash); the CFS shows capex (cash). Confusing the two is the most common interview-level error.

Mistake 5: Ignoring leased assets (IFRS 16 / ASC 842)

Since 2019, most operating leases sit on the balance sheet as right-of-use (ROU) assets — see our ASC 842 lease accounting guide for the full ROU asset roll-forward and liability amortization. They have their own amortization schedule that mirrors a capex/depreciation waterfall but should generally live in a separate "Leases" tab.

💡 Pro Tip: For deals and DCFs, normalize ROU asset amortization out of EBITDA when computing comparable trading multiples. Pre-IFRS 16 and post-IFRS 16 EBITDA are not directly comparable.

Stress Testing Your Capex Schedule

Once the schedule is built and linked, run three quick stress tests before declaring it done.

Test 1: Sanity check at terminal year

In your terminal year, maintenance capex should approximate depreciation. If revenue grows at 3% forever but your terminal capex is 50% of D&A, the asset base is shrinking — which is inconsistent with perpetual growth.

Test 2: Capex spike scenario

Toggle capex up by 50% in the next year. Net PP&E should rise, depreciation should rise gradually over the useful life (not all at once), and FCF should drop in year 1 then partially recover.

Test 3: Useful life sensitivity

Change useful life from 10 to 7 years. Depreciation should rise meaningfully, EBIT should fall, and net PP&E at terminal should fall. If nothing changes, your formulas are not properly linked.

⚠️ Warning: If depreciation does not move when you change useful life, the most likely culprit is a hard-coded depreciation rate inside the waterfall. Search for any cells in the depreciation block that don't reference your useful life input.

Building a Capex Schedule Faster with AI

Vintage waterfalls are mechanically tedious — every row anchored differently, every formula one of dozens of subtle variations. This is exactly the kind of work AI is excellent at automating. With VeloraAI, you can describe the structure in plain English ("build a 10-year straight-line depreciation waterfall starting in 2024 with vintages running 15 years") and have the formulas, anchors, and links generated directly into your workbook. Analysts using VeloraAI for schedule construction routinely cut build time on capex, debt, and working capital schedules from hours to minutes — with fewer anchor errors and tighter audit trails.

Frequently Asked Questions

What is the difference between capex and depreciation in Excel?

Capex is the cash spend on long-lived assets in a given period — it appears in the cash flow statement and increases gross PP&E on the balance sheet. Depreciation is the non-cash allocation of that capex over the asset's useful life — it appears as an expense on the income statement and reduces net PP&E. They are linked but never equal in any single period.

How do you forecast capex as a percentage of revenue?

The most common approach is to take three years of historical capex divided by revenue, average the ratio, and apply it to forecast revenue. For mature businesses, this ratio is typically 3-8%; for capital-intensive industries (telecom, utilities, oil & gas), 10-25%. Always split into maintenance and growth capex when the strategy plan calls for capacity expansion.

Should I use SLN, DDB, or VDB for depreciation in Excel?

Use SLN(cost, salvage, life) for straight-line book depreciation in operating models. Use VDB(cost, salvage, life, start, end) when you need MACRS-style tax depreciation with a switch to straight-line. Use DDB only for double-declining without the switch. For most operating models, straight-line is the right answer because it matches GAAP reporting and produces stable EBITDA forecasts.

How do I model maintenance capex vs. growth capex separately?

Build them as two input lines in your assumptions tab. Maintenance capex is typically modeled as a % of revenue or % of prior-year gross PP&E. Growth capex is modeled bottom-up by project, with explicit timing and amount. At the terminal year of a DCF, maintenance capex should approximate depreciation — this is the standard reviewer check for terminal value consistency.

Why doesn't my balance sheet balance after I add a capex schedule?

The single most common cause is that depreciation flowing to the income statement does not match the depreciation reducing net PP&E on the balance sheet. Trace one period: D&A on the IS, accumulated depreciation movement on the BS, and the non-cash add-back on the CFS must all reference the same cell. If they reference different cells, fix the links.

Next Steps

A working capex schedule is the foundation of every credible three-statement model. Once you have the gross PP&E rollforward, the vintage-based depreciation waterfall, and the three financial statement links wired up, you have eliminated the largest single source of model errors in operating and DCF models.

The next schedules to build out are the debt schedule (interest expense, principal amortization) and the working capital schedule (DSO, DPO, DIO) — together with the capex schedule, these three sub-models drive almost every forecast line in a professional financial model.