Merger Model Template with Accretion/Dilution Analysis

Tested prompts for m&a merger model excel template compared across 5 leading AI models.

BEST BY JUDGE SCORE Claude Haiku 4.5 9/10

You're building an M&A merger model and you need a working Excel template that handles accretion/dilution analysis without forcing you to wire every formula from scratch. Maybe you're a junior banker with a pitch due Friday, a corp dev analyst sizing a target, or a PE associate stress-testing a bolt-on. Either way, you don't want a generic DCF file relabeled as a merger model. You want purchase price allocation, financing mix, synergy phasing, and pro forma EPS math that actually ties out.

This page gives you prompts that generate a complete merger model scaffold you can paste into Excel: acquirer and target standalone financials, deal assumptions (cash/stock/debt mix, premium, transaction fees), goodwill and intangibles calc, combined P&L with synergy realization, and the accretion/dilution bridge with breakeven synergy solver. The four model outputs below show how Claude, GPT, Gemini, and Grok each structure the template so you can pick the one that matches your firm's convention.

Use the comparison table to decide which output to drop into your workbook, then the editorial below shows how to adapt it for specific deal types.

When to use this

Use an AI-generated merger model template when you need a defensible accretion/dilution analysis fast and your firm doesn't have a standardized template, or the existing one is too heavy for the situation. It works best for public-to-public strategic deals where EPS impact is the headline metric, and for early-stage screening before you invest hours in a full three-statement build.

Pitching a strategic acquirer on a public target and need first-cut accretion/dilution by Friday
Corp dev screening 5-10 bolt-on targets and needs a repeatable template per target
PE associate modeling a platform add-on with mixed cash/rollover/debt financing
Teaching or interview prep where you need to walk through PPA, goodwill, and EPS math cleanly
Sanity-checking a banker's model by rebuilding the accretion/dilution bridge independently

When this format breaks down

Full three-statement LBO models where debt paydown, cash sweeps, and IRR to equity are the output. Use a proper LBO template instead.
Cross-border deals with material FX, tax inversion, or transfer pricing mechanics. The template won't capture jurisdictional nuances.
Deals where the target is pre-revenue or loss-making and EPS accretion is meaningless. Focus on revenue synergies and strategic NPV instead.
Fairness opinions or board materials where the model must tie to audited filings line-by-line. Start from the 10-K, not a generated template.

The prompt we tested

You are an investment banking M&A analyst. Build a complete, professional merger model in Excel-compatible format based on the deal details below.

Deal Details:
Acquirer: PublicCo trades at $50/share with 200M shares outstanding, $1.2B revenue growing 6%, 20% EBITDA margin, 25% tax rate. Target: PrivateCo with $400M revenue growing 8%, 18% EBITDA margin. Deal: $1.5B purchase price, 60% cash (financed with new debt at 6%)/40% stock, $30M run-rate synergies phased in over 2 years, $20M deal fees. Build a 5-year accretion/dilution model.

Instructions:
Format every worksheet as a markdown table with cell references (column letters and row numbers) so the user can replicate it directly in Excel. Use realistic investment banking conventions (blue font notation for hardcoded inputs, black for formulas), include all key formulas in Excel syntax, and keep the overall response comprehensive but organized with clear tab headers.

Deliver the model as a structured set of worksheets, each presented as a labeled table with clearly defined rows, columns, formulas (in Excel syntax), and assumptions. Include the following tabs at minimum:
1. Assumptions & Transaction Summary (purchase price, % cash/stock/debt, synergies, financing rates, tax rate, deal fees)
2. Acquirer Standalone Financials (3-5 year IS projection, shares outstanding, EPS)
3. Target Standalone Financials (3-5 year IS projection, shares outstanding, EPS)
4. Sources & Uses of Funds
5. Purchase Price Allocation & Goodwill calculation
6. Pro Forma Combined Income Statement (with synergies, new interest expense, foregone interest on cash, new share count, amortization of intangibles)
7. Accretion/Dilution Analysis (Pro Forma EPS vs Standalone Acquirer EPS, $ and % accretion/dilution by year)
8. Sensitivity Tables (accretion/dilution vs. % stock consideration and vs. synergy assumptions)

For each formula, show the exact Excel syntax (e.g., =B5*(1+B6)) and reference cells explicitly. After the model, provide a brief commentary on whether the deal is accretive or dilutive and the key drivers.

Model comparison

Model	Quality	Cost	Latency	Tokens
Claude Opus 4.7	9	$0.1657	21.2s	806 / 2048
Claude Haiku 4.5	9	$0.0108	11.6s	555 / 2048
GPT-5.4	Not run yet
Gemini 2.5 Pro	Not run yet
Grok 4.1 Fast Reasoning	9	$0.0011	47.7s	622 / 2048

Outputs

Claude Opus 4.7 9/10

# M&A Merger Model: PublicCo Acquisition of PrivateCo

Below is a complete, professional merger model structured across 8 worksheets. Each tab is presented as a markdown table with explicit cell references and Excel formulas.

---

## Tab 1: Assumptions & Transaction Summary

| Cell | A (Label) | B (Value) | Notes / Formula |
|------|-----------|-----------|-----------------|
| 1 | **TRANSACTION ASSUMPTIONS** | | |
| 2 | Acquirer Share Price ($) | 50.00 | Hardcoded (blue) |
| 3 | Acquirer Shares Outstanding (M) | 200.0 | Hardcoded (blue) |
| 4 | Acquirer Equity Value ($M) | 10,000.0 | `=B2*B3` |
| 5 | | | |
| 6 | Purchase Price ($M) | 1,500.0 | Hardcoded (blue) |
| 7 | % Cash Consideration | 60% | Hardcoded (blue) |
| 8 | % Stock Consideration | 40% | `=1-B7` |
| 9 | Cash Consideration ($M) | 900.0 | `=B6*B7` |
| 10 | Stock Consideration ($M) | 600.0 | `=B6*B8` |
| 11 | | | |
| 12 | **FINANCING** | | |
| 13 | New Debt Raised ($M) | 900.0 | `=B9` |
| 14 | Interest Rate on New Debt | 6.0% | Hardcoded (blue) |
| 15 | New Shares Issued (M) | 12.0 | `=B10/B2` |
| 16 | | | |
| 17 | **SYNERGIES & FEES** | | |
| 18 | Run-Rate Synergies ($M) | 30.0 | Hardcoded (blue) |
| 19 | Year 1 Phase-in % | 50% | Hardcoded (blue) |
| 20 | Year 2+ Phase-in % | 100% | Hardcoded (blue) |
| 21 | Deal Fees ($M, expensed) | 20.0 | Hardcoded (blue) |
| 22 | | | |
| 23 | **TAX & OTHER** | | |
| 24 | Tax Rate | 25% | Hardcoded (blue) |
| 25 | Intangibles % of Purchase Price | 25% | Hardcoded (blue) |
| 26 | Intangibles Amortization Life (yrs) | 10 | Hardcoded (blue) |
| 27 | Target Book Value of Equity ($M) | 300.0 | Hardcoded (blue) estimate |

---

## Tab 2: Acquirer Standalone Financials

| Cell | A | B (Y1) | C (Y2) | D (Y3) | E (Y4) | F (Y5) |
|------|---|--------|--------|--------|--------|--------|
| 1 | **PublicCo Standalone** | 2025E | 2026E | 2027E | 2028E | 2029E |
| 2 | Revenue Growth | 6.0% | 6.0% | 6.0% | 6.0% | 6.0% |
| 3 | Revenue ($M) | 1,272.0 | 1,348.3 | 1,429.2 | 1,515.0 | 1,605.9 |
| 4 | EBITDA Margin | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% |
| 5 | EBITDA ($M) | 254.4 | 269.7 | 285.8 | 303.0 | 321.2 |
| 6 | D&A ($M, ~3% rev) | 38.2 | 40.4 | 42.9 | 45.5 | 48.2 |
| 7 | EBIT ($M) | 216.2 | 229.2 | 243.0 | 257.6 | 273.0 |
| 8 | Interest Expense (existing) | 20.0 | 20.0 | 20.0 | 20.0 | 20.0 |
| 9 | Pretax Income | 196.2 | 209.2 | 223.0 | 237.6 | 253.0 |
| 10 | Taxes (25%) | 49.1 | 52.3 | 55.7 | 59.4 | 63.2 |
| 11 | Net Income | 147.2 | 156.9 | 167.2 | 178.2 | 189.7 |
| 12 | Shares Outstanding (M) | 200.0 | 200.0 | 200.0 | 200.0 | 200.0 |
| 13 | Standalone EPS ($) | 0.74 | 0.78 | 0.84 | 0.89 | 0.95 |

**Key Formulas (Tab 2):**
- B3: `=1200*(1+B2)` (base revenue $1.2B grown 1 year)
- C3: `=B3*(1+C2)` — drag across
- B5: `=B3*B4`
- B6: `=B3*3%`
- B7: `=B5-B6`
- B9: `=B7-B8`
- B10: `=B9*Assumptions!$B$24`
- B11: `=B9-B10`
- B13: `=B11/B12`

---

## Tab 3: Target Standalone Financials

| Cell | A | B (Y1) | C (Y2) | D (Y3) | E (Y4) | F (Y5) |
|------|---|--------|--------|--------|--------|--------|
| 1 | **PrivateCo Standalone** | 2025E | 2026E | 2027E | 2028E | 2029E |
| 2 | Revenue Growth | 8.0% | 8.0% | 8.0% | 8.0% | 8.0% |
| 3 | Revenue ($M) | 432.0 | 466.6 | 503.9 | 544.2 | 587.7 |
| 4 | EBITDA Margin | 18.0% | 18.0% | 18.0% | 18.0% | 18.0% |
| 5 | EBITD

Claude Haiku 4.5 9/10

# M&A Merger Model: PublicCo Acquisition of PrivateCo

## TAB 1: ASSUMPTIONS & TRANSACTION SUMMARY

| **Line Item** | **Column A** | **Column B** | **Column C** |
|---|---|---|---|
| **Section 1: Transaction Parameters** | | | |
| Purchase Price ($ millions) | A2 | **1,500** | =B2 (input) |
| % Cash Consideration | A3 | **60%** | =B3 (input) |
| % Stock Consideration | A4 | **40%** | =B4 (input) |
| Cash Amount ($ millions) | A5 | | =B2*B3 |
| Stock Amount ($ millions) | A6 | | =B2*B4 |
| **Section 2: Acquirer Details** | | | |
| PublicCo Current Stock Price ($/share) | A8 | **50** | =B8 (input) |
| PublicCo Shares Outstanding (millions) | A9 | **200** | =B9 (input) |
| PublicCo Equity Value ($ millions) | A10 | | =B8*B9 |
| New Shares Issued (millions) | A11 | | =B6/B8 |
| **Section 3: Target Details** | | | |
| Target Equity Value ($ millions) | A13 | **1,500** | =B2 (input) |
| **Section 4: Financing Assumptions** | | | |
| % Financed with Debt | A15 | **100%** | =B15 (input, of cash portion) |
| New Debt Issued ($ millions) | A16 | | =B5*B15 |
| New Debt Interest Rate | A17 | **6.0%** | =B17 (input) |
| Cash Available (Post-Deal) ($ millions) | A18 | **0** | =B5-B16 |
| **Section 5: Synergy Assumptions** | | | |
| Run-Rate Annual Synergies ($ millions) | A20 | **30** | =B20 (input) |
| Synergy Realization - Year 1 (%) | A21 | **50%** | =B21 (input) |
| Synergy Realization - Year 2 (%) | A22 | **100%** | =B22 (input) |
| Year 1 Synergies ($ millions) | A23 | | =B20*B21 |
| Year 2 Synergies ($ millions) | A24 | | =B20*B22 |
| **Section 6: Other Assumptions** | | | |
| Transaction Fees ($ millions) | A26 | **20** | =B26 (input) |
| Acquirer Tax Rate (%) | A27 | **25%** | =B27 (input) |
| Target Tax Rate (%) | A28 | **25%** | =B28 (input) |
| Effective Tax Rate (Post-Merger) (%) | A29 | **25%** | =B29 (input) |
| Goodwill Amortization Period (years) | A30 | **Indefinite** | =B30 (input) |
| Intangibles Amortization Period (years) | A31 | **10** | =B31 (input) |

---

## TAB 2: ACQUIRER STANDALONE FINANCIALS

| **Line Item** | **Year 0 (LTM)** | **Year 1** | **Year 2** | **Year 3** | **Year 4** | **Year 5** |
|---|---|---|---|---|---|---|
| **Column** | **A** | **B** | **C** | **D** | **E** | **F** |
| **Income Statement** | | | | | | |
| Revenue ($ millions) | A3 | 1,200 | 1,272 | 1,348 | 1,429 | 1,514 |
| Revenue Growth Rate | A4 | | 6.0% | 6.0% | 6.0% | 6.0% | 6.0% |
| EBITDA Margin | A5 | | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% |
| EBITDA ($ millions) | A6 | 240 | 254.4 | 269.6 | 285.8 | 302.8 |
| **Formulas for Row 3-6:** | | | | | | |
| | | =B3*(1+B4) | =C3*(1+C4) | =D3*(1+D4) | =E3*(1+E4) | =F3*(1+F4) |
| | | =B3*B5 | =C3*C5 | =D3*D5 | =E3*E5 | =F3*F5 |
| Depreciation & Amortization ($ millions) | A7 | 24 | 24 | 24 | 24 | 24 |
| EBIT ($ millions) | A8 | 216 | 230.4 | 245.6 | 261.8 | 278.8 |
| **Formula Row 8:** | | =B6-B7 | =C6-C7 | =D6-D7 | =E6-E7 | =F6-F7 |
| Interest Expense ($ millions) | A9 | 0 | 0 | 0 | 0 | 0 |
| EBT ($ millions) | A10 | 216 | 230.4 | 245.6 | 261.8 | 278.8 |
| **Formula Row 10:** | | =B8-B9 | =C8-C9 | =D8-D9 | =E8-E9 | =F8-F9 |
| Taxes ($ millions) | A11 | 54 | 57.6 | 61.4 | 65.45 | 69.7 |
| **Formula Row 11:** | | =B10*$B$27 | =C10*$B$27 | =D10*$B$27 | =E10*$B$27 | =F10*$B$27 |
| Net Income ($ millions) | A12 | 162 | 172.8 | 184.2 | 196.35 | 209.1 |
| **Formula Row 12:** | | =B10-B11 | =C10-C11 | =D10-D11 | =E10-E11 | =F10-F11 |
| **Per Share Metrics** | | | | | | |
| Shares Outstanding (millions) | A14 | 200 | 200 | 200 | 200 | 200 |
| EPS ($/share) | A15 | 0.81 | 0.864 | 0.921 | 0.982 | 1.046 |
| **Formula Row 15:** | | =B12/B14 | =C12/C14 | =D12/D14 | =E12/E14 | =F12/F14 |

---

## TAB 3: TARGET STANDALONE FINANCIALS

| **Line Item** | **Year 0 (LTM)** | **Year 1** | **Year 2** | **Year 3** | **Year 4** | **Year 5** |
|---|---|---|---|---|---|---|
| **Column** | **A** | **B** | **C** | **D** | **E** | **F** |
| **Income Statement** | | | | | | |
| Revenue ($ millions) | A3 | 400 | 432 | 466.6 | 503.5 | 543.8 |
| Revenue Growth Rate | A4 | | 8.0% | 8.0% | 8.0% | 8.0% | 8.0% |
| EBIT

Grok 4.1 Fast Reasoning 9/10

## Tab 1: Assumptions & Transaction Summary

**Instructions for Excel:** Create a new worksheet named "Assumptions". Hardcoded inputs are in **blue font** (format cells with blue font color). Formulas in black. Rows 1-50 used below.

|     | A                          | B                  | C                  |
|-----|----------------------------|--------------------|--------------------|
| 1   | **ACQUIRER ASSUMPTIONS**   |                    |                    |
| 2   | Revenue (Y0, $M)           | <span style="color:blue">1,200</span> |                    |
| 3   | Revenue Growth             | <span style="color:blue">6.0%</span>  |                    |
| 4   | EBITDA Margin              | <span style="color:blue">20.0%</span> |                    |
| 5   | D&A % of Revenue           | <span style="color:blue">4.0%</span>  |                    |
| 6   | Shares Outstanding (M)     | <span style="color:blue">200</span>    |                    |
| 7   | Share Price                | <span style="color:blue">$50.00</span> |                    |
| 8   | **TARGET ASSUMPTIONS**     |                    |                    |
| 9   | Revenue (Y0, $M)           | <span style="color:blue">400</span>    |                    |
| 10  | Revenue Growth             | <span style="color:blue">8.0%</span>  |                    |
| 11  | EBITDA Margin              | <span style="color:blue">18.0%</span> |                    |
| 12  | D&A % of Revenue           | <span style="color:blue">4.0%</span>  |                    |
| 13  | **TRANSACTION ASSUMPTIONS**|                    |                    |
| 14  | Purchase Price ($M)        | <span style="color:blue">1,500</span>  |                    |
| 15  | % Cash Consideration       | <span style="color:blue">60.0%</span> |                    |
| 16  | % Stock Consideration      | <span style="color:blue">40.0%</span> |                    |
| 17  | New Debt Interest Rate     | <span style="color:blue">6.0%</span>  |                    |
| 18  | Synergies Run-Rate ($M)    | <span style="color:blue">30</span>     |                    |
| 19  | Synergy Ramp Y1            | <span style="color:blue">50.0%</span> |                    |
| 20  | Synergy Ramp Y2+           | <span style="color:blue">100.0%</span>|                    |
| 21  | Deal Fees ($M, expensed Y1)| <span style="color:blue">20</span>     |                    |
| 22  | Tax Rate                   | <span style="color:blue">25.0%</span> |                    |
| 23  | **PPA ASSUMPTIONS**        |                    |                    |
| 24  | Target Net Tangible Assets ($M) | <span style="color:blue">200</span> | |
| 25  | Target Intangible Assets ($M)   | <span style="color:blue">400</span> | |
| 26  | Intangible Amort. Period (Yrs)  | <span style="color:blue">10</span>  | |
| 27  | Goodwill ($M)              | =B14-B24-B25      | **900**            |
| 28  | Annual Intangible Amort. ($M)  | =B25/B26          | **40**             |
| 29  | **TRANSACTION SUMMARY**    | **Value ($M)**    | **Shares (M)**     |
| 30  | Total Purchase Price       | =B14              |                    |
| 31  | Cash Portion               | =B30*B15          |                    |
| 32  | Stock Portion              | =B30*B16          | =B32/Assumptions!B7 |
| 33  | New Debt Raised            | =B31              |                    |
| 34  | New Shares Issued          |                    | =B32/B7            |
| 35  | Total Pro Forma Shares     |                    | =B6+B34            |
| 36  | New Annual Interest ($M)   | =B33*B17          |                    |

*(Note: B36=54. In Excel, format B2:B36 blues for inputs; B27:B36 formulas auto-populate.)*

## Tab 2: Acquirer Standalone Financials

**Instructions:** New worksheet "Acquirer_Standalone". Link to Assumptions (e.g., =Assumptions!B2). Headers in row 1: A1=" ", B1="Y0", C1="Y1", D1="Y2", E1="Y3", F1="Y4", G1="Y5". Projections assume constant margins/growth.

|     | A             | B (Y0)       | C (Y1)              | D (Y2)              | E (Y3)              | F (Y4)              | G (Y5)              |
|-----|---------------|--------------|---------------------|---------------------|---------------------|---------------------|---------------------|
| 2   | Revenue       | =Assumptions!B2 | =B2*(1+Assumptions!B3) | =C2*(1+Assumptions!B3) | =D2*(1+Assumptions!B3) | =E2*(1+Assumptions!B3) | =F2*(1+Assumptions!B3) |
| 3   | EBITDA        | =B2*Assumptions!B4 | =C2*Assumptions!B4 | =D2*Assumptions!B4 | =E2*Assumptions!B4 | =F2*Assumptions!B4 | =G2*Assumptions!B4 |
| 4   | D&A           | =B2*Assumptions!B5 | =C2*Assumptions!B5 | =D2*Assumptions!B5 | =E2*Assumptions!B5 | =F2*Assumptions!B5 | =G2*Assumptions!B5 |
| 5   | EBIT          | =B3-B4       | =C3-C4              | =D3-D4              | =E3-E4              | =F3-F4              | =G3-G4              |
| 6   | Taxes         | =B5*Assumptions!B22 | =C5*Assumptions!B22 | =D5*Assumptions!B22 | =E5*Assumptions!B22 | =F5*Assumptions!B22 | =G5*Assumptions!B22 |
| 7   | Net Income    | =B5-B6       | =C5-C6              | =D5-D6              | =E5-E6              | =F5-E6              | =G5-G6              |
| 8   | Shares (M)    | =Assumptions!B6 | =Assumptions!B6     | =Assumptions!B6     | =Assumptions!B6     | =Assumptions!B6     | =Assumptions!B6     |
| 9   | EPS           | =B7/B8       | =C7/C8              | =D7/D8              | =E7/E8              | =F7/F8              | =G7/G8              |

*(Sample values: Y0 Rev=1200, NI=144, EPS=0.72; Y1 Rev=1272, NI=152.64, EPS=0.76. No interest assumed standalone. Blues: none new, all links/formulas.)*

## Tab 3: Target Standalone Financials

**Instructions:** New worksheet "Target_Standalone". Similar structure.

|     | A             | B (Y0)       | C (Y1)              | D (Y2)              | E (Y3)              | F (Y4)              | G (Y5)              |
|-----|---------------|--------------|---------------------|---------------------|---------------------|---------------------|---------------------|
| 2   | Revenue       | =Assumptions!B9 | =B2*(1+Assumptions!B10) | =C2*(1+Assumptions!B10) | =D2*(1+Assumptions!B10) | =E2*(1+Assumptions!B10) | =F2*(1+Assumptions!B10) |
| 3   | EBITDA        | =B2*Assumptions!B11 | =C2*Assumptions!B11 | =D2*Assumptions!B11 | =E2*Assumptions!B11 | =F2*Assumptions!B11 | =G2*Assumptions!B11 |
| 4   | D&A           | =B2*Assumptions!B12 | =C2*Assumptions!B12 | =D2*Assumptions!B12 | =E2*Assumptions!B12 | =F2*Assumptions!B12 | =G2*Assumptions!B12 |
| 5   | EBIT          | =B3-B4       | =C3-C4              | =D3-D4              | =E3-E4

What makes these work

01

Lock the financing mix first
Before running accretion/dilution, nail down the cash/stock/debt split in dollar terms. The template output only makes sense if sources equal uses (purchase equity + refinanced debt + fees). Build a sources and uses table even for simple deals.
02

Phase synergies realistically
Default templates often show 100% synergies Year 1, which overstates accretion. Use 25/60/100% phasing for cost synergies and delay revenue synergies by 12-18 months. Always include one-time integration costs equal to 1-1.5x run-rate synergies.
03

Separate PPA from goodwill
A good template identifies intangibles (customer relationships, technology, tradenames) before plugging goodwill. This matters because intangibles amortize and hit EPS, while goodwill doesn't. Ask the AI to break out intangibles at 15-25% of purchase premium.
04

Build the breakeven synergy solver
The most useful output isn't the EPS number, it's the synergy level required to make the deal neutral. This lets you pressure-test whether announced synergies are credible. Ask for a goal-seek or formula-driven breakeven line.

More example scenarios

#01 · Public tech acquirer buying a SaaS target, 70/30 stock/cash

Input

Acquirer: $12B market cap, 200M shares at $60, $2.50 EPS, 25x P/E. Target: $3B offer at 30% premium, 50M shares, $0.80 EPS. Financing: 70% stock, 30% cash from balance sheet. Run-rate cost synergies $80M phased 25/60/100% over 3 years. Tax rate 21%. Build accretion/dilution for Years 1-3.

Expected output

Template outputs: 50M new shares issued at $60 (35M from stock portion / $2.1B), cash outlay $900M, goodwill calc net of identified intangibles ($400M), pro forma share count 235M. Year 1 EPS: $2.48 (0.8% dilutive), Year 2: $2.71 (accretive 8.4%), Year 3: $2.89 (accretive 15.6%). Breakeven synergy Year 1: $95M.

#02 · Industrial bolt-on, all-cash with new debt

Input

Acquirer: $800M revenue industrial manufacturer, 50M shares, $3.00 EPS. Target: $250M revenue, $35M EBITDA, purchase price $280M (8x EBITDA). Financing: 100% new term loan at 7% interest. $10M cost synergies Year 1 full run-rate. 25% tax rate.

Expected output

Template shows $19.6M after-tax interest expense on new debt, $7.5M after-tax synergies, $28M target net income contribution. Pro forma EPS: $3.32 (10.6% accretive Year 1). Sensitivity table on purchase multiple (7-10x) and synergy realization (50-100%) included.

#03 · Healthcare merger of equals, all-stock

Input

Two public healthcare companies combining. Acquirer $8B equity, Target $6B equity, exchange ratio 0.65x. Combined revenue $5.2B, $800M cost synergies over 4 years, $150M one-time integration costs Year 1. Model pro forma ownership and EPS.

Expected output

Template calculates pro forma ownership 57/43, share count implications, purchase accounting on 43% stake (acquirer is accounting acquirer). Year 1: 2.1% dilutive due to integration costs, Year 2: 4.8% accretive, Year 3: 12% accretive, Year 4: 18% accretive at full synergy run-rate.

#04 · PE platform add-on with rollover equity

Input

PE-owned platform acquires competitor for $150M. Financing: $60M new debt at 9%, $75M sponsor equity, $15M management rollover. Target EBITDA $18M, $4M synergies. Model pro forma capital structure and equity returns assumption, 5-year hold.

Expected output

Template outputs pro forma leverage 4.2x EBITDA, rollover dilution to sponsor (9.1%), pro forma EBITDA bridge showing standalone + target + synergies. Accretion framed as cash-on-cash to sponsor equity rather than EPS, with 5-year exit sensitivity at 8-10x EBITDA.

Common mistakes to avoid

Forgetting foregone interest on cash
If the acquirer uses balance sheet cash, you lose the interest income that cash was earning. Templates often skip this and overstate accretion by 10-30 bps. Always model after-tax foregone interest on the cash portion.
Using spot share price for stock deals
Stock-for-stock deals lock in an exchange ratio, not a price. Using the announcement-day price for Year 2 and Year 3 share count is wrong. The shares issued are fixed at close, so hold share count constant post-deal.
Ignoring deal fees and financing costs
Advisory fees (1-2% of deal value), financing fees (2-3% of new debt), and legal costs can total $50-100M on a mid-cap deal. Skipping them understates the cash outlay and goodwill. Capitalize financing fees and expense advisory fees per GAAP.
Mismatched fiscal years
If acquirer is calendar year and target is June year-end, you cannot just add net incomes. Stub periods and calendarization matter. Force both companies onto the same fiscal calendar before combining.
Treating accretion as value creation
EPS accretion is not the same as NPV positive. A deal can be accretive just from using cheap debt while destroying shareholder value. Always pair the accretion/dilution output with a value-per-share or NPV analysis.

Related queries

Frequently asked questions

What should a complete M&A merger model in Excel include?

At minimum: acquirer and target standalone financials, deal assumptions tab (price, premium, financing mix), sources and uses, purchase price allocation with goodwill and intangibles, combined pro forma P&L with synergies and integration costs, accretion/dilution output with breakeven synergy, and sensitivity tables on purchase price and synergy realization.

How do you calculate accretion/dilution in a merger model?

Pro forma EPS = (Acquirer NI + Target NI + After-tax synergies - After-tax new interest expense - After-tax intangible amortization - After-tax foregone interest) divided by pro forma share count. Compare this to acquirer standalone EPS. Positive difference is accretive, negative is dilutive.

Is a free Excel template good enough for a real deal?

For first-cut screening and pitch work, yes. For signed LOIs, fairness opinions, or board approval, no. Generated templates give you structure and math, but real deals need auditable ties to filings, jurisdictional tax detail, and integration with the acquirer's planning model.

What's a realistic cost synergy assumption for an M&A model?

For strategic deals, 3-8% of target revenue or 5-15% of combined SG&A is the typical range. Industrial consolidations skew higher (10-15%), while tech deals with minimal overlap skew lower (2-5%). Always phase over 2-3 years and include integration costs of 1-1.5x run-rate.

Should I model the deal on a calendar or fiscal year basis?

Use the acquirer's fiscal year since that's what investors track EPS against. If the target has a different year-end, calendarize their financials by pulling trailing twelve months data or stubbing quarters. Never just add net incomes from mismatched periods.

How do I handle stock-based compensation in the pro forma?

Target SBC continues as an expense on the combined P&L. Unvested target options and RSUs get rolled into acquirer equivalents at the exchange ratio, increasing diluted share count. Include the incremental dilution from rolled-over awards in your pro forma share count, not just new shares issued.