LBO Model Template: Step-by-Step Excel Structure, Formulas, and Worked Example

An LBO model template is a spreadsheet that measures the investor returns and debt capacity of acquiring a company primarily with borrowed funds. On FINRA's 75-item Investment Banking Representative (Series 79) exam, Collection, Analysis, and Evaluation of Data accounts for 37 items alongside 18 items on mergers and acquisitions, tender offers, and financial restructurings. Below is the tab layout, cash waterfall logic, and worked numerical example you need to build a free LBO model template in Excel without locked cells or broken circularity.

Updated

What an LBO valuation model template does

A leveraged buyout model calculates the internal rate of return (IRR) and equity return multiple for a financial sponsor buying a business with debt and equity, or solves backward for the maximum purchase price the buyer can pay while clearing its return target. As the U.S. Bureau of Labor Statistics notes in its Financial Analysts profile (modified August 27, 2026), financial analysts use mathematics and financial modeling to determine the value of financial securities, assess risks, and forecast future growth.

You can practice explaining leveraged buyout mechanics out loud in our investment banking interview prep library before building your file.

  • Long-form three-statement LBO model template: Links a full income statement, balance sheet with goodwill and deferred financing fees, cash flow statement, multi-tranche debt waterfall, and two-way sensitivity tables.

Core modules to build in your free LBO template

Use standard financial color coding: blue font for hardcoded inputs, black font for same-sheet formulas, and green font for cross-sheet links.

On FINRA's Series 79 exam, Function 1 (Collection, Analysis, and Evaluation of Data) accounts for 37 of the 75 exam items, while Function 2 covers 20 items on underwriting and new financing transactions and Function 3 covers 18 items on mergers and acquisitions, tender offers, and financial restructuring transactions.

Standard LBO Model Template Architecture

Standard LBO Model Template Architecture
ModuleKey InputsPrimary Outputs
Transaction AssumptionsLTM EBITDA, entry EV/EBITDA multiple, transaction and financing fees, management rolloverPurchase Enterprise Value, Purchase Equity Value, fee amortization schedule
Sources and UsesLeverage multiples by debt tranche, minimum cash, existing debt refinancedTotal Uses, Total Sources, Sponsor Cash Equity plug, Sources = Uses check
Operating ForecastRevenue growth, COGS and SG&A margins, D&A, Capex, receivables/inventory/payables turnoverProjected EBITDA, EBIT, net income, and Cash Flow Available for Debt Service (CFADS)
Debt & Interest ScheduleBase interest rate plus spread per tranche, mandatory amortization, cash sweep, PIK rateEnding debt by tranche, revolver draws/paydowns, interest expense, leverage ratios
Exit & Returns AttributionExit year, exit EV/EBITDA multiple, management option pool dilutionExit Enterprise Value, Exit Net Debt, Sponsor Proceeds, MOIC, IRR, and returns bridge
Sensitivity TablesStep increments for Entry Multiple, Exit Multiple, EBITDA CAGR, and Initial LeverageTwo-way sensitivity grids showing IRR and MOIC across deal cases

Sources and Uses setup and capital structure priority

Always build the Uses side of your Sources and Uses table first. Uses show the total cash needed to close: buying target equity or enterprise value, refinancing existing debt, funding minimum cash, and paying M&A advisory transaction fees plus lender financing fees. Sources stack each debt tranche in order of seniority, with management rollover equity and sponsor cash equity plugging the gap so Total Sources minus Total Uses equals zero.

Structure your debt schedule in order of legal claim priority so cash sweeps and covenant checks follow the credit agreements and indentures governing repayment, prepayment, events of default, and negative covenants.

  • Revolving Credit Facility: Set up a revolving line in your template to draw when cash flow turns negative and pay down first when cash flow turns positive.
  • Senior Secured Term Loans (Term Loan A and Term Loan B): Include rows for floating cash interest, scheduled principal amortization, and priority excess cash sweeps.
  • Subordinated / Mezzanine Debt and PIK Notes: Model fixed cash interest rates with Payment-in-Kind (PIK) interest that accrues onto ending principal instead of using cash before exit.
  • Preferred and Common Equity: Place sponsor cash equity and management rollover last in line to capture residual value after net debt is repaid at exit.

Cash sweeps, circularity, and return logic

Structuring the cash flow waterfall yourself helps you adapt your template during a modeling test when asked to add a tranche or disable circularity. Deduct mandatory scheduled amortization across all tranches first, and route remaining excess cash flow to revolver paydowns and optional senior debt sweeps.

Second, protect your model from interest circularity crashes. Interest expense lowers taxes and net income, which lowers cash available for optional debt paydown, changing ending debt. Calculating interest on average debt creates an intentional circular loop. Always wire a circuit breaker switch so a typo never locks your sheet in `#VALUE!` or `#REF!` errors.

  • Interest Circuit Breaker Toggle: Build a dedicated toggle cell that switches your interest calculation between average debt and beginning-of-period debt so you can break circular loops immediately.
  • Revolver Draw and Paydown Logic: Link your revolving credit line so cash shortfalls after mandatory debt service trigger an automatic draw and positive cash balances pay down any existing revolver balance first.
  • Optional Cash Sweep Cap: Cap optional senior debt prepayments at remaining principal after scheduled amortization so tranche balances never drop below zero.
  • PIK Interest Accrual: Accrue non-cash Payment-in-Kind interest onto ending subordinated debt on the debt schedule and add the non-cash charge back to operating cash flow.
  • Sponsor Return Metrics (MOIC and IRR): Divide exit sponsor equity proceeds by initial sponsor equity invested for Multiple on Invested Capital, and measure annualized Internal Rate of Return across the initial equity outflow and exit inflows.

Worked LBO example and 3-driver returns attribution bridge

Below is an original worked example created by our team using invented case data so you can check your spreadsheet math to the decimal. A private equity sponsor acquires Northstar Industrial at the end of Year 0 at 10.0x LTM EBITDA of $50.00 million ($500.00 million Purchase Enterprise Value, cash-free and debt-free). Advisory transaction fees are $10.00 million (expensed at close) and debt financing fees are $5.00 million (capitalized and amortized at $1.00 million per year over 5 years), bringing Total Uses to $515.00 million. Sources are 5.0x Senior Unitranche Debt ($250.00 million at 8.0% cash interest, zero mandatory amortization, 100% cash sweep) and $265.00 million of Sponsor Equity.

EBITDA grows by $5.00 million per year to $75.00 million in Year 5. Operating D&A and Capex are both $10.00 million per year, Net Working Capital increases by $2.00 million per year, the tax rate is 25.0%, and `Circ_Switch` is set to `0` (interest on beginning debt). Adding back $11.00 million total D&A and fee amortization, less $10.00 million Capex and $2.00 million NWC outflow, creates a net -$1.00 million cash adjustment below Net Income each year.

Our Worked Example: 5-Year LBO Cash Sweep ($ in millions, invented case data)

Our Worked Example: 5-Year LBO Cash Sweep ($ in millions, invented case data)
Line ItemYear 1Year 2Year 3Year 4Year 5
EBITDA$55.00$60.00$65.00$70.00$75.00
Less: Operating D&A & Fee Amort.(11.00)(11.00)(11.00)(11.00)(11.00)
EBIT44.0049.0054.0059.0064.00
Less: Cash Interest (8.0% Beg. Debt)(20.00)(18.64)(16.90)(14.75)(12.18)
Pre-Tax Income (EBT)24.0030.3637.1044.2551.82
Less: Taxes at 25.0%(6.00)(7.59)(9.28)(11.06)(12.96)
Net Income18.0022.7727.8333.1938.87
Plus: D&A/Amort., Less: Capex & NWC(1.00)(1.00)(1.00)(1.00)(1.00)
CFADS / 100% Debt Sweep17.0021.7726.8332.1937.87
Beginning Debt250.00233.00211.23184.40152.22
Ending Debt$233.00$211.23$184.40$152.22$114.35

Exit returns, attribution bridge, sensitivities, and checklist

Exiting at 10.0x Year 5 EBITDA ($75.00 million) yields an Exit Enterprise Value of $750.00 million. Subtracting $114.35 million of Year 5 Ending Debt leaves Sponsor Exit Equity Proceeds of $635.65 million. Against the $265.00 million initial equity check, MOIC is 2.40x (`$635.65M / $265.00M`) and 5-year IRR is 19.1% (`(635.65 / 265.00)^(1/5), 1`), creating $370.65 million of net equity gain. Add a three-driver attribution bridge so investment committees see what drove that $370.65 million gain: EBITDA Growth contributes +$250.00 million (`($75.00M, $50.00M) x 10.0x`, or 67.4%), Multiple Expansion contributes $0.00 million (`(10.0x, 10.0x) x $75.00M`, or 0.0%), and Net Debt Paydown less Upfront Fees contributes +$120.65 million (`($250.00M, $114.35M), $15.00M fees`, or 32.6%).

Finish your template with dynamic sensitivity tables: one crossing Entry Multiple against Exit Multiple for IRR and MOIC, and another crossing Exit EBITDA against Initial Leverage. Link the corner of each sensitivity grid to your live return output and keep sensitivity tables on the same tab as the inputs they flex.

Before submitting a recruiting test or client model, run this model audit checklist from our team. If you also have consulting private equity interviews, pair this check with our M&A case study examples.

  • Sources equal Uses: Total Sources match Total Uses across every leverage toggle.
  • Balance sheet balances: Total Assets equal Total Liabilities plus Stockholders' Equity at close and in every forecast year.
  • No negative debt: Ending Revolver, Term Loan, and Subordinated balances never turn negative under upside growth.
  • PIK flows to three statements: Accrues to income statement interest, adds back on cash flows, and compounds onto ending debt.
  • Minimum cash holds: Cash never falls below the target minimum balance when all excess cash sweeps.
  • Rollover dilution matches: Sponsor exit ownership reflects rolled management shares and in-the-money options.
  • Circuit breaker resets cleanly: Toggling your interest switch to beginning debt and back to average debt clears any `#REF!` errors.
Public-to-private LBO rules, fairness opinions, and governance

In a public-to-private buyout of an SEC-reporting company, federal rules govern how your valuation model, management rollover, and debt commitments are disclosed. Under SEC Corporation Finance Interpretations on Exchange Act Rule 13e-3 (updated February 11, 2026, Question 101.01), a going-private merger involving an affiliate leveraged buyout of Section 12(g) or Section 15(d) equity requires Schedule 13E-3. Both the issuer and the affiliate must state whether each reasonably believes the transaction is fair or unfair to unaffiliated security holders. Under Section 201.06, a general understanding that target senior management will collectively receive 20% of the surviving entity's equity can trigger Rule 13e-3. That rule can apply even before equity documents are finalized or executed because the buyer is deemed on both sides of the deal. Question 117.06 confirms that Item 9 of Schedule 13E-3 and Item 1015 of Regulation M-A cover both written and oral financial advisor reports, opinions, and appraisals.

Debt commitments in tender-offer LBOs also follow strict SEC standards. Under SEC Tender Offer Rules and Schedules Interpretations (updated July 9, 2026, Questions 101.18 and 101.19), a tender offer counts as fully financed when the offeror holds a binding lender commitment letter. A lender's 'highly confident' letter does not make an offer fully financed. Securing committed financing for an unfinanced or partly financed offer is a material change requiring a prompt Schedule TO amendment and shareholder dissemination.

FINRA Rule 5150 governs fairness opinions provided or described to public shareholders. Member firms must maintain written procedures to determine whether the valuation analyses used are appropriate. When a firm uses a fairness committee, its procedures must include review and approval by personnel who do not serve on the deal team. The opinion must also disclose contingent fees, material relationships over the past two years, whether company-supplied information was independently verified, and whether insider compensation fairness was evaluated. Under Section 217.01 of the SEC's going-private interpretations, the financial advisor need not be independent of the issuer, but material relationships and contingent or zero fees must be quantified.

From FINRA Series 79, Investment Banking Representative Exam, FINRA Rule 5150: Fairness Opinions, U.S. Bureau of Labor Statistics: Financial Analysts Occupational Outlook Handbook, SEC Corporation Finance Interpretations: Going Private Transactions, Exchange Act Rule 13e-3 and Schedule 13E-3 and SEC Corporation Finance Interpretations: Tender Offer Rules and Schedules.

1 practice question and sample answer

Say your answer out loud first, then read the sample answer.

Practice question

  1. Walk me through how you build an LBO model from scratch and what drives the returns.

    Sample answer. Example answer written by our team. The applicant is invented. I build an LBO model in five steps. First, I set the transaction assumptions: I multiply LTM EBITDA by the entry multiple to get Purchase Enterprise Value, then build a Sources and Uses table that accounts for refinancing existing debt, transaction and financing fees, each new debt tranche by leverage multiple, any management rollover, and the sponsor cash equity plug so Sources equal Uses. Second, I project the operating forecast over five years, driving revenue, EBITDA, taxes, Capex, and changes in net working capital to get Cash Flow Available for Debt Service. Third, I link the debt schedule in order of seniority: cash flow pays mandatory principal amortization first, and any excess cash sweeps down the revolver and senior term debt, while non-cash PIK interest accrues onto subordinated principal. Fourth, I calculate exit value in Year 5 by multiplying Exit EBITDA by an exit multiple, then subtract ending net debt to find exit equity proceeds. Fifth, I compute MOIC and IRR against the initial sponsor equity check and sensitize entry and exit multiples. Three levers drive returns: EBITDA growth, debt paydown from cash flow, and multiple expansion.

    Gives a numbered five-step roadmap first, names the exact bridge from EBITDA to Cash Flow Available for Debt Service and debt seniority, and closes cleanly on the three return drivers in under 90 seconds.

Practice the interview, out loud

Soreno's AI interviewer asks banking fit and technical questions, follows up on your answers and scores them.

Questions people ask

What is the difference between a short-form and a long-form LBO model template?

A short-form LBO template skips a full projected balance sheet and calculates Cash Flow Available for Debt Service directly from the income statement, Capex, and working capital assumptions on one tab for quick screening or interviews. A long-form LBO valuation model template links all three financial statements, goodwill accounting, deferred financing fees, and multi-tranche credit covenants.

Do you need a balance sheet to build a working LBO template in Excel?

Full deal models add the balance sheet to track working capital turnover ratios, balance sheet covenants, and purchase accounting.

How do you fix circular reference errors in an LBO debt schedule?

Add a dedicated circuit breaker toggle cell that switches your interest calculation from average debt to beginning-of-year debt. Switching the toggle to beginning debt breaks the circular loop between interest expense, taxes, cash flow, and ending debt so any workbook error resets cleanly.

Why do LBO models value companies using EV/EBITDA multiples instead of P/E?

An LBO changes a target company's debt load, interest expense, and net income from closing through exit, which distorts equity-level Price-to-Earnings multiples across capital structures. Enterprise Value to EBITDA is capital-structure neutral and isolates pre-debt operating cash flow capacity across comparable companies and precedent transactions.

When does management equity rollover in a public LBO trigger SEC Rule 13e-3?

Under Section 201.06 of the SEC's going-private interpretations, even a general understanding that a public target's senior management will receive a material equity stake (such as 20%) in the surviving buyout entity can place the financial buyer on both sides of the deal as an affiliate. That triggers Schedule 13E-3 filing and fairness disclosure obligations to unaffiliated shareholders.

Keep going