Comparable Company Analysis in Excel: Build Trading Comps (2026)

May 5, 2026 · VeloraAI Team
Financial Modeling Data Analysis Excel

If a managing director asks for a "quick valuation read" on a target, no one is firing up a 50-tab DCF. They are pulling up a comparable company analysis — a one-page table of peer multiples that delivers a defensible valuation range in under an hour. Trading comps are the Swiss Army knife of valuation: faster than a DCF, more market-grounded than a sum-of-the-parts, and the first slide in nearly every pitch book on Wall Street.

This guide walks through how to build a comparable company analysis in Excel the way bankers actually build it — peer selection, enterprise value math, multiple calculation, scrubbing for non-recurring items, and applying the median to your target. Every formula is the real one used in pitch books, not a textbook simplification.

What Is Comparable Company Analysis and Why Do Bankers Live By It?

Comparable company analysis (CCA), also called "trading comps" or "public comps," values a target company by applying the trading multiples of similar publicly traded peers. The logic is simple: if Peer A trades at 12x EBITDA and Peer B at 14x, your target — operating in the same industry with similar margins — should command something in that 12–14x range.

Comps are favored because they are market-based (no assumptions about WACC or terminal growth), defensible (the data is observable in 10-Ks and 10-Qs), and fast (a clean comps table can be built in 2–4 hours). The downside: comps inherit whatever mispricing exists in the market and can mask company-specific risk.

ℹ️ Note: Trading comps tell you what investors are paying for similar businesses today. They are a relative valuation — not an intrinsic one. Always pair comps with a DCF for triangulation.

When Comps Are the Right Tool

Use comparable company analysis when:

  • The target operates in a sector with 5+ liquid public peers
  • You need a fast valuation read for a fairness opinion or pitch
  • Cash flows are too volatile to support a credible DCF (cyclicals, early-stage growth)
  • Buyers explicitly benchmark against public peers (most M&A processes)

Comps are weaker for one-of-a-kind assets, distressed targets, or highly regulated entities where peer multiples are distorted.

How Do You Select the Right Peer Group?

A solid peer group has 5–12 publicly traded companies that match the target on industry, size, growth, profitability, and capital structure. Start with 15–20 candidates from a sector screen, then narrow by filtering on financial similarity. Fewer than 5 peers gives unreliable statistics; more than 12 dilutes signal with noise.

The Five Selection Criteria That Actually Matter

Criterion Acceptable Range vs. Target Why It Matters
Industry / business model Same GICS sub-industry Multiples reflect sector economics
Revenue / market cap ±30% of target Size affects scale advantages and multiples
Revenue growth (NTM) Within ±500 bps High-growth peers trade richer
EBITDA margin Within ±500 bps Profitability drives multiple compression/expansion
Geography Same primary region FX, tax, and demand dynamics differ

A common rookie mistake is including any company in the same SIC code. A $500M specialty chemicals firm is not comparable to BASF — the size, end markets, and capital intensity diverge too far.

💡 Pro Tip: Read the "Competition" section of each candidate's 10-K. Companies usually name their own peer set. If three of your candidates list each other as competitors, you have a tight peer group.

Sources for the Peer Set

Bloomberg Terminal:    RV (Relative Valuation) function
Capital IQ:            "Comps" tab on the company tear sheet
S&P Capital IQ Pro:    Industry → Public Comparables screen
Free alternatives:     SEC EDGAR + finance.yahoo.com + stockanalysis.com

If you do not have terminal access, build the peer list from each candidate's most recent 10-K and proxy statement — both name competitors and benchmark companies explicitly.

How Do You Build a Trading Comps Table in Excel?

A trading comps table has three logical blocks: inputs (raw financials and share data), calculations (enterprise value and multiples), and outputs (summary statistics and the implied valuation range). Build each block on a separate sheet so the model is auditable. The walkthrough below assumes Excel 365 with dynamic arrays.

graph LR
    A[Raw Financials<br/>10-K / 10-Q / Earnings] --> B[Inputs Tab<br/>One row per peer]
    B --> C[Calc Block<br/>EV + Multiples]
    C --> D[Summary Stats<br/>Min / 25th / Median / 75th / Max]
    D --> E[Apply to Target<br/>Implied EV Range]
    E --> F[Implied Equity Value<br/>per Share]

Step 1: Lay Out the Inputs Tab

Create one row per peer with these columns at minimum. Color-code inputs blue, calculations black — the standard banker convention.

Column Field Source
A Ticker —
B Company Name —
C Share Price (current) Yahoo Finance / Bloomberg
D Diluted Shares Outstanding Latest 10-Q cover page
E Total Debt (ST + LT) Latest 10-Q balance sheet
F Cash & Equivalents Latest 10-Q balance sheet
G Preferred Stock Latest 10-Q
H Minority Interest Latest 10-Q
I LTM Revenue Sum of last 4 quarters
J LTM EBITDA Adjusted, sum of last 4 quarters
K NTM Revenue (consensus) Capital IQ / Bloomberg ANR
L NTM EBITDA (consensus) Capital IQ / Bloomberg ANR

⚠️ Warning: Always use diluted shares outstanding (treasury stock method including options, RSUs, and convertibles), not basic shares. Using basic shares understates equity value and overstates multiples.

Step 2: Calculate Enterprise Value for Each Peer

Enterprise value strips out capital structure differences so peers are compared apples-to-apples. The standard formula:

EV = Equity Value + Total Debt + Preferred Stock + Minority Interest − Cash

In Excel, with the layout above, the EV formula in column M is:

=C2*D2 + E2 + G2 + H2 - F2

Where C2*D2 is equity value (share price × diluted shares). For a company trading at $45 with 200M diluted shares, $1.5B in debt, $300M in cash, no preferred or minority interest:

EV = ($45 × 200M) + $1,500M + $0 + $0 − $300M
EV = $9,000M + $1,500M − $300M
EV = $10,200M

Example: Apple-style calc — share price $190 × 15.4B diluted shares = $2,926B equity value. Add $109B debt, subtract $73B cash → EV ≈ $2,962B. The cash net of debt detail matters most for cash-rich tech peers.

Step 3: Calculate the Trading Multiples

Multiples normalize for size so a $5B company can be compared to a $50B peer. The four most-used multiples in trading comps:

EV / Revenue (LTM)  =M2/I2
EV / EBITDA (LTM)   =M2/J2
EV / Revenue (NTM)  =M2/K2
EV / EBITDA (NTM)   =M2/L2
P / E (LTM)         =C2/[LTM EPS]

For sectors with negative or near-zero earnings, EV/Revenue is the primary multiple. For mature, profitable industries, EV/EBITDA dominates because it neutralizes capex policy and capital structure.

💡 Pro Tip: Always show both LTM and NTM multiples side by side. LTM is grounded in audited reality; NTM reflects how the market is actually pricing forward growth. The spread between the two reveals expected earnings momentum.

Step 4: Scrub for Non-Recurring Items

Raw EBITDA from a 10-K is rarely the right number. Strip out:

  • Restructuring charges (severance, facility closures)
  • Impairments and write-downs
  • Litigation settlements (one-time)
  • Gain/loss on asset sales
  • Acquisition-related transaction costs
  • Stock-based compensation (controversial — disclose your treatment either way)

Build a small "Adjustments" block to the right of each peer row so the scrubbing is transparent. A reviewer should be able to tie every adjusted number back to a footnote in the 10-K.

Adjusted EBITDA = Reported EBITDA + Restructuring + Impairments + Other One-Time

Step 5: Compute Summary Statistics

The summary block is the most-quoted output of the comps table. Use these formulas across the multiples column (e.g., EV/EBITDA NTM in column P, peers in rows 2:11):

Min          =MIN(P2:P11)
25th pctile  =PERCENTILE.INC(P2:P11, 0.25)
Median       =MEDIAN(P2:P11)
Mean         =AVERAGE(P2:P11)
75th pctile  =PERCENTILE.INC(P2:P11, 0.75)
Max          =MAX(P2:P11)

Bankers default to the median because it ignores outliers. The mean is reported for completeness but rarely used as the anchor multiple. The 25th and 75th percentiles define the implied valuation range.

ℹ️ Note: If a peer's multiple is more than 2x the median or below half the median, exclude it as an outlier — but disclose the exclusion in a footnote. Common reasons: pending M&A premium, near-bankruptcy distress, or non-comparable accounting.

Step 6: Apply the Multiples to the Target

The final step turns peer multiples into an implied valuation for your target.

Implied EV (low)   = 25th pctile × Target NTM EBITDA
Implied EV (mid)   = Median       × Target NTM EBITDA
Implied EV (high)  = 75th pctile × Target NTM EBITDA

Implied Equity (low)  = Implied EV (low) − Net Debt
Implied Equity (high) = Implied EV (high) − Net Debt

Then divide by diluted shares to get implied price per share. If your target has $400M NTM EBITDA, the median peer multiple is 11.5x, and net debt is $800M:

Implied EV       = 11.5 × $400M = $4,600M
Implied Equity   = $4,600M − $800M = $3,800M
Implied $/share  = $3,800M ÷ 100M shares = $38.00

Show the result as a football field chart alongside DCF and precedent transaction ranges. That visual is what ends up in the pitch book.

graph TD
    A[Target NTM EBITDA<br/>$400M] --> B{Apply Peer Multiple}
    B --> C[Low: 25th pctile<br/>10.2x → $4,080M EV]
    B --> D[Mid: Median<br/>11.5x → $4,600M EV]
    B --> E[High: 75th pctile<br/>13.1x → $5,240M EV]
    C --> F[Subtract Net Debt<br/>− $800M]
    D --> F
    E --> F
    F --> G[Implied Equity Range<br/>$3,280M – $4,440M]
    G --> H[Per Share: $32.80 – $44.40]

What Are the Most Common Mistakes in Trading Comps?

Even senior associates make these errors. Catching them is the difference between a comps table that survives the MD red pen and one that gets thrown out.

  1. Using basic shares instead of diluted. Always apply the treasury stock method to options and RSUs.
  2. Mixing fiscal year ends. Calendarize peers with non-December year ends to a common LTM period.
  3. Failing to scrub EBITDA. Reported EBITDA includes one-time gains/losses that distort multiples.
  4. Including peers with pending M&A. Targets in active deals trade at takeout premiums and skew the median.
  5. Ignoring stub periods. LTM = most recent annual + latest stub − prior year stub. Skipping this overstates revenue.
  6. Stale share counts. Use the share count from the most recent 10-Q cover page, not the prior 10-K.
  7. Currency mismatches. Convert all figures to a single reporting currency before calculating multiples.

⚠️ Warning: A comps table with a peer in active acquisition talks will look "off" — that peer's multiple will be 20–40% above the rest. Always footnote and exclude announced-deal targets, or the median is meaningless.

The "Calendarization" Trap

If your target has a December year-end and a peer has a June year-end, their LTM periods do not overlap. The fix:

Calendarized LTM = Most Recent Full Year
                 + Stub Period (current FY through quarter end)
                 − Stub Period (prior FY through same quarter)

This is grunt work in Excel but non-negotiable for a defensible comps table.

How Does AI Speed Up the Comps Workflow?

Building a 10-peer comps table the traditional way takes 4–6 hours: 60% on data extraction, 30% on scrubbing, 10% on actual analysis. AI-powered Excel tools — like VeloraAI — flip that ratio. Natural-language prompts can pull 10-Q balance sheet items, calculate diluted share counts under the treasury stock method, and flag non-recurring EBITDA adjustments by reading the MD&A section directly.

The analytical parts — peer selection judgment, deciding which adjustments matter, choosing the right multiple — still require an analyst's brain. But the data plumbing that consumes most of the day can largely be automated. The result is a comps table built in 60–90 minutes that an analyst spends time interrogating rather than assembling.

💡 Pro Tip: Even with AI extraction, manually verify the diluted share count and net debt for every peer. These two numbers drive equity value and EV — getting them wrong cascades through every multiple.

Frequently Asked Questions

What is the difference between trading comps and precedent transactions?

Trading comps use current public market multiples for similar peers (no control premium). Precedent transactions use historical M&A deal multiples at which similar companies were acquired (includes a control premium of typically 20–35%). Trading comps yield a "minority interest" valuation; precedents yield an "acquisition" valuation. Most pitch books show both side by side.

How many peers do I need for a credible comps analysis?

Aim for 5–12 peers. Below 5, the median is statistically unreliable and one outlier dominates the result. Above 12, you start including marginal comparables that dilute the signal. Investment banks typically settle on 6–10 peers after screening 15–20 candidates and removing those that fail the size, growth, or profitability filters.

Why use EV/EBITDA instead of P/E ratio?

EV/EBITDA is capital-structure neutral — it values the entire enterprise before financing decisions. P/E is distorted by leverage (a heavily indebted firm has a lower P/E for the same operating performance). EV/EBITDA also strips out depreciation policy differences. P/E remains useful for banks, REITs, and insurance companies where EBITDA is not a meaningful operating metric.

Should I use mean or median for the comps multiple?

Use the median. The mean is sensitive to outliers — one peer trading at 30x in a group of 20x companies pulls the mean up artificially. The median is the robust central tendency banks anchor to. Report the mean alongside it for transparency, but draw your valuation range from the 25th–75th percentile band centered on the median.

How often should I update a comps table?

Update share prices and consensus estimates daily during a live deal; balance sheet items quarterly when 10-Qs drop. The full peer set should be re-screened every 6–12 months — companies get acquired, spin off divisions, or drift out of comparability. A stale comps table is one of the fastest ways to lose credibility in a pitch.

Building Comps That Hold Up to Scrutiny

A well-built comparable company analysis is judged not on its sophistication but on its defensibility. Every peer choice, every adjustment, and every multiple should have a clear rationale a reviewer can poke at and you can defend. Get the inputs right, scrub with discipline, and let the median speak for itself.

If you build comps tables regularly, an AI-powered Excel add-in like VeloraAI can shoulder the data extraction and adjustment grunt work — leaving you free to focus on peer selection and storytelling, the parts where analyst judgment actually matters. Open a recent 10-K next to your comps tab, prompt for the diluted share count and net debt, and watch hours of formatting collapse into minutes.

Sources: