LBO Model in Excel: Build a Leveraged Buyout From Scratch (2026)
Private equity firms deploy over $1 trillion annually in leveraged buyouts, and every single deal starts with an LBO model built in Excel. Whether you are preparing for a PE interview, evaluating a bolt-on acquisition, or stress-testing debt capacity for a sponsor client, the LBO model is the single most testable skill in finance.
This guide walks you through building a complete LBO model in Excel from a blank workbook — covering deal assumptions, sources and uses, financial projections, the debt schedule (including the dreaded circular reference), and returns analysis. Every formula is included so you can follow along cell by cell.
What Is an LBO Model and Why Does It Matter?
A leveraged buyout model is a financial tool that simulates acquiring a company using a significant amount of debt, operating it for 3–7 years, and selling it at exit. The model answers one question: what equity return (IRR and MOIC) can the sponsor expect?
LBO models matter because they are the primary decision-making framework for private equity firms. They also appear on nearly every PE interview and modeling test. Unlike a DCF, which values a company based on intrinsic cash flows, an LBO model evaluates a deal's feasibility from the buyer's perspective — factoring in leverage, debt paydown, and exit timing.
How Is an LBO Model Different From a DCF?
An LBO model focuses on equity returns to the sponsor, while a DCF model focuses on enterprise value based on unlevered free cash flows. Here is a side-by-side comparison:
| Feature | LBO Model | DCF Model |
|---|---|---|
| Primary output | IRR and MOIC | Enterprise / equity value |
| Discount rate | Implied by purchase & exit prices | WACC |
| Debt modeling | Detailed tranches, amortization, sweeps | Simplified or excluded |
| Time horizon | Typically 5–7 years | 5–10 years + terminal value |
| Circular references | Common (avg. balance interest) | Rare |
| Primary user | Private equity sponsors | Investment bankers, equity research |
| Key sensitivity | Entry/exit multiple, leverage | WACC, terminal growth rate |
Understanding this distinction helps you know when each model is appropriate and how to structure your workbook. While an LBO model evaluates returns for a financial buyer (private equity), a merger model in Excel answers the same transaction from the strategic acquirer's perspective — testing whether the combined company would produce accretion or dilution to the buyer's earnings per share.
Step 1: Set Up Deal Assumptions
Every LBO model starts with a clean assumptions block. Create a dedicated section at the top of your worksheet (or on a separate "Assumptions" tab) with these inputs:
Transaction Assumptions
Cell B3: LTM EBITDA = 100 ($ millions)
Cell B4: Entry Multiple = 8.0x
Cell B5: Enterprise Value = B3 * B4 → 800
Cell B6: Net Debt at Close = 0 (assume cash-free, debt-free)
Cell B7: Equity Value = B5 - B6 → 800
Cell B8: Transaction Fees = B5 * 2% → 16
Cell B9: Total Uses = B5 + B8 → 816
Operating Assumptions
Cell B12: Revenue Growth Rate = 5%
Cell B13: EBITDA Margin = 25%
Cell B14: D&A (% of Revenue) = 3%
Cell B15: Capex (% of Revenue) = 4%
Cell B16: Change in NWC (% of ΔRev)= 10%
Cell B17: Tax Rate = 25%
Exit Assumptions
Cell B20: Hold Period = 5 years
Cell B21: Exit Multiple = 8.0x (same as entry for conservative case)
Pro tip: Color-code all hard-coded inputs in blue font. This is the industry standard convention and makes it instantly clear which cells are assumptions vs. formulas.
Step 2: Build the Sources and Uses Table
The sources and uses table shows where the money comes from and where it goes. This is the foundation of your capital structure.
graph LR
subgraph Sources
S1[Term Loan A]
S2[Term Loan B]
S3[Sponsor Equity]
end
subgraph Uses
U1[Enterprise Value]
U2[Transaction Fees]
end
S1 --> U1
S2 --> U1
S3 --> U1
S3 --> U2
Uses of Funds
Enterprise Value = 800
Transaction Fees = 16
─────────────────────────────
Total Uses = 816
Sources of Funds
Revolver (drawn at close) = 0
Term Loan A (TLA) = 240 (3.0x EBITDA, or 30% of EV)
Term Loan B (TLB) = 280 (3.5x EBITDA, or 35% of EV)
───────────────────────────────────
Total Debt = 520 (6.5x leverage)
Sponsor Equity (plug) = 296 (= Total Uses - Total Debt)
───────────────────────────────────
Total Sources = 816
The sponsor equity is always the plug — it equals total uses minus total debt. In Excel:
=Total_Uses - TLA - TLB
This tells you the equity check: the PE firm needs to invest $296M of its own capital.
Step 3: Project Free Cash Flows
Now build a 5-year projection. Start with revenue, flow down to EBITDA, then calculate levered free cash flow — the cash available to repay debt.
Projection Layout (Years 1–5)
Set up columns for each projection year. Here are the key formulas for Year 1 (column D), assuming Year 0 baseline revenue is $400M:
Revenue = Prior_Rev * (1 + Growth_Rate)
= 400 * 1.05 = 420
EBITDA = Revenue * EBITDA_Margin
= 420 * 25% = 105
D&A = Revenue * DA_Pct
= 420 * 3% = 12.6
EBIT = EBITDA - D&A
= 105 - 12.6 = 92.4
Taxes = EBIT * Tax_Rate
= 92.4 * 25% = 23.1
NOPAT = EBIT - Taxes
= 92.4 - 23.1 = 69.3
(+) D&A = 12.6
(-) Capex = Revenue * Capex_Pct = 420 * 4% = 16.8
(-) Chg in NWC = (Revenue - Prior_Rev) * NWC_Pct = 20 * 10% = 2.0
Levered FCF = NOPAT + D&A - Capex - Chg_NWC
= 69.3 + 12.6 - 16.8 - 2.0 = 63.1
Copy these formulas across all five projection years. The levered free cash flow tells you how much cash is available each year to service and repay debt.
Step 4: Build the Debt Schedule
The debt schedule is the heart of every LBO model — and where most people get stuck. You need to model mandatory amortization, optional prepayments, and interest expense for each debt tranche.
Term Loan A Schedule
TLA typically amortizes 5–10% per year with the remainder due at maturity.
Beginning Balance = Prior year Ending Balance (Year 1 = 240)
Mandatory Amort = Original_TLA * 5% = 12 per year
Optional Prepay = MAX(0, MIN(Beg_Bal - Mand_Amort, Levered_FCF - Interest_Expense - TLB_Amort))
Ending Balance = Beg_Bal - Mand_Amort - Optional_Prepay
Term Loan B Schedule
TLB has minimal amortization (typically 1% per year) and is repaid at exit.
Beginning Balance = Prior year Ending Balance (Year 1 = 280)
Mandatory Amort = Original_TLB * 1% = 2.8 per year
Optional Prepay = 0 (TLA is repaid first in standard waterfall)
Ending Balance = Beg_Bal - Mand_Amort
Interest Expense
This is where the circular reference appears. Interest should be calculated on the average of beginning and ending debt balances:
TLA Interest = AVERAGE(TLA_Beg_Bal, TLA_Ending_Bal) * TLA_Rate
TLB Interest = AVERAGE(TLB_Beg_Bal, TLB_Ending_Bal) * TLB_Rate
Using average balances is more accurate than beginning-only, but it creates a loop: interest affects free cash flow, which affects debt repayment, which affects the ending balance used to calculate interest.
How Do You Fix Circular References in an LBO Model?
The circular reference in an LBO model occurs because interest expense depends on debt balances, which depend on cash flow available for repayment, which depends on interest expense. There are three standard fixes:
Option A — Enable iterative calculations (recommended):
- Go to File → Options → Formulas
- Check Enable iterative calculation
- Set Maximum Iterations to 100 and Maximum Change to 0.001
Excel will solve the loop automatically. This is the industry standard approach.
Option B — Add a circuit breaker toggle:
Create a named cell called Circ (set to 1 or 0). Wrap your interest formula:
=IF(Circ=1, AVERAGE(Beg_Bal, End_Bal) * Rate, Beg_Bal * Rate)
When Circ = 1, interest uses the average balance (circular). When Circ = 0, it falls back to beginning balance only (no circularity). If the model breaks, flip the switch to 0, fix the issue, then flip back to 1.
Option C — Use beginning balance only:
Simply calculate interest on the beginning balance. This avoids circularity entirely at the cost of slight inaccuracy. For interview tests with time pressure, this is perfectly acceptable.
| Approach | Accuracy | Complexity | Best For |
|---|---|---|---|
| Iterative calculation | High | Medium | Full models, on-the-job work |
| Circuit breaker toggle | High | Higher | Complex models with multiple debt tranches |
| Beginning balance only | Moderate | Low | Interview tests, quick analyses |
The two-tranche structure above covers most LBO use cases. For a comprehensive guide to multi-tranche capital structures — revolvers, TLA/TLB, senior notes, and the full suite of credit coverage ratios lenders actually monitor — see our debt schedule Excel credit analysis guide.
Step 5: Model the Cash Sweep
A cash sweep (or excess cash flow sweep) forces the borrower to use a percentage of remaining free cash flow to prepay debt after mandatory amortization and interest. Typical sweep percentages range from 50% to 75%.
Here is the formula logic for a 50% cash sweep applied to TLA first:
Cash Available for Sweep = Levered_FCF - Total_Interest - Total_Mand_Amort
Sweep Amount = MAX(0, Cash_Available * 50%)
TLA Prepayment = MIN(Sweep_Amount, TLA_Beg_Bal - TLA_Mand_Amort)
TLB Prepayment = MIN(Sweep_Amount - TLA_Prepayment, TLB_Beg_Bal - TLB_Mand_Amort)
The MIN functions ensure you never repay more than the outstanding balance. The waterfall logic — TLA before TLB — reflects the seniority of the debt stack.
graph TD
A[Levered Free Cash Flow] --> B[Pay Interest on All Tranches]
B --> C[Pay Mandatory Amortization]
C --> D{Remaining Cash > 0?}
D -->|Yes| E[Cash Sweep: 50% of Excess]
D -->|No| F[No Optional Prepayment]
E --> G[Repay TLA First]
G --> H{TLA Fully Repaid?}
H -->|Yes| I[Repay TLB with Remainder]
H -->|No| J[All Sweep to TLA]
Step 6: Calculate Returns — IRR and MOIC
This is the payoff. After projecting five years of operations and debt paydown, calculate the sponsor's equity return at exit.
Exit Equity Value
Exit EBITDA = Year 5 EBITDA (projected)
Exit Enterprise Val = Exit_EBITDA * Exit_Multiple
(-) Net Debt at Exit = TLA_End_Yr5 + TLB_End_Yr5
Exit Equity Value = Exit_EV - Net_Debt_at_Exit
MOIC (Multiple on Invested Capital)
MOIC = Exit_Equity_Value / Sponsor_Equity_Invested
A 2.0x MOIC means the sponsor doubled their money. PE firms generally target 2.0x–3.0x MOIC.
IRR (Internal Rate of Return)
Set up a row of cash flows for the Excel IRR function:
Year 0: -Sponsor_Equity (negative, it's an outflow)
Year 1: 0 (no dividends in standard case)
Year 2: 0
Year 3: 0
Year 4: 0
Year 5: +Exit_Equity_Value
IRR = IRR(Year0:Year5) → e.g., 22.3%
Most PE firms target a 20%+ IRR as their minimum return threshold. If your model shows an IRR below 15%, the deal likely does not work at the assumed leverage and entry multiple.
💡 Pro Tip: In interviews, if you calculate an IRR in the low teens, don't just state the number — explain what levers could improve it. Higher leverage, lower entry multiple, faster EBITDA growth, or multiple expansion at exit all push returns up. This demonstrates deal judgment, not just modeling ability.
What Drives Returns in an LBO?
There are three primary return drivers, often called the value creation bridge:
- EBITDA growth — Revenue growth and margin expansion increase the exit EBITDA
- Multiple expansion — Selling at a higher multiple than entry (not assumed in conservative cases)
- Debt paydown — Using free cash flow to repay debt increases equity value at exit
You can quantify each driver's contribution to total return, which is a common PE interview question. Once exit equity value is determined, a PE waterfall model governs how those proceeds are distributed between the fund's GP and its limited partners — across return of capital, preferred return, catch-up, and carried interest tiers. In management carve-out situations or growth equity deals where founders retain equity, a cap table model in Excel tracks how exit equity value is first split between preferred investors, option holders, and common shareholders — before the sponsor's share enters the fund-level waterfall.
Step 7: Run Sensitivity Analysis
No LBO model is complete without sensitivity analysis to test how returns change under different assumptions. Build a two-way data table varying entry multiple and exit multiple:
1. Type the IRR formula in the top-left corner of your table
2. Enter exit multiples across the top row (7.0x, 7.5x, 8.0x, 8.5x, 9.0x)
3. Enter entry multiples down the left column (7.0x, 7.5x, 8.0x, 8.5x, 9.0x)
4. Select the entire table range
5. Go to Data → What-If Analysis → Data Table
6. Row input cell = Exit_Multiple assumption cell
7. Column input cell = Entry_Multiple assumption cell
This produces a matrix showing IRR at every combination. Use conditional formatting to highlight cells above 20% IRR in green and below 15% in red — this instantly shows the "zone of feasibility" for the deal.
You should also build a separate sensitivity table for leverage (Debt/EBITDA) vs. EBITDA growth rate, as these are the two assumptions with the most impact on returns.
Common LBO Modeling Mistakes to Avoid
After reviewing hundreds of LBO models (and breaking a few ourselves), here are the errors we see most often:
Forgetting to enable iterative calculations — Your model will show
#REF!or zero interest. Always check File → Options → Formulas before building the debt schedule.Repaying more debt than the balance — If your prepayment formula can go negative, add a
MAX(0, ...)wrapper. Without it, you will generate phantom cash from thin air.Ignoring the debt repayment waterfall — Senior debt (TLA) must be repaid before subordinated debt (TLB, mezzanine). Mixing up the priority cascade will produce incorrect ending balances.
Using EBITDA instead of levered FCF for debt repayment — Cash available to repay debt is after taxes, interest, capex, and working capital changes. EBITDA overstates available cash significantly.
Hardcoding values instead of linking to assumptions — Every number in your projection should trace back to an assumption cell. Hardcoded numbers break when you run scenarios.
Not stress-testing negative scenarios — What happens if revenue declines 10%? If the company cannot service its debt, the deal fails regardless of upside projections.
A disciplined pre-delivery review catches the mechanical errors before they reach a deal team or lender — the financial model audit checklist for Excel provides a six-phase protocol that applies directly to LBO models.
Frequently Asked Questions
How long does it take to build an LBO model in Excel?
A simple LBO model with one debt tranche takes 30–60 minutes once you know the structure. A full model with multiple tranches, a revolver, cash sweep, and sensitivity tables takes 3–5 hours. PE interview modeling tests typically give you 1–3 hours to build a simplified version from a prompt.
What Excel functions are most important for LBO modeling?
The essential functions are IRR and XIRR for return calculations, MIN and MAX for debt repayment caps, AVERAGE for mid-year debt balances, IF for circuit breaker logic, and DATA TABLE for sensitivity analysis — all part of the Excel formula toolkit every analyst should master. You will also use SUMPRODUCT if modeling multiple debt tranches with varying rates. For deal teams that build multiple LBO models, Excel's LAMBDA function lets you define MOIC, IRR, and other recurring calculations as named, reusable functions — no VBA required.
Can I build an LBO model in Google Sheets?
Yes, Google Sheets supports iterative calculations (File → Settings → Calculation → Iterative calculation). However, large LBO models with multiple scenarios run noticeably slower in Sheets than in desktop Excel. For interview prep and learning, Sheets works fine. For production models, use Excel.
What leverage ratio is typical in an LBO?
Total Debt/EBITDA in LBOs typically ranges from 4.0x to 7.0x, depending on the industry, interest rate environment, and deal size. Senior secured debt is usually 3.0x–4.5x EBITDA, with the remainder in subordinated or mezzanine debt. Lenders stress-test coverage ratios before approving these levels. For infrastructure and energy deals funded with non-recourse debt, lenders take a different approach: rather than EBITDA multiples, a project finance model sculpts debt repayments around DSCR targets, ensuring each period's cash flow can service its debt independently of any parent company guarantee.
How do I practice LBO modeling for interviews?
Start by building the simple model outlined in this guide from scratch — no template. Then rebuild it under time pressure (target 60 minutes). Once comfortable, add complexity: a revolver, PIK toggle notes, management rollover equity, and dividend recapitalizations. Resources from Wall Street Prep and Macabacus offer free templates to benchmark your work.
Wrapping Up
The LBO model is one of the most practical and testable skills in finance. Once you have built one from scratch, the structure becomes second nature — every deal just changes the inputs. Focus on getting the debt schedule and cash flow waterfall right, and the returns analysis follows naturally.
If you find yourself spending more time debugging formulas than analyzing deals, tools like VeloraAI can help by generating complex Excel formulas from plain English descriptions — particularly useful when constructing nested IF/MIN/MAX logic for debt waterfalls.
The best way to learn is to build. Open a blank workbook, plug in the assumptions from this guide, and start modeling.