Merger Model in Excel: Accretion/Dilution Analysis Step by Step

Global M&A deal volume exceeded $3.5 trillion in 2025, and every single transaction required one deliverable before the board would sign off: a merger model in Excel showing whether the deal is accretive or dilutive to the acquirer's earnings per share. If you work in investment banking, corporate development, or private equity, the merger model is not optional — it is the analysis that determines whether a deal gets done.

This guide walks you through building a complete merger model in Excel from scratch, covering standalone projections, purchase consideration, transaction adjustments, purchase price allocation, the pro forma income statement, and the final accretion/dilution verdict. Every formula is included so you can follow along cell by cell.

What Is a Merger Model?

A merger model is a financial model that combines the income statements of two companies — an acquirer (buyer) and a target (seller) — to calculate the pro forma earnings per share (EPS) of the combined entity after an acquisition. The primary output answers one question: is this deal accretive (increases EPS) or dilutive (decreases EPS) to the acquirer's shareholders?

Unlike a DCF model, which values a company based on intrinsic cash flows, or an LBO model, which evaluates returns to a financial sponsor, the merger model evaluates the earnings impact of combining two businesses from the acquirer's perspective.

When Do You Need a Merger Model?

• Investment banking: Advising acquirers and targets on deal feasibility and pricing
• Corporate development: Evaluating bolt-on acquisitions and strategic combinations
• Board presentations: Demonstrating to directors that a proposed deal creates shareholder value
• Fairness opinions: Supporting independent assessments of transaction pricing

How Is a Merger Model Different From a DCF or LBO?

The merger model sits alongside the DCF and LBO as one of the three core models in investment banking. Here is how they compare:

Feature	Merger Model	DCF Model	LBO Model
Primary output	Pro forma EPS (accretion/dilution)	Enterprise / equity value	IRR and MOIC to sponsor
Perspective	Acquirer's shareholders	Intrinsic value (any investor)	Financial sponsor (PE firm)
Key metric	% EPS accretion or dilution	Implied share price	Equity IRR
Discount rate	Not applicable	WACC	Implied by entry/exit prices
Debt focus	Acquisition financing only	Capital structure for WACC	Leveraged capital structure
Time horizon	1–3 years post-close	5–10 year projection	3–7 year hold period
Uses synergies?	Yes — critical input	Rarely	Sometimes (operating improvements)

💡 Pro Tip: In practice, bankers build all three models for the same transaction. The DCF establishes a standalone valuation range, comparable company analysis anchors the market-based multiple, the merger model tests deal feasibility, and the LBO sets a floor price (what a financial sponsor would pay). Together, they form the "valuation football field."

For PE-backed acquirers, the returns picture extends one layer further — a private equity waterfall model determines how the GP and LP ultimately divide the fund's exit proceeds across preferred return, catch-up, and carried interest tiers.

The Merger Model Architecture

Before touching Excel, understand the logical flow of a merger model. Every step feeds into the next:

graph TD
    A[Acquirer Standalone Financials] --> E[Pro Forma Income Statement]
    B[Target Standalone Financials] --> E
    C[Purchase Consideration<br>Cash / Stock / Mix] --> D[Transaction Adjustments]
    D --> E
    E --> F[Pro Forma EPS]
    F --> G{Accretive or Dilutive?}
    G -->|EPS Increases| H[Deal is Accretive ✓]
    G -->|EPS Decreases| I[Deal is Dilutive ✗]

Now let's build each component.

Step 1: Set Up Standalone Financial Data

Start by gathering the key income statement metrics for both companies (typically extracted from each entity's three-statement financial model). You need these inputs in a dedicated Assumptions tab:

Acquirer Inputs

Input	Example Value	Cell
Share price	$50.00	B3
Diluted shares outstanding	200M	B4
Net income (LTM or projected)	$1,000M	B5
Standalone EPS	$5.00	B6
Tax rate	25%	B7
Pre-tax cost of debt	5.0%	B8
Cash on balance sheet	$500M	B9
Interest income yield on cash	3.0%	B10

Target Inputs

Input	Example Value	Cell
Share price (current)	$30.00	D3
Diluted shares outstanding	50M	D4
Net income (LTM or projected)	$150M	D5
Standalone EPS	$3.00	D6

Calculate the acquirer's standalone EPS with a simple formula:

=B5/B4

This returns $5.00 — the benchmark against which we will measure accretion or dilution.

Step 2: Structure the Purchase Consideration

The acquirer can pay for the target using cash, stock, or a combination. This decision is the single biggest driver of accretion/dilution because it determines how many new shares are issued and how much debt or cash is consumed.

Offer Price and Premium

Set up the offer price as a premium to the target's current share price:

Offer Price per Share = Target Share Price × (1 + Premium %)

In Excel:

=D3*(1+B13)

If the target trades at $30.00 and you offer a 33.3% premium, the offer price is $40.00 per share.

The total equity purchase price (equity value) is:

=B14*D4

At $40.00 per share × 50M shares = $2,000M total equity purchase price.

Cash vs. Stock Mix

Create input cells for the % cash and % stock in the consideration:

Cash Consideration  = Total Purchase Price × Cash %
Stock Consideration = Total Purchase Price × Stock %

⚠️ Warning: The cash/stock mix fundamentally changes the accretion/dilution outcome. All-cash deals avoid diluting shares but create interest expense from new debt. All-stock deals avoid debt costs but dilute existing shareholders. Most real deals use a mix — model both extremes in your sensitivity tables.

New Shares Issued

If any portion is paid in stock, calculate the new shares issued to the target's shareholders:

New Shares Issued = Stock Consideration / Acquirer Share Price

In Excel:

=B17/B3

For a 50/50 deal ($1,000M stock / $50.00 share price) = 20M new shares issued.

Step 3: Calculate Transaction Adjustments

A merger model is not simply adding two income statements together. You must account for several adjustments that change the combined earnings:

3a. Foregone Interest on Cash

If the acquirer uses cash from its balance sheet, it loses the interest income it was earning on that cash:

Foregone Interest (pre-tax) = Cash Used from B/S × Interest Income Yield
Foregone Interest (after-tax) = Foregone Interest × (1 - Tax Rate)

In Excel:

=MIN(B16, B9) * B10 * (1 - B7)

If you use $500M cash earning 3.0% at a 25% tax rate, the after-tax foregone interest is $11.25M.

3b. New Debt Interest Expense

If the cash consideration exceeds available cash, the acquirer must raise new debt:

New Debt Required = Cash Consideration - Cash on Balance Sheet
New Interest Expense (after-tax) = New Debt × Cost of Debt × (1 - Tax Rate)

In Excel:

=MAX(B16-B9, 0) * B8 * (1-B7)

If cash consideration is $1,000M and you have $500M on hand, you borrow $500M at 5.0%: after-tax interest expense = $18.75M.

3c. Synergies

Synergies are the expected cost savings or revenue enhancements from combining the two businesses. Model them as a pre-tax number and apply the acquirer's tax rate:

After-Tax Synergies = Pre-Tax Synergies × (1 - Tax Rate)

=B22*(1-B7)

ℹ️ Note: Be conservative with synergies. Most merger models phase synergies in over 1–3 years. For an initial accretion/dilution screen, analysts typically use Year 1 run-rate synergies as the base case and 0% synergies as the downside case.

3d. Transaction and Financing Fees

Advisory fees, legal costs, and debt issuance costs are typically one-time charges that reduce Year 1 earnings. For simplicity, many models exclude these from the recurring accretion/dilution analysis but include them in the full P&L build.

Step 4: Purchase Price Allocation and Goodwill

When the acquirer pays more than the target's book value of net assets, the excess is allocated to identifiable intangible assets and goodwill:

Goodwill = Purchase Price - Fair Value of Net Tangible Assets - Identified Intangibles

Why Goodwill Matters for the Merger Model

Under current accounting standards (ASC 805), goodwill is not amortized — it sits on the balance sheet and is tested annually for impairment. This means goodwill from the acquisition does not reduce earnings in the accretion/dilution analysis.

However, identifiable intangible assets (customer relationships, patents, trade names) are amortized over their useful lives. This amortization creates a non-cash charge that reduces pre-tax income:

Intangible Amortization (after-tax) = Identified Intangibles / Useful Life × (1 - Tax Rate)

=B26/B27*(1-B7)

Example: If the acquirer identifies $200M in intangible assets with a 10-year useful life at a 25% tax rate, annual after-tax amortization = $200M / 10 × 0.75 = $15M drag on pro forma earnings.

Step 5: Build the Pro Forma Income Statement

Now bring everything together. The pro forma combined net income is:

Pro Forma Net Income = Acquirer Net Income
                     + Target Net Income
                     + After-Tax Synergies
                     - Foregone Interest on Cash (after-tax)
                     - New Debt Interest Expense (after-tax)
                     - Intangible Amortization (after-tax)

In Excel, create a clear build-up:

=B5 + D5 + B23 - B20 - B21 - B28

Using our example numbers:

Component	Amount
Acquirer Net Income	$1,000.00M
+ Target Net Income	$150.00M
+ After-Tax Synergies ($100M pre-tax)	$75.00M
- Foregone Interest on Cash	($11.25M)
- New Debt Interest Expense	($18.75M)
- Intangible Amortization	($15.00M)
= Pro Forma Net Income	$1,180.00M

How Do You Calculate Accretion or Dilution?

This is the most important calculation in the model. Divide pro forma net income by the pro forma diluted share count:

Pro Forma Shares = Acquirer Shares + New Shares Issued
Pro Forma EPS    = Pro Forma Net Income / Pro Forma Shares

In Excel:

=B4+B18
=B30/B31

Using our example: Pro Forma Shares = 200M + 20M = 220M

Pro Forma EPS = $1,180M / 220M = $5.36

The Accretion/Dilution Verdict

Accretion / (Dilution) % = (Pro Forma EPS / Standalone EPS) - 1

=B32/B6-1

$5.36 / $5.00 - 1 = +7.3% accretive

The deal increases the acquirer's EPS by 7.3%. This is a positive signal, though not the sole reason to pursue a deal.

⚠️ Warning: A deal being accretive does NOT automatically make it a good deal. A company could acquire a low-growth, cheap asset that is immediately accretive to EPS but destroys long-term value. Accretion/dilution is one data point alongside strategic fit, synergy risk, and intrinsic valuation.

The Quick Accretion/Dilution Shortcut

Experienced bankers use a mental shortcut to quickly estimate whether a deal will be accretive or dilutive before building the full model:

Compare the acquirer's P/E ratio to the target's P/E ratio (adjusted for the premium).

• If Acquirer P/E > Target P/E (at the offer price): likely accretive in an all-stock deal
• If Acquirer P/E < Target P/E (at the offer price): likely dilutive in an all-stock deal

The logic: the acquirer is "buying" earnings at a lower multiple than its own, so combining them increases the blended EPS.

In our example:

• Acquirer P/E = $50.00 / $5.00 = 10.0x
• Target P/E at offer price = $40.00 / $3.00 = 13.3x

The target's P/E at the offer price is higher than the acquirer's, which suggests an all-stock deal would be dilutive. Our model showed accretion because we used a 50/50 cash-stock mix and included synergies — both of which offset the P/E disadvantage.

💡 Pro Tip: This shortcut breaks down when you include cash consideration, synergies, and financing effects. Always build the full model — but use the shortcut to sanity-check your output. If your model shows accretion when the P/E math says dilution, verify your synergy and financing assumptions.

How Do You Build a Sensitivity Table for Accretion/Dilution?

No merger model is complete without a two-way sensitivity table showing how accretion/dilution changes across different assumptions. For a step-by-step walkthrough of Excel's data table setup, conditional formatting heat maps, and tornado charts, see our sensitivity analysis guide for financial models. The two most common axes are:

Sensitivity 1: Offer Premium vs. Cash/Stock Mix

Set up a two-way data table with:

• Row input: Offer premium (15%, 20%, 25%, 30%, 35%, 40%)
• Column input: % cash in consideration (0%, 25%, 50%, 75%, 100%)
• Output cell: Accretion/dilution %

In Excel, use a Data Table (What-If Analysis → Data Table):

1. Place the accretion/dilution % formula in the top-left corner of the table
2. Enter premium values down the first column
3. Enter cash % values across the first row
4. Select the entire table range
5. Go to Data → What-If Analysis → Data Table
6. Set the Row input cell to your cash % assumption cell
7. Set the Column input cell to your premium % assumption cell

Row input cell:    $B$15  (Cash %)
Column input cell: $B$13  (Premium %)

Sensitivity 2: Synergies vs. Offer Premium

This table answers the critical question: how much in synergies do we need to make this deal accretive at a given premium?

	0% Synergies	$50M	$100M	$150M	$200M
25% Premium	-2.1%	+0.8%	+3.6%	+6.5%	+9.3%
30% Premium	-3.5%	-0.6%	+2.3%	+5.1%	+8.0%
33% Premium	-4.5%	-1.6%	+1.3%	+4.1%	+7.0%
35% Premium	-5.2%	-2.3%	+0.6%	+3.4%	+6.3%
40% Premium	-6.5%	-3.6%	-0.8%	+2.1%	+4.9%

This type of table is what boards and deal committees actually review when deciding whether to proceed.

Common Merger Model Mistakes to Avoid

Even experienced analysts make errors in merger models. Here are the most frequent issues:

1. Forgetting to tax-affect adjustments. Synergies, interest expense, and foregone interest must all be calculated on an after-tax basis. A common error is adding $100M in pre-tax synergies directly to pro forma net income, overstating the benefit by 25%.

2. Double-counting cash. If cash on the balance sheet is used for the acquisition, you cannot also assume the acquirer continues to earn interest on that same cash. The foregone interest adjustment handles this.

3. Ignoring intangible amortization. While goodwill is not amortized, identified intangible assets from purchase price allocation are. Skipping this overstates pro forma EPS.

4. Using the wrong share count. The pro forma share count must include new shares issued to the target's shareholders. For stock-for-stock deals, this is the single biggest driver of dilution. For privately-held acquisitions where the target has multiple preferred series, option pools, and convertible notes, a cap table model in Excel is the cleaner way to calculate the target's fully diluted share count before modeling the deal consideration.

5. Mixing up "accretive to EPS" with "good deal." Many dilutive deals are excellent strategic acquisitions, and many accretive deals destroy value. Always present accretion/dilution alongside the full valuation analysis.

Before sharing a merger model with a deal team or client, a structured review pays dividends — our financial model audit checklist for Excel covers every phase from structural verification and formula consistency to stress-testing, catching the errors above before they become problems.

Frequently Asked Questions

What does it mean when a merger is accretive?

An accretive merger increases the acquirer's earnings per share after the transaction closes. This happens when the earnings contributed by the target (plus synergies, minus financing costs) more than offset any dilution from issuing new shares. Accretion is generally viewed positively by investors, but it is not the sole indicator of a good deal.

How do synergies affect accretion/dilution?

Synergies directly increase pro forma net income, making accretion more likely. Cost synergies (headcount reduction, facility consolidation) are more predictable and weighted more heavily by bankers. Revenue synergies (cross-selling, pricing power) are harder to quantify and often discounted or excluded from base case models.

Can a dilutive deal still be a good acquisition?

Absolutely. Many of the most successful acquisitions in history were initially dilutive to EPS. If the target provides strategic value — new markets, critical technology, defensive positioning — short-term EPS dilution may be a worthwhile trade-off. The key is whether the deal creates long-term shareholder value, not whether Year 1 EPS increases.

What is the typical premium paid in M&A transactions?

Acquisition premiums typically range from 20% to 40% over the target's unaffected share price. The median premium for public company acquisitions in the U.S. has historically been around 30–35%. Higher premiums make accretion harder to achieve because the acquirer is paying more per dollar of target earnings.

How does the cash vs. stock mix affect the result?

All-cash deals avoid share dilution but create interest expense from new debt or lost interest income on deployed cash. All-stock deals avoid financing costs but increase the share count. The optimal mix depends on the acquirer's cost of debt, P/E ratio relative to the target, and balance sheet capacity. Most real transactions use a combination.

Putting It All Together

The merger model is one of the three foundational models every investment banking analyst must master — alongside the DCF and the LBO. Unlike those models, the merger model directly answers the question boards and investors ask first: what happens to our earnings?

The key to building a reliable merger model is getting the transaction adjustments right — foregone interest, new debt costs, synergies, and intangible amortization. Miss any one of these, and your accretion/dilution output will be wrong.

If you find yourself building merger models frequently, tools like VeloraAI can accelerate the process by generating the formulas for transaction adjustments and sensitivity tables from natural language descriptions, letting you focus on the assumptions rather than the cell references.

The best merger models are not just technically correct — they tell a clear story about whether a deal makes financial sense. Build yours with the rigor this guide provides, and your output will hold up under any level of scrutiny.