Introduction
LBO and merger models are the most complex and most high-stakes models in investment banking. An LBO model determines whether a private equity fund can profitably acquire a company. A merger model determines whether a deal creates or destroys value for the acquirer's shareholders. Both models sit on top of the three-statement model and layer transaction-specific mechanics (leveraged capital structures, purchase price allocations, pro forma combinations) that add significant complexity.
This article covers the practical best practices for building both model types in Excel, focusing on structure, common pitfalls, and the quality checks that ensure reliable output.
LBO Model: Structure and Best Practices
Recommended Tab Structure
A well-organized LBO model follows a modular design:
1. Assumptions tab: Entry multiple, leverage assumptions (for each debt tranche), EBITDA growth assumptions, exit multiple, holding period, transaction fees, and any other driver 2. [Sources and uses](/guides/valuation-investment-banking/sources-and-uses-of-funds-lbo): Maps every source of financing against every use of funds, must balance 3. [Pro forma balance sheet](/guides/valuation-investment-banking/pro-forma-balance-sheet-purchase-price-allocation): Day 1 balance sheet with PPA adjustments (goodwill, intangibles, deferred taxes) 4. Projected financial statements: Income statement, balance sheet, cash flow statement for the holding period 5. [Debt schedule](/guides/valuation-investment-banking/lbo-debt-schedule-amortization-cash-sweeps): Tracks each tranche with interest, amortization, cash sweeps, and optional prepayments 6. [Returns analysis](/guides/valuation-investment-banking/lbo-returns-irr-moic-cash-on-cash): Exit EV, exit equity, IRR, MOIC, and returns attribution
Key Modeling Points
The debt schedule is the most complex component. It must handle multiple tranches with different interest rates (fixed vs. floating), amortization schedules, cash sweeps with leverage-based step-downs, call protection on high-yield bonds, and PIK interest accrual for subordinated instruments. Each tranche operates independently but interacts with the others through the prepayment waterfall.
The circular reference is intentional. Interest expense depends on the debt balance, which depends on free cash flow, which depends on interest expense. Enable iterative calculations in Excel or build a circuit breaker toggle. Do not avoid the circularity by using only beginning-period balances for interest; using average balances is more accurate and is the industry standard.
- Circuit Breaker (Financial Model)
A toggle cell (typically on the assumptions or cover tab) that the analyst can switch between 1 and 0 to enable or disable circular references in the model. When set to 0, the circular formulas (interest expense, cash sweep calculations, revolver draws) are overridden with zeros, breaking the circularity and allowing the model to calculate without iterative computation. When set to 1, the full circular formulas activate and the model uses Excel's iterative calculation to converge. The circuit breaker is essential for debugging: if the model produces #VALUE! errors or fails to converge, switching the breaker to 0 allows the analyst to identify which formula is causing the problem. Best practice includes a clear label ("CIRC BREAKER: 1=ON, 0=OFF") and conditional formatting that turns the cell red when the breaker is off (reminding the analyst to re-enable it before presenting results).
Returns should be calculated using XIRR, not IRR, because the cash flows may not be evenly spaced (the investment date, dividend recapitalizations, and exit date rarely fall on exact annual intervals).
Merger Model: Structure and Best Practices
Recommended Tab Structure
1. Assumptions tab: Offer price, form of consideration (cash/stock/mix), synergy estimates, transaction fees, financing terms 2. Transaction adjustments: PPA (goodwill, intangible amortization, deferred taxes), sources and uses, new financing, and fee treatment 3. Pro forma income statement: Combines both companies' income statements with adjustments for synergies, new interest expense, and PPA amortization 4. [Accretion/dilution analysis](/guides/valuation-investment-banking/accretion-dilution-analysis-measuring-eps-impact): Pro forma EPS vs. acquirer standalone EPS, with sensitivity to synergies and purchase price 5. [Pro forma balance sheet](/guides/valuation-investment-banking/pro-forma-balance-sheet-purchase-price-allocation): Combined balance sheet with transaction adjustments 6. [Pro forma credit analysis](/guides/valuation-investment-banking/pro-forma-credit-analysis-rating-agency-impact): Leverage ratios, interest coverage, and comparison to investment-grade thresholds
Key Modeling Points
Goodwill is the balance sheet plug. After combining both companies' balance sheets and applying all adjustments (new debt, new equity, fee treatment, PPA intangibles, deferred tax effects), goodwill is calculated as the amount needed to balance the pro forma balance sheet. If goodwill does not balance the sheet, there is an error in the transaction adjustments.
PPA amortization flows to the accretion/dilution. The intangible assets created in the PPA are amortized over their useful lives, creating a non-cash expense that reduces pro forma net income and makes the deal appear more dilutive. Present accretion/dilution both with and without PPA amortization to show the "cash" impact.
The form of consideration determines the share count. In an all-cash deal, the pro forma share count equals the acquirer's current diluted shares. In an all-stock deal, the pro forma count includes the new shares issued to the target's shareholders (based on the exchange ratio). In a mixed deal, only the stock portion creates new shares.
- Merger Model (Acquisition Model)
A financial model that evaluates the financial impact of a proposed M&A transaction by combining the acquirer's and target's financial statements, applying transaction adjustments (purchase price allocation, new financing, synergies), and assessing the impact on the combined entity's EPS (accretion/dilution), credit profile (pro forma leverage), and balance sheet. The merger model is used by both sell-side advisors (to evaluate incoming bids) and buy-side advisors (to determine the maximum offer price that creates value).
Sensitivity Analysis for Both Models
LBO Sensitivities
| Primary Axis | Secondary Axis | What It Shows |
|---|---|---|
| Entry multiple | Exit multiple | How multiple expansion/compression affects returns |
| Entry multiple | EBITDA growth | How much growth is needed to justify each entry price |
| Leverage | EBITDA growth | Trade-off between financial engineering and operational improvement |
Merger Sensitivities
| Primary Axis | Secondary Axis | What It Shows |
|---|---|---|
| Purchase premium | Synergy level | Breakeven synergies needed at each premium level |
| Cash % | Stock % | How form of consideration affects accretion/dilution |
| Revenue growth | Margin expansion | Pro forma EPS trajectory over Years 1-3 |
The merger model is built on the buy side to support the board's decision on whether to approve the deal. The accretion/dilution analysis, pro forma credit metrics, and synergy breakeven calculations are presented to the board alongside the standalone valuation and fairness opinion. The board needs to see that the deal creates value (or at least does not destroy value) on an EPS basis, maintains an acceptable credit profile, and is justified by credible synergy estimates. The merger model is typically updated multiple times during negotiations as the offer price, consideration mix, and synergy assumptions evolve.
Both models are living documents during a deal process. They are not built once and presented; they are rebuilt, stress-tested, and updated continuously as new information becomes available, making clean architecture and documented assumptions even more important than for a one-time analysis.
Free Interview PDF Guide