The Anatomy of a Leveraged Buyout: Step-by-Step Insights into Financial Modeling

By Armand Pjeci

Introduction

This article offers a comprehensive, step-by-step guide to building an LBO model in Excel, tailored for students, analysts, and finance enthusiasts aiming to master a fundamental tool of the private equity sector.

1. What is an LBO?

A Leveraged Buyout (LBO) is an acquisition strategy whereby an investor, commonly a private equity fund, secures control of a company by financing the transaction through a blend of debt and equity. The hallmark of an LBO is its substantial reliance on financial leverage: typically, over 60% of the acquisition cost is financed by debt instruments, with equity forming merely a minor portion of the total capital invested.

The strategic employment of leverage significantly enhances returns: should the transaction prove successful, even modest EBITDA growth, effective deleveraging, and an enhanced exit multiple may yield considerable returns on the equity invested. Conversely, leverage also exacerbates adverse outcomes: should operational performance decline or market conditions contract, losses may be substantial, potentially obliterating the entire equity investment.

A thorough understanding of LBO dynamics is indispensable for those aspiring to a career in private equity or investment banking. The proficiency to construct and evaluate an LBO model in Excel constitutes a vital technical competency, essential not only for recruitment processes but also for the practical assessment of investment opportunities.

2. Best Practices for LBO Modelling in Excel

In addition to constructing a model accurately, adhering to best practices in modelling is essential to ensure consistency, clarity, and reliability. The following are key guidelines:

Colour Coding
Adopt a clear colour convention to differentiate various cell types:

• Blue: Inputs and fixed assumptions (e.g., EBITDA margin, interest rate).
• Black: Formulae and internal references within the same worksheet.
• Green: References to cells in other worksheets (e.g., values drawn from the “Assumptions” tab).

Avoid Hard Coding
With the exception of initial assumptions (in blue), no numbers should be manually entered into formulae.

• Every cell should contain a link or a calculation, rather than arbitrary static values.
• This approach enhances the model’s dynamism, simplifies updates, and reduces the risk of errors.

Always Validate Your Model

• Upon completion, conduct a thorough review.
• Incorporate automatic model checks (e.g., “sources must equal uses”).
• These checks help identify structural errors or inconsistencies before the model is used for real-world decision-making.

3. Structure of the LBO Modelling Process

The construction of an effective Leveraged Buyout (LBO) model demands rigour, sequential logic, and meticulous attention to internal consistency. Below, we outline the seven essential steps to develop the model in a structured and professional manner:

• Calculation of the Purchase Price
  Determine the acquisition value of the company, typically based on a multiple of EBITDA.
• Analysis of Financing
  Define the capital structure used to finance the transaction.
• Setup of Sources and Uses
  Reconcile the sources of capital with their uses, ensuring balance.
• Construction of the Income Statement Projections
  Project revenues, margins, depreciation, interest, and net profit.
• Construction of Free Cash Flow (FCF)
  Calculate the cash available for debt servicing.
• Debt Repayment Waterfall
  Model annual deleveraging, incorporating mandatory and optional repayment options.
• Analysis of Exit Valuation
  Evaluate investor returns through the Money-on-Money (MoM) multiple and Internal Rate of Return (IRR).

Step 1 – Calculation of the Purchase Price

The initial step in constructing an LBO model involves determining the amount the private equity fund is willing to pay for the target company. This is typically calculated using a multiple of the company’s EBITDA.

For instance, if the company’s projected EBITDA is €50 million and the applied multiple is 5.00x, the purchase price would amount to €250 million. Alternatively, an absolute purchase price (e.g., €250 million) may be provided directly; however, in most cases, the starting point is an operational metric (such as EBITDA) combined with a multiple consistent with the relevant market.

Step 2 – Analysis of the Financing Structure

Once the purchase price has been determined, it is necessary to establish how the transaction will be financed.

Typically, the capital structure comprises:

• Debt (e.g., 60–70% of the purchase price)
• Equity (e.g., 30–40%)

If only one component is provided (e.g., “60% debt”), the other is calculated by difference:

Step 3 – Sources and Uses Configuration

Having determined the purchase price and financing structure, the next step is to construct the Sources and Uses table, a cornerstone of the LBO model. This table ensures that the funds raised (Sources) fully cover the transaction costs (Uses).

Sources

These represent all financial resources employed to fund the acquisition. The most common sources include:

• Senior debt (e.g., Term Loan, High-Yield Bonds, Mezzanine Financing)
• Investor equity

In more complex transactions, additional sources may feature, such as:

• Seller rollover equity
• Deferred purchase price notes

Uses
These indicate how the funds will be allocated:

• The primary use is the purchase price of the target company. In more detailed models, ancillary costs (typically 5% of the purchase price) are also included, such as:

• Advisory fees
• Legal and accounting expenses
• Other transaction-related costs

It is imperative that the total Sources equal the total Uses.

Step 4 – Projection of the Income Statement

The fourth phase involves modelling the company’s Income Statement for the holding period, starting from revenues and culminating in net profit. The objective is to estimate EBITDA and Net Income in a manner consistent with the operational and financial assumptions of the LBO.

Revenue
The calculation begins with an initial value (e.g., €300 million in 2025) and applies a growth rate (e.g., +4% for the first year). The Excel formula is:

Cost of Goods Sold (COGS)

As COGS is expressed as a percentage of revenue, one need only multiply the percentage by the revenue for the relevant year to obtain the value. The same logic applies to the cells for SG&A, R&D, and D&A.

EBITDA

Having directly entered costs with a negative sign in the model, one can proceed with a straightforward summation to calculate intermediate values such as EBITDA, EBIT, EBT, and Net Income.

Taxes
Calculated by applying the tax rate to the Earnings Before Tax (EBT):

Important – Interest Expense

Interest expense represents the cost of debt used to finance the acquisition. To calculate it, the outstanding debt balance is multiplied by the applicable interest rate. As the debt balance changes annually (due to repayments funded by Free Cash Flow), the interest expense adjusts accordingly. This calculation is performed in the Debt Paydown section, and the resulting values are recorded here as costs, expressed as negative figures.

The Issue of Circularity

A circular dependency arises due to the following interrelationships:

• Interest expense depends on the debt balance.
• The debt balance depends on Free Cash Flow.
• However, Free Cash Flow is influenced by the interest expense.

This creates a circular calculation loop, which can lead to errors in Excel, particularly when using the average of the opening and closing debt balances—a more precise method—rather than solely the opening balance to compute interest.

To address this, an IF function with a “circularity switch” is implemented, allowing the interest calculation to be toggled on or off during debugging:

• 1 = Interest calculation enabled, with interest computed using the average of the opening and closing debt balances.
• 0 = Interest calculation temporarily disabled, useful when Excel flags an error (e.g., #REF!), with interest calculated using only the opening debt balance.

A simple example:

Step 5 – Calculation of Free Cash Flow (FCF)

Having projected the Profit and Loss Statement, the next step is to construct the Free Cash Flow (FCF), which represents the cash available to service debt repayments. In LBO models, the levered FCF is calculated, that is, the residual cash flow after accounting for interest and taxes.

This formula enables the estimation of annual net cash flow, which is critical for modelling deleveraging in subsequent years.

Breakdown of Components Included in the Calculation:

• Taxes
  Taxes are derived directly from the profit and loss statement and represent an actual cash outflow. It is assumed that all taxes are paid in cash.
• Interest
  Interest expenses are also taken directly from the profit and loss statement. They reflect the cost of outstanding debt and are deducted as they directly impact available cash.
• CapEx (Capital Expenditures)
  CapEx represents annual investments in durable assets (e.g., plant and equipment).
  In the model, it is calculated as a fixed percentage of revenue, for example, 10%:
  CapEx = Revenue × 10%
  The negative sign indicates that this is a cash outflow.
• Change in Working Capital (Change in NWC)
  An increase in operational working capital (e.g., higher receivables or inventory) constitutes a cash outflow. In this model, it is assumed to be a fixed annual amount (e.g., −€5M), which is subtracted from the cash flow.

The outcome of this step is the net cash available for debt repayment. This cash will be used in the subsequent phase to simulate the Debt Paydown Waterfall, which outlines the debt amortisation schedule over time.

Step 6 – Debt Repayment

The sixth step of the LBO model involves simulating the annual debt repayment schedule, utilising the Free Cash Flow calculated earlier, and determining the interest expenses associated with the outstanding debt, which are then incorporated into the income statement.

Structure of the Calculation

• Beginning Debt Balance
  The initial debt balance in the first year is sourced from the Sources table (e.g., £120M). In subsequent years, the beginning debt balance is simply the ending debt balance of the previous year.
• Paydown (Amortising)
  This represents the portion of debt repaid, in this case corresponding to the entire free cash flow (FCF) generated.
  Note: In some instances, the debt repayment schedule may be fixed (e.g., for an initial debt of €120M, €12M is repaid annually over 10 years). In more advanced LBO models, in addition to the amortisation schedule, a cash sweep may be incorporated, whereby excess cash—after all mandatory payments— is used to make additional repayments on the outstanding debt.
• Ending Debt Balance
  Beginning Debt + Paydown
  (Since the paydown is negative, this effectively results in a subtraction.)

Step 7 – Exit Analysis: Evaluating Returns for the Investor

The final step in the operational model is the Exit Analysis, which determines the returns generated by the private equity fund at the end of the holding period.

The key metrics calculated in this phase are:

• Money-on-Money Multiple (MoM)
• Internal Rate of Return (IRR)

Components of the Exit Analysis:

Purchase Price

This is the price paid at entry and should be directly linked to the Uses table. It is entered with a negative sign, as it represents an initial cash outflow.

Debt

The debt repayment amount is recorded with a negative sign, alongside the residual debt at the end of the period, which constitutes a final outflow to settle the remaining debt.

Annual Cash Flows

In this model, it is assumed that all Free Cash Flow is used to repay debt. Consequently, the balance between debt repayment and cash flows to the investor remains zero during the holding period. All returns are realised upon exit.

Exit Value

This is the sale price of the company, calculated as: Exit Value = Exit EBITDA × Exit Multiple

In the example provided, a multiple of 5.00x is applied to the 2030 EBITDA.

Performance Metrics

• Money-on-Money Multiple (MoM):
  This measures the multiple of the invested capital, calculated as:

MoM = Final Equity / Initial Equity

• Internal Rate of Return (IRR):
  This metric quantifies the annualised return on the investment, accounting for the time value of money. The IRR is calculated in Excel based on the equity cash flows. In this model, an IRR of approximately 21% and a MoM of 2.57x are achieved.

Conclusion

In conclusion, the mastery of Leveraged Buyout modelling requires both technical aptitude and strategic comprehension of private equity mechanisms. The seven-step framework delineated herein provides a robust foundation for evaluating prospective acquisitions, from purchase price determination through to exit analysis. Through meticulous attention to financial leverage, cash flow projections, and debt servicing schedules, analysts can effectively assess risk-return profiles and identify value creation opportunities. This disciplined modelling approach ultimately serves as an indispensable decision-making instrument that underpins successful private equity investment strategies in contemporary competitive markets.

Bibliography

CFI, https://corporatefinanceinstitute.com/resources/financial-modeling/steps-to-lbo-modeling/

Harvard, https://corpgov.law.harvard.edu/2022/01/14/buyouts-a-primer/

Wall Street Prep, https://www.wallstreetprep.com/knowledge/basics-of-an-lbo-model/