DuPont Analysis in Excel: 3-Step & 5-Step ROE Decomposition (2026)

June 29, 2026 · VeloraAI Team
Data Analysis Financial Modeling Excel

A 25% ROE looks great until you decompose it and find it's all leverage — the same number that made WaMu look unstoppable in 2007. DuPont analysis in Excel is the technique that separates real operating quality from financial engineering, and it takes about ten minutes to build once you understand the algebra. Originally developed at the DuPont Corporation in the 1920s by Donaldson Brown, it remains the single most important ratio decomposition framework on the CFA Level I curriculum and on every credit committee that funds your borrower's revolver.

This guide walks through both the 3-step and 5-step DuPont formula in Excel, with working formulas, a Walmart-vs-Amazon worked example, and the audit traps that quietly distort the answer.

What Is DuPont Analysis?

DuPont analysis is a framework that decomposes Return on Equity (ROE) into the underlying operational, efficiency, and leverage drivers that produced it. Instead of treating ROE as a single number, the model rewrites it as a product of ratios so you can attribute changes year-over-year or differences across peers to specific causes.

The core insight: two companies can post identical 18% ROEs while running completely different businesses. One earns thin margins on rapid asset turnover (a grocer); another earns wide margins on slow-turning capital (a software firm); a third earns mediocre returns on a heavy debt load (a utility or a bank). DuPont makes those differences visible.

Why DuPont Still Matters in 2026

Modern dashboards and AI tools surface ROE in seconds — but decomposition is what makes the number actionable. A CFO doesn't fix "ROE went down." A CFO fixes "asset turnover compressed by 12% because we built inventory ahead of a launch that slipped." DuPont gives you that diagnostic.

💡 Pro Tip: Always use average equity and average assets in DuPont ratios, not period-end balances. A late-year debt paydown or share buyback can swing point-in-time leverage by 15-20% and contaminate every downstream ratio.

The 3-Step DuPont Formula in Excel

The 3-step model is the classic version and the one most analysts memorize. It splits ROE into three intuitive drivers:

ROE = Net Profit Margin × Asset Turnover × Equity Multiplier

Algebraically, the identity is trivial — (NI/Sales) × (Sales/Assets) × (Assets/Equity) simplifies straight back to NI/Equity. The power is in the interpretation.

Component Formula What It Measures Driver
Net Profit Margin Net Income / Revenue Pricing power & cost control Operating efficiency
Asset Turnover Revenue / Average Total Assets Capital intensity Asset use efficiency
Equity Multiplier Average Total Assets / Average Equity Balance sheet leverage Financial leverage
ROE Product of the three Return to shareholders Combined effect

How Do You Build a 3-Step DuPont Model in Excel?

To build a 3-step DuPont model in Excel, lay out four inputs (Net Income, Revenue, Average Total Assets, Average Equity), compute three ratios (Net Profit Margin, Asset Turnover, Equity Multiplier), then multiply them. Always use averages for balance sheet items and label each line clearly so the decomposition is auditable.

Here is the minimal Excel block. Assume FY2025 inputs in column C and FY2024 in column B:

A4:  Net Income           C4: 12,500
A5:  Revenue              C5: 180,000
A6:  Avg Total Assets     C6: 95,000
A7:  Avg Equity           C7: 42,000

A10: Net Profit Margin    C10: =C4/C5
A11: Asset Turnover       C11: =C5/C6
A12: Equity Multiplier    C12: =C6/C7
A13: ROE (DuPont)         C13: =C10*C11*C12
A14: ROE (Check)          C14: =C4/C7

C13 and C14 must equal — if they don't, you have a balance error or a missing component. Use this as a built-in audit.

Computing Averages Properly with AVERAGE

The most common error in junior models is using period-end equity. Fix it with a named range and AVERAGE():

=AVERAGE(BalanceSheet!$E$22, BalanceSheet!$F$22)

Or, for a rolling 5-year DuPont, use OFFSET or INDEX to grab the prior period:

=AVERAGE(INDEX($B5:$F5, COLUMN()-1), INDEX($B5:$F5, COLUMN()-2))

⚠️ Warning: If you copy a DuPont template from one company to another, re-check the equity definition. Some analysts use Total Stockholders' Equity (incl. minority interest), others use Common Equity only. Mixing the two when comparing peers makes the equity multiplier non-comparable and torpedoes the analysis.

The 5-Step DuPont Formula (Extended Version)

The 5-step model splits Net Profit Margin into three further ratios that isolate operating performance, financing cost, and tax effects. This is the version used in credit analysis, where understanding why profitability changes matters more than the headline number.

ROE = Tax Burden × Interest Burden × EBIT Margin × Asset Turnover × Equity Multiplier
Component Formula Reveals
Tax Burden Net Income / EBT Effective tax rate (1 − ETR)
Interest Burden EBT / EBIT Drag from interest expense
EBIT Margin EBIT / Revenue True operating profitability
Asset Turnover Revenue / Avg Assets Capital intensity
Equity Multiplier Avg Assets / Avg Equity Financial leverage

The first three terms collapse to Net Profit Margin: (NI/EBT) × (EBT/EBIT) × (EBIT/Sales) = NI/Sales. But by separating them, you can see whether a margin change came from a tax restructuring, a refinancing, or a real change in operations.

graph TD
    A[ROE] --> B[Net Profit Margin]
    A --> C[Asset Turnover]
    A --> D[Equity Multiplier]
    B --> E[Tax Burden<br/>NI / EBT]
    B --> F[Interest Burden<br/>EBT / EBIT]
    B --> G[EBIT Margin<br/>EBIT / Revenue]
    C --> H[Revenue / Avg Assets]
    D --> I[Avg Assets / Avg Equity]

How Do You Calculate the 5-Step DuPont in Excel?

Calculate the 5-step DuPont in Excel by adding EBIT and EBT (Earnings Before Tax) as inputs alongside Net Income and Revenue. Then build five ratios — tax burden, interest burden, EBIT margin, asset turnover, and equity multiplier — and multiply them. The product equals ROE and isolates the operating, financing, and tax drivers.

Extend the 3-step template with two more inputs and three more ratios:

A4:  Net Income           C4:  12,500
A5:  EBT                  C5:  16,200
A6:  EBIT                 C6:  19,500
A7:  Revenue              C7:  180,000
A8:  Avg Total Assets     C8:  95,000
A9:  Avg Equity           C9:  42,000

A12: Tax Burden           C12: =C4/C5
A13: Interest Burden      C13: =C5/C6
A14: EBIT Margin          C14: =C6/C7
A15: Asset Turnover       C15: =C7/C8
A16: Equity Multiplier    C16: =C8/C9
A17: ROE (5-Step DuPont)  C17: =PRODUCT(C12:C16)
A18: ROE (Check)          C18: =C4/C9

Using PRODUCT() instead of chained multiplication makes the formula scan-able and lets you add or remove factors without rewriting the chain.

ℹ️ Note: For multinationals reporting under US GAAP, non-operating items (gains on sales, impairments, restructuring) flow through to EBT but distort the Interest Burden ratio. For comparison work, recompute EBIT using operating income from the income statement rather than EBT + Interest Expense, or strip out non-operating lines explicitly.

Real-World DuPont Analysis: Walmart vs Amazon

DuPont's value becomes obvious when you compare two businesses that look similar on a single metric. Take Walmart and Amazon, both retailers with double-digit ROEs but very different engines.

Metric (FY2025E) Walmart Amazon Insight
Net Profit Margin 2.8% 6.4% Amazon's AWS lifts blended margin
Asset Turnover 2.5× 1.1× Walmart's stores churn inventory fast
Equity Multiplier 3.0× 2.7× Walmart slightly more levered
ROE 21.0% 19.0% Comparable headline...

The headline ROEs are close, but the shape is opposite. Walmart is a low-margin, high-turnover business with modest leverage — classic retail. Amazon is a higher-margin, capital-intensive business carried by AWS and ad revenue. A change in either driver tells you something totally different:

  • If Walmart's Asset Turnover slipped from 2.5× to 2.2×, that's a 12% efficiency hit — probably inventory build or fulfillment investment.
  • If Amazon's Net Profit Margin compressed from 6.4% to 4.5%, that's a 30% margin hit — AWS price wars, retail loss leaders, or new infrastructure spend.

Example: For a five-year trend analysis, build the DuPont decomposition as a horizontal Excel block — years across columns C:G, components down rows. Add a YoY delta row using =C13/B13-1 so you can spot which driver moved most. Conditional format the deltas with a 3-color scale to flag deteriorating components instantly.

Visualizing DuPont Trends

A small stacked-contribution chart in Excel makes year-over-year changes obvious. Convert each component to its log contribution to ROE:

=LN(C10)+LN(C11)+LN(C12)

Then plot the log contribution of each driver as a stacked column. Years where leverage dominates the bar stand out immediately — usually the years you should worry about.

Common DuPont Analysis Mistakes

Even experienced analysts make these errors. Audit your model against this list before relying on the output.

1. Using Period-End vs Average Balance Sheet Values

The numerator (Net Income, Revenue) is a flow measured over the period. The denominator (Assets, Equity) is a stock at a point in time. Mismatching them creates artificial distortions, especially after a large capital event mid-year. Always use the average of beginning and ending balances:

=(BeginningEquity + EndingEquity) / 2

2. Ignoring Minority Interest

When a parent consolidates a partially-owned subsidiary, total assets includes 100% of the sub but equity attributable to common shareholders excludes the minority stake. The equity multiplier inflates. The fix: use total equity (including non-controlling interests) in the multiplier, and reconcile net income to attributable net income separately.

3. Comparing Across Industries Without Adjustment

A bank's equity multiplier is typically 10-15×. A software firm's is 1.5-2×. Comparing their ROEs head-to-head without context is meaningless. Use DuPont to compare within an industry or to track a single company over time.

4. Forgetting That High ROE Isn't Always Good

A 40% ROE driven entirely by leverage (Equity Multiplier of 8×) means the company is one rate cycle from a covenant breach. DuPont surfaces this; a single ROE figure hides it.

⚠️ Warning: When ROE rises and the Equity Multiplier rises proportionally while Margin and Turnover are flat, the company is just borrowing more. That's not improving operations — that's just rearranging the capital structure, and it reverses the moment refinancing costs go up.

5. Using GAAP Net Income for Operationally-Focused DuPont

For analyzing operational health, normalize Net Income for one-time items: restructuring charges, gains on sale, impairments, and discontinued operations. Otherwise your trend is noise.

DuPont Analysis Beyond ROE: ROIC and ROA Variants

The DuPont algebra generalizes. You can decompose any return metric the same way.

DuPont for ROA (Return on Assets)

Drop the leverage term to isolate operating performance:

ROA = Net Profit Margin × Asset Turnover
=C4/C5 * C5/C6

ROA is the better lens when comparing companies with different capital structures, because it strips out the leverage signal entirely.

DuPont for ROIC (Return on Invested Capital)

For corporate finance work, ROIC matters more than ROE because it measures returns on all capital deployed (debt + equity) without leverage noise:

ROIC = NOPAT / Invested Capital
     = (EBIT × (1 - Tax Rate)) / (Debt + Equity)

Decomposed:

ROIC = (NOPAT / Revenue) × (Revenue / Invested Capital)
     = NOPAT Margin × Capital Turnover

In Excel:

A20: NOPAT              C20: =C6*(1-0.21)
A21: Invested Capital   C21: =DebtBS + EquityBS
A22: NOPAT Margin       C22: =C20/C7
A23: Capital Turnover   C23: =C7/C21
A24: ROIC               C24: =C22*C23

💡 Pro Tip: Compare ROIC to the company's WACC. ROIC > WACC means the business is creating value; ROIC < WACC means it's destroying value regardless of how high ROE appears. This single comparison is the foundation of value-based management.

Building a Reusable DuPont Template

For repeated use across a peer group, build the model with these conventions:

  1. Inputs sheet — raw income statement and balance sheet items, one column per company or per year. Use blue font for hard-coded inputs (standard banker convention).
  2. Ratios sheet — all DuPont components as named ranges. Black font for formulas, color the row green if the component is improving YoY.
  3. Summary sheet — single-screen view with both 3-step and 5-step decompositions plus a sparkline per component.
  4. Audit row — every column has the NI/Equity cross-check, and the file does not pass review until every audit row is zero.
=IF(ROUND(ROE_DuPont - ROE_Direct, 4) = 0, "✓", "ERROR")

For peer screens of 10+ companies, lay the decomposition horizontally and use XLOOKUP for financial analysis to pull components into a comparison block:

=XLOOKUP("Net Profit Margin", $A:$A, Company1!C:C)

This is exactly the pattern AI-assisted Excel tools like VeloraAI's add-in automate — pulling the right line item from each company's financials, building the decomposition, and flagging anomalies — so analysts can spend their time on interpretation instead of cell construction.

Frequently Asked Questions

What is the difference between 3-step and 5-step DuPont analysis?

The 3-step DuPont decomposes ROE into Net Profit Margin × Asset Turnover × Equity Multiplier. The 5-step further splits Net Profit Margin into Tax Burden, Interest Burden, and EBIT Margin. Use the 5-step when you need to isolate whether margin changes came from operations, financing, or taxes — which matters for credit analysis and forensic accounting.

Why does my DuPont ROE not equal Net Income divided by Equity?

The most likely cause is mismatched periods: using period-end equity for the multiplier but full-year net income on top, or using different equity definitions across the two formulas. Re-check that you used the same average equity figure and that nothing is rounded too aggressively. Even rounding to two decimals can introduce a 0.5-1% gap.

What is a good ROE using DuPont analysis?

A healthy ROE in most industries is 15-20%, but the decomposition matters more than the level. An ROE of 25% driven by a 4× equity multiplier is far riskier than 15% from balanced margin, turnover, and modest leverage. Always compare the components to the company's industry median, not the headline number to a universal benchmark.

Can DuPont analysis be used for banks?

Yes, but with modifications. Bank DuPont uses Equity / Assets (the inverse of the equity multiplier) and replaces Asset Turnover with Net Interest Margin and non-interest income ratios. The CAMELS framework borrows directly from the DuPont logic. For commercial bank analysis, see specialized bank ROE decompositions rather than the standard 3-step form.

What software is best for DuPont analysis?

Excel remains the standard because the model is small, transparent, and easy to audit. Specialized FP&A tools (Adaptive Insights, Anaplan) automate the calculation across multiple entities. AI add-ins like VeloraAI generate the decomposition from raw financial statements with a natural-language prompt, which speeds up peer screens substantially when you're comparing 10+ companies.

From Single Ratio to Real Diagnosis

DuPont analysis is one of those classic frameworks that survives every wave of new finance technology because the underlying algebra never changes. ROE will always be margin × turnover × leverage, and the questions a CFO asks about the business will always map back to those three knobs. Build the decomposition once, audit it carefully, and you have a tool that works on every company in every industry for the rest of your career.

The next step: extend your DuPont template into a full trend dashboard — add a five-year horizontal layout with sparklines for each component and a peer comparison block using XLOOKUP. That single workbook will replace most of the standalone ratio analyses on your desk.