Precedent Transactions Analysis in Excel: M&A Comps Guide (2026)
A senior M&A banker once told a junior analyst: "Trading comps tell you what a company is worth on a Tuesday. Precedent transactions tell you what someone actually paid to own it." That distinction is the entire reason precedent transactions analysis in Excel exists — and why no investment bank pitch book is complete without it.
If you've already mastered comparable company analysis, precedent transactions are the natural next step. They capture control premiums, synergy value, and strategic intent that public trading multiples simply cannot. This guide walks through building a full M&A transaction comps model in Excel — from screening deals to deriving an implied valuation range — with formulas you can copy into your next pitch.
What Is Precedent Transactions Analysis?
Precedent transactions analysis (also called transaction comps or M&A comps) is a valuation method that derives a target company's value from multiples paid in historical acquisitions of similar businesses. Bankers use it alongside DCF and trading comps to triangulate a defensible deal price, typically expressed as an EV/EBITDA or EV/Revenue range.
Unlike trading comps, precedent transactions reflect a control premium — the extra value an acquirer pays to take 100% ownership and direct strategy. That premium historically averages 20–40% over an unaffected share price, depending on sector and deal type.
ℹ️ Note: Precedent transactions are typically the highest of the three core valuation methodologies (DCF, trading comps, transactions) because they include both the control premium and expected synergy value baked into the purchase price.
How Do You Build a Precedent Transactions Analysis in Excel?
To build a precedent transactions analysis in Excel, follow these seven steps: (1) screen comparable deals, (2) collect transaction data, (3) calculate enterprise value paid, (4) compute LTM and forward multiples, (5) scrub outliers, (6) apply statistics to the target, and (7) derive a valuation range. Each step uses standard Excel formulas — no add-ins required.
The Full Workflow at a Glance
graph TD
A[Screen Deals: Industry, Size, Date] --> B[Collect Data: Price, EBITDA, Debt]
B --> C[Calculate Equity Value & Enterprise Value]
C --> D[Compute LTM EV/EBITDA, EV/Revenue, P/E]
D --> E[Scrub Outliers: Distressed, Tax-Driven, Carve-Outs]
E --> F[Apply Min/Median/Max to Target Metrics]
F --> G[Derive Implied Valuation Range]
Step 1: Screen for Truly Comparable Deals
The single biggest mistake junior analysts make is including deals that aren't actually comparable. A 2015 acquisition of a distressed competitor at fire-sale prices is not a relevant comp for a healthy 2026 strategic buyout.
Use these five filters when screening:
- Industry / sub-sector — same SIC or GICS code, ideally same business model
- Geography — North America vs. EU vs. APAC trade at different multiples
- Deal size — within ~0.5x to 3x of target EV
- Recency — last 3–5 years, with sensitivity to interest rate regime
- Deal type — strategic vs. financial sponsor, public vs. private target
💡 Pro Tip: Build a screening table with binary flag columns (Industry Match, Size Match, Recent Match, Clean Deal). Use a SUMPRODUCT to score each deal, then filter to deals scoring 4/4. This forces consistency across analysts on the same team.
=SUMPRODUCT(--(B2:E2="Y"))
If column B–E are your four binary flags, the formula returns a 0–4 score. Filter for ≥ 3 to keep your peer set tight.
Where to Source Deal Data
| Source | Coverage | Best For | Cost |
|---|---|---|---|
| S&P Capital IQ | Global M&A, full financials | Bulge bracket / MM banks | $$ |
| Mergermarket | Rumored + announced deals | Live deal pipeline tracking | $$ |
| FactSet Mergerstat | US public deals, premiums data | Control premium analysis | $$ |
| SEC EDGAR (8-K, S-4) | US public deals, free | Detailed deal docs, fairness opinions | Free |
| Pitchbook | Private + sponsor deals | PE-backed transactions | $$ |
| Press releases | Headline metrics only | Initial screening | Free |
⚠️ Warning: Press releases routinely quote headline equity value while omitting assumed debt, preferred equity, and minority interests. Always reconcile back to enterprise value from the actual filing, not the press release, or your multiples will be systematically too low.
Step 2: Build the Transaction Master Table
Set up your master tab with one row per deal. At minimum, capture these columns — and yes, the order matters because downstream formulas reference them by position.
| Column | Field | Source |
|---|---|---|
| A | Announcement Date | 8-K, press release |
| B | Target Name | 8-K |
| C | Acquirer Name | 8-K |
| D | Deal Type | Strategic / Sponsor |
| E | Consideration | Cash / Stock / Mix |
| F | Offer Price/Share | Merger agreement |
| G | Shares Outstanding (diluted) | 10-Q + proxy |
| H | Equity Value | F × G |
| I | Plus: Debt | Latest 10-Q |
| J | Less: Cash | Latest 10-Q |
| K | Plus: Preferred / Minority | Balance sheet |
| L | Enterprise Value | H + I − J + K |
| M | LTM Revenue | Target filings |
| N | LTM EBITDA | Target filings |
| O | LTM Net Income | Target filings |
| P | EV / LTM Revenue | L ÷ M |
| Q | EV / LTM EBITDA | L ÷ N |
| R | P / E | H ÷ O |
Calculating Equity Value and Enterprise Value
The mechanics are simple, but the discipline matters. Use named ranges or structured table references so the formulas read cleanly during PRT (partner review time).
Equity Value =OfferPricePerShare * DilutedSharesOutstanding
Enterprise Value =EquityValue + Debt - Cash + Preferred + Minority
For a structured table named tDeals:
=[@[Offer Price/Share]]*[@[Diluted Shares]]
=[@[Equity Value]]+[@Debt]-[@Cash]+[@Preferred]+[@Minority]
Example: A target with 50M diluted shares, a $42 offer price, $500M of debt, $150M of cash, and $0 preferred has equity value of $2,100M and enterprise value of $2,450M. Note that out-of-the-money options should still be in the diluted share count using the treasury stock method if the offer price exceeds strike.
Step 3: Compute LTM Financials Correctly
"LTM" means last twelve months ending immediately before announcement — not last fiscal year. Junior analysts who use last full-year EBITDA inflate or deflate multiples by a full quarter of drift.
Use this canonical LTM formula in Excel:
LTM_EBITDA = LastFY_EBITDA + Stub_Current_YTD - Stub_PriorYear_YTD
In Excel, if FY2024 EBITDA is in cell B5, 9-month YTD 2025 EBITDA is in C5, and 9-month YTD 2024 EBITDA is in D5:
=B5+C5-D5
💡 Pro Tip: Always pull EBITDA from the company's segment reporting rather than recomputing from the income statement. Pre-existing analyst adjustments for stock-based comp, restructuring, and impairments take hours to replicate from scratch and rarely tie out.
Why You Adjust for Synergies
If you're computing the multiple paid by the acquirer, do not subtract synergies. If you're computing the multiple the target was actually worth standalone, you must back out the present value of synergies the buyer paid for. Most pitch books show both — labeled "EV/EBITDA" and "EV/Synergy-Adjusted EBITDA" — to bracket the analyst's view.
Step 4: Calculate the Multiples
With EV and LTM metrics in place, the multiples themselves are trivial. The art is in error handling. A negative EBITDA produces a meaningless multiple, and a tiny EBITDA produces an absurdly large one — both should be flagged and dropped, not displayed.
Use IFERROR plus a sanity threshold:
=IF(N5<=0,"NM",IF(L5/N5>50,"NM",L5/N5))
This returns "NM" (not meaningful) when EBITDA is zero/negative or the resulting multiple exceeds 50x. Adjust thresholds by industry — SaaS deals can legitimately price above 50x EBITDA, while industrials almost never do.
Handling Stock + Cash Consideration
Mixed-consideration deals require an extra step. The equity value isn't the offer price per share times shares outstanding — it's the value of total consideration delivered on closing, valued at the acquirer's stock price on the announcement date (or a defined VWAP window per the merger agreement).
Equity Value = CashPerShare * Shares + ExchangeRatio * AcquirerPrice * Shares
⚠️ Warning: For collar deals, the merger agreement defines specific reference prices and adjustment mechanics. Read the S-4. Hard-coding a single acquirer share price for a collared exchange ratio is a classic deal-doc error that gets flagged in fairness opinion review.
How Do You Apply the Multiples to Value the Target?
Once you have a clean comp table, calculate the minimum, 25th percentile, median, mean, 75th percentile, and maximum EV/EBITDA paid across the peer set. Then multiply your target's LTM EBITDA by each statistic to get an implied EV range. Convert EV to equity value by subtracting net debt and dividing by diluted shares.
Use these Excel statistics formulas on your multiple column (assume Q5:Q20 contains EV/EBITDA values):
Minimum =MIN(Q5:Q20)
25th %ile =PERCENTILE.INC(Q5:Q20,0.25)
Median =MEDIAN(Q5:Q20)
Mean =AVERAGE(Q5:Q20)
75th %ile =PERCENTILE.INC(Q5:Q20,0.75)
Maximum =MAX(Q5:Q20)
When the column contains "NM" text values, swap to AGGREGATE which ignores errors and text:
=AGGREGATE(17,6,Q5:Q20,1) ' 25th percentile, ignoring errors
=AGGREGATE(17,6,Q5:Q20,3) ' 75th percentile, ignoring errors
The first argument 17 is the QUARTILE.INC function code; 6 ignores error values.
Build the Implied Valuation Output Block
| Metric | Multiple | × Target LTM EBITDA | Implied EV | − Net Debt | Implied Equity | ÷ Shares | Implied Price |
|---|---|---|---|---|---|---|---|
| Low (25th %ile) | 8.5x | $200M | $1,700M | ($300M) | $1,400M | 50M | $28.00 |
| Median | 11.2x | $200M | $2,240M | ($300M) | $1,940M | 50M | $38.80 |
| Mean | 11.8x | $200M | $2,360M | ($300M) | $2,060M | 50M | $41.20 |
| High (75th %ile) | 13.7x | $200M | $2,740M | ($300M) | $2,440M | 50M | $48.80 |
The output is your valuation range: in this example, the target is worth $28–$49 per share based on what comparable companies have actually sold for, with a central tendency around $39–$41.
Why Control Premium Matters (And How to Adjust)
If you're using trading comps to value a control transaction, you have to bolt on a control premium. If you're using precedent transactions, that premium is already embedded — which is exactly why transaction multiples sit above trading multiples.
The standard analysis pulls one-day, one-week, and four-week premium statistics from FactSet Mergerstat or Capital IQ:
Control Premium = (Offer Price - Unaffected Share Price) / Unaffected Share Price
In Excel, if the offer price is in F5 and the 4-week unaffected price is in G5:
=F5/G5-1
Median public-target control premiums historically run 25–35% in stable rate regimes. In 2026, with higher cost of capital, premiums have compressed toward the lower end of that band — adjust your fairness opinion ranges accordingly.
ℹ️ Note: For carve-out and sponsor-to-sponsor secondary sale transactions, the "control premium" concept doesn't apply the same way because there is no unaffected public trading price. Use only strategic acquisitions of public targets for premium statistics.
What Multiples Should You Show in the Output?
The standard precedent transactions output page in a pitch book shows three multiples side by side, because each captures a different aspect of value.
| Multiple | Captures | When to Lead With It |
|---|---|---|
| EV / LTM Revenue | Top-line scale, useful when EBITDA is volatile | High-growth, pre-profit targets (SaaS, biotech) |
| EV / LTM EBITDA | Operating profitability, capital-structure neutral | Mature businesses, most strategic M&A |
| EV / NTM EBITDA | Forward profitability, deal thesis economics | Growth deals, turnaround stories |
| P / E | Equity-level return, sensitive to leverage | Financial institutions, insurance |
| EV / EBIT | Captures D&A intensity | Capital-intensive industries |
💡 Pro Tip: Always show both LTM and NTM (next-twelve-months) multiples when forward consensus estimates are available. Strategic acquirers underwrite on forward EBITDA, so NTM multiples often correlate better with actual deal pricing than LTM.
Common Mistakes That Break a Precedent Transactions Analysis
Even seasoned analysts get tripped up by the same handful of errors. Audit your output for all five before sending it to the MD.
- Including distressed or insolvent target deals — bankruptcy sales price differently and skew the low end of the range
- Mixing geographies or business models — a US specialty distributor is not a comp for a European wholesaler
- Forgetting to adjust for acquired tax assets / NOLs — adjusting the EV down by the NPV of acquired tax shields is required for some deals
- Using fiscal-year financials instead of LTM — produces multiples that are off by a full quarter
- Failing to scrub deals with significant synergies disclosed in fairness opinions — these inflate the upper end of your range artificially
⚠️ Warning: Inflating the high end of your valuation range with cherry-picked deals is how analysts get caught in fairness-opinion litigation. Defensible work product means a documented, repeatable screening process — not "I included it because it made the chart look better."
How Precedent Transactions Fit With Other Valuation Methods
A football field valuation chart is the standard output of any valuation pitch: three to five methodologies stacked vertically, each showing a low–high range. Precedent transactions typically anchor the upper end.
graph LR
A[52-Week Trading Range] --> F[Football Field]
B[Analyst Price Targets] --> F
C[Trading Comps] --> F
D[Precedent Transactions] --> F
E[DCF Valuation] --> F
F --> G[Recommended Offer Range]
The overlap of the three "fundamental" methods — comps, transactions, and DCF — defines the defensible bid range. The board of directors uses that overlap to set the negotiating position and to substantiate fairness in the proxy statement. For M&A deals where the acquirer's EPS impact matters as much as valuation, the merger model in Excel ties the transaction pricing directly to accretion or dilution. Running a sensitivity analysis in Excel on the precedent multiple range versus your target's LTM EBITDA turns the static comp table into a defensible bid range that survives board scrutiny.
Automating Precedent Transactions Analysis With AI
The mechanical work of pulling deal financials, calculating multiples, and refreshing comp tables is exactly the kind of repetitive analyst work that AI tools now compress from days to hours. VeloraAI's Excel add-in can parse a list of deal tickers, generate the LTM normalization formulas, and flag outliers in your comp set — leaving you to focus on the judgment calls (which deals to include, how to adjust for synergies, where to anchor the range).
The qualitative work — defending your peer screen to the MD, explaining outliers in a fairness committee, negotiating the deal — remains firmly human. AI shortens the time to a defensible first draft, not the time to a closed transaction.
Frequently Asked Questions
What is the difference between precedent transactions and comparable company analysis?
Precedent transactions value a target based on multiples paid in actual M&A deals for similar companies, including a control premium. Comparable company analysis values a target based on trading multiples of public peers — no control premium. Transaction multiples typically run 20–40% higher than trading multiples for the same business.
How many precedent transactions should I include in my analysis?
Aim for 8 to 15 transactions. Fewer than 8 makes statistical measures unreliable; more than 15 typically forces you to include deals with weaker comparability. If you have fewer than 8 high-quality comps, expand the lookback window before relaxing industry or size filters — recent + relevant beats older + closer.
Why are EV/EBITDA multiples in precedent transactions higher than in trading comps?
Three reasons: (1) control premium — acquirers pay extra for 100% ownership and strategic direction; (2) synergies — buyers price in expected cost and revenue synergies; (3) competitive bidding — auction processes can drive prices above fundamental fair value. Together these add roughly 20–40% to trading multiples on average.
Where can I find precedent M&A transactions data for free?
SEC EDGAR is the best free source for US public deals — search for 8-K filings under Item 1.01 (Material Definitive Agreement) and the resulting S-4 or proxy statement for full deal terms. Press releases give headline numbers, but only the filed documents have the detail needed for accurate multiples.
How do I handle precedent transactions with negative EBITDA targets?
For unprofitable targets, switch from EV/EBITDA to EV/Revenue or EV/Gross Profit as the primary multiple, and add a separate growth-rate axis to the comp table. Many SaaS and biotech deals fall into this bucket. Flag negative-EBITDA multiples as "NM" in your output and document the alternative metric prominently.
What's the right time window for precedent transactions?
The standard window is 3–5 years. Shorter than 3 years often produces too few deals; longer than 5 years pulls in transactions from a meaningfully different interest rate, valuation, and regulatory environment. For sectors that have repriced sharply (tech in 2022–2023, healthcare in 2024), tighten the window to deals announced after the rerate.
Precedent transactions analysis is one of the three pillars of every credible valuation. Build it carefully, screen ruthlessly, and let the multiples do the talking. The next time an MD asks "what's the right offer?", you'll have a defensible answer grounded in what real acquirers have actually paid for businesses like the one on your desk.