We’ll cover the basics of LBO models so you’re ready for your first LBO modeling test. It’s a certainty in any private equity recruiting process, so the sooner you’re prepared, the better.

This LBO will be a relatively simple model, equivalent to what you might find in a 30 to 60-minute test (no balance sheet or complex items, but more advanced than a paper LBO). Let’s dive in.

LBO Model Overview

To quickly get you up to speed, let’s quickly cover what an LBO model is (skip this section if you already know).

LBO stands for Leveraged Buyout, which is when a private equity fund, known as a financial sponsor, acquires a target company using a mixture of debt (borrowed money) and equity (their own money) to finance the purchase.

After acquiring the business, the sponsor will hold the company for a period of time before ultimately selling it. The goal is to end up with more money at the end of the deal than they had in the beginning, earning a return on their investment.

How do they earn a return?

There are three primary ways for private equity funds to create value in an LBO:

1. Multiple Expansion

   • Multiple expansion is the finance bro way of saying that you sold the company for more than you bought it for, at a given level of earnings

   • When you purchase a business, the cost of that business will be their EBITDA (x) an entry multiple

     • For example, a business earning $100M of EBITDA is acquired at a 10.0x entry multiple for an enterprise value (purchase price) of $1B

   • The idea with multiple expansion is that your exit multiple, what you sell the business on, is higher than your entry multiple

     • For example, you sell the same $100M EBITDA business for a 15.0x exit multiple for an enterprise value of $1.5B

2. EBITDA Growth

   • The next way to create value is to grow the business and increase its earnings

   • For example, you buy the same $100M EBITDA business at a 10.0x entry multiple for $1B, increase EBITDA to $200M while you own the company, and sell it at a 10.0x exit multiple for $2B

3. The Use of Leverage

   • The last main avenue for value creation is via the use of leverage, or debt financing, to complete the deal

   • This amplifies your returns (whether positive or negative)

   • For example, you buy the $100M EBITDA business at an entry multiple of 10.0x for $1B, financing the deal with $500M of debt (50%) and $500M of equity (50%)

     • You again increase EBITDA to $200M and sell the business at a 10.0x exit multiple for $2B

     • From this sale, you pay back the $500M of debt you borrowed and are left with $1.5B of proceeds, a 3.0x return compared to the $500M of your own money that you put into the deal

     • If you hadn’t used leverage, you would have used $1B of your cash and returned $2B, for a lower 2.0x return

Hop Into Excel

Now that we’ve got the intro out of the way, let’s learn how to actually build out an LBO model in Excel. Download the sample model test below so you can follow along. This will have tabs for the test prompt, a blank template, and a completed model answer key.

Password: TransactedTest

 

Investment Entry & Transaction Assumptions

The first thing we’ll need to do is understand our purchase of the target company. Similar to the example above, we’ll look at LTM EBITDA and multiply it by our entry multiple to get to our purchase price.

With this specific prompt, we’re not given our LTM EBITDA. Instead, we’ll have to calculate it in our income statement. We’ll get to this step in a little bit, so you can go ahead and build out everything you need for the purchase of the target, but leave the LTM EBITDA blank for now.

LTM EBITDA

Let’s quickly calculate our LTM EBITDA so we can continue building out the transaction entry and sources & uses.

From our prompt, grab the LTM revenue and margins. Drop those in for our historical period, and link them up so that we get to our LTM EBITDA.

Now that we have that, go ahead and link up that $162M of LTM EBITDA to your transaction entry in the first step.

Sources & Uses

The next thing we’ll need to do is understand how we’re financing this deal. The sources & uses does exactly what it sounds like: tells us how much money we need to complete the deal and where we’re getting all that money from.

Uses

With our sources & uses, it’s actually a little bit easier to work backward, starting with our uses. No surprise, the biggest item within our uses will be the money we need to buy the target company, or the purchase price that we started calculating in step one.

Drop in a line for the purchase price, and link up the cell to our calculation in the prior step ($1.9B).

Along with our purchase price, we’ll also have some transaction fees and financing fees. The transaction fees are the cost of any buy-side investment bankers, legal spend, and consultants or diligence providers we’ve used during the deal process.

The prompt tells us that’s 2% of the purchase price, so we can go ahead and link that up.

The financing fees will be related to our debt financing. They’re just an additional cost that our lender will tack on when we take out the loans. We’ll leave these blank for now and complete them after we’ve outlined our debt financing, which we’ll do next.

Last item, we have some cash that we’re putting on the balance sheet of the company. This is just some of our money that will act as a buffer for the business post-transaction, so they still have enough to run the business. Drop this $10M into the uses and we’ll be all set.

Sources

Now that we know how much cash we need, we can go ahead and figure out where we’re getting it all from.

We know this will be a mixture of debt and equity. We’ll sketch out our debt financing first, because this will inform our equity need. Essentially, we’re taking out as much debt as we can (or as much as is prudent), and then whatever amount we still need we’ll cover with our own money.

Copy over everything in the prompt into a separate section on financing assumptions. They key step here is to link up our leverage multiple with our LTM EBITDA, which will tell us how much debt we can take out. This is the leverage part of an LBO Model. We’ll also drop in information on our financing fees, interest rate, and mandatory amortization.

Now that we have our financing fees, link those up to our uses.

Now, back in our sources & uses, let’s build out the framework for our sources. Drop in our Term Loan A and Term Loan B lines, and link them up to the debt financing assumptions.

Then, with our sponsor equity (our cash) acting as a plug, we’ll set that equal to our total uses and subtract the amount of money we already have from our Term Loan A and Term Loan B. This just tells us that we’ll bridge the gap from our debt to what we need by putting in as much of our own money as we have to.

Income Statement

Now we’ll project out the company’s financial performance through our investment. We already dropped in our build to LTM EBITDA, so we’ll just need to incorporate our assumptions over the projection period.

Grab all of our operating assumptions from the prompt and hook those up to the relevant items within our income statement. We’ll be going out to 2027, which is when the prompt says we’re exiting the investment.

After we get to 2027’s EBITDA, let’s build down through net income.

We won’t have our interest and financing fee amortization yet, but we can drop in our D&A and taxes based on the information in the prompt.

Cash Flow Statement

Our cash flow statement will tell us how much cash we’re generating from the business. This will inform our debt servicing — are we able to meet our mandatory amortization and do we have any excess cash we can use for optional debt repayment?

Change in Net Working Capital

First thing, let’s calculate our change in net working capital. This will be the increase (or decrease) in cash tied up running the business.

Our prompt gives us net working capital as a percent of revenue. We need to understand change in net working capital, so we’ll just need to calculate the period-over-period change in our balance.

Free Cash Flow

Here we can bridge from our income statement to our free cash flow. We’ll start with our net income, adjust for non-cash items in the income statement, and take into account our capital expenditures and change in networking capital (from the prior step). Note that we’ll need to add back our non-cash financing fee amortization, but we won’t know that yet (just link up to the income statement, flip the sign, and it will flow through when we add it to the income statement).

We’ll also drop in a space for our cash used in debt service, which we’ll fill in later.

Change in Cash

With this information, we can calculate our cash balance each year. Set the first year ending balance equal to our cash to the balance sheet from our sources and uses.

For all other periods, look at the prior period’s ending balance, set the current period’s beginning balance equal to that, and then calculate the ending balance by summing the beginning balance and the change in cash.

The change in cash will be our free cash flow prior to debt service plus our cash used in debt service. At this stage, prior to our buildout of the debt schedule, we don’t yet know our cash used in debt service. This means our cash balance, for now, will just be increasing by our free cash flow generated.

Debt Schedule

The debt schedule is one of the more unique components of an LBO model and modeling test. It will tell us how much debt we have, how much we have to pay back each year (mandatory amortization), and how much optional repayment we do on top of what we’re required to.

In the first step, just set up a simple table with each of our tranches of debt. It will be blank for now, but once we’re done we can use this as a summary. Include our cash balance to bridge to net debt.

Cash Available for Repayment

We need to know how much cash we have available for repayment. Link up to our cash flow statement’s free cash flow to determine our cash available for mandatory amortization.

Our cash available for optional prepayment will equal our cash available for mandatory amortization, less what we’ve used for mandatory amortization. We don’t know that yet, so leave it blank for now.

Revolver

We’ll start with our revolver, which will be a little bit different from our other debt tranches. This is because we can use it to bridge any cash gaps that we may have. Instead of only repaying debt, we might take out additional debt if we need to.

This particular model won’t require that, but we’ll have to build in that functionality either way.

Set it up similarly to our cash balance: Beginning balance, Optional borrowing / (repayment), and Ending balance.

Our ending balance will be zero because we didn’t use the revolver to help finance the transaction. Our beginning balance will equal the prior period’s ending balance.

We don’t yet know the optional borrowing or repayment, so we’ll come back to that after we figure out our mandatory amortization.

Revolver Commitment

The other nuance with our revolver is that we have to take into account our commitment. This is the maximum amount that our lender will let us draw. Link that up to our debt assumptions, and calculate our available capacity as the difference between our total commitment and the ending balance (what we’ve already drawn).

Available Capacity = MAX(0, Maximum Capacity – Revolver Ending Balance)

Term Loan A

Same thing here — let’s set up our Beginning balance, Mandatory amortization, Optional prepayment, and Ending balance.

Link up our Term Loan A amount from the sources & uses to the ending balance in our transaction’s pro forma year (the LTM period prior to the transaction, adjusted for the impact of the transaction).

Set the beginning balance equal to the prior period’s ending balance.

Calculate our mandatory amortization based on our assumption from the debt financing schedule back up in our transaction entry.

Mandatory Amortization = -MAX(0, MIN(Beginning balance, Initial principal amount * amortization %))

We compare the amortization vs. the beginning balance because we wouldn’t pay back more than we owe.

Cash Available for Optional Prepayment

Now that we’ve calculated our mandatory amortization, we can head back up to the top of our debt schedule and figure out how much cash we have left over for any optional prepayment.

We can do this now, prior to completing our Term Loan B, because we didn’t have any mandatory amortization to worry about in our Term Loan B.

Cash for Optional Prepayment = MAX(0, Cash for Mandatory Amort. – Cash Used for Mandatory Amort.)

Now let’s head back to our Term Loan A and complete the optional prepayment there.

Optional Prepayment = -MAX(0, MIN(Beginning Balance + Mandatory Amortization, Cash for Optional Prepayment))

Revolver Wrap-Up

Let’s circle back quickly to our revolver. We can finish that up now that we know how much cash we need for our mandatory amortization on the Term Loan A.

Optional Borrowing / (Repayment) = MIN(Revolver Avl. Capacity, -MIN(Beginning Balance, Cash Avl. for Mandatory Amort. + Mandatory Amort.))

This tells us that if our cash available for mandatory amortization doesn’t meet our mandatory amortization need, we can draw on the revolver up to the available capacity. If our cash available for mandatory amortization exceeds our need, we’ll use that amount to pay back any beginning balance that we might have on the revolver.

What will usually happen is that our cash available for mandatory amortization plus our mandatory amortization will be a positive number and our beginning balance will be zero. In that case, the formula will take that beginning balance zero as the minimum and there will be neither a draw nor a repayment.

Term Loan B

Getting down to the bottom of our debt schedule, let’s tackle our Term Loan B. No mandatory amortization here, so we only need our Beginning balance, Optional prepayment, and Ending balance.

Optional Prepayment = -MAX(0,MIN(Beginning Balance + Mandatory Amortization, Cash. Avl. for Optional Prepayment + Term Loan A Optional Prepayment))

For our optional prepayment, we’ll use the same formula as our Term Loan A, with one small change. We need to take into account the cash we used for our earlier optional prepayment, so just add that Term Loan A optional prepayment to the end of the formula.

We won’t make any optional prepayments on Term Loan B until all of our Term Loan A has been paid off. Until that happens, any leftover cash we have will be used to pay down the Term Loan A.

Debt Schedule Wrap-Up

Now that we’ve completed each tranche, head back up to the top of our debt schedule to finalize the summary table. Link this up to the ending balance of each respective tranche.

Then, head to our cash flow statement. On our blank debt service line, link up to all the cash we used in the debt schedule. This will be all mandatory and optional payments from each tranche (don’t forget the revolver).

Interest Schedule

With the debt schedule complete, now we can calculate the interest that we owe on that debt. This will be the final thing we need to knock out before we can progress to our exit and returns analysis.

Benchmark Rate

Each of our tranches of debt has a floating interest rate. This means that the rate will be calculated as the benchmark rate plus a spread above that benchmark.

For our benchmark rate, we’ll be using SOFR. Take the the provided SOFR forward curve from our prompt and drop that in at the top of our interest schedule.

We also have a 1% floor, which means that when we calculate our rate we need to take the maximum of each year’s SOFR and 1%. This will be our effective benchmark rate.

Interest Rates

Link to our spreads that we dropped into our debt financing assumptions at the top of the sheet. These are given in basis points, or 1/100 of a percent. Go ahead and divide them by 10,000 to turn them into a percent.

Then, add the effective benchmark rate to each spread to get our interest rate for each tranche of debt.

Interest Expense

With our rates complete, we can calculate the interest we owe each year on our debt.

Interest Expense = AVERAGE(Beginning Balance, Ending Balance) * Interest Rate

We need to use the average here so we can take into account the debt repaid over the course of the year. If we based our calculation only on the beginning balance, our payment would be artificially high; if only on the ending balance, then artificially low.

Circularity Switch

We also need to build in what’s called a circularity switch. The way an LBO model works, each component is dependent on other parts of the model. This can lead to circular reference issues in excel. For example, our debt repayment and interest expense is dependent on our cash flow, but our cash flow is dependent on our debt repayment and our interest expense.

To help fix this, a circularity switch will let us turn off our average balance interest expense calculation. In turn, Excel will be able to calculate properly, and we’ll be able to turn back on our switch to get back to normal.

Take our interest expense formula and tweak it:

Interest Expense = IF(Circularity Switch = 1, AVERAGE(Beginning Balance, Ending Balance) * Interest Rate, Beginning Balance)

If the switch is turned off (not set to 1), then interest expense will calculate based on the beginning balance of debt. You can also set the switch to 0, which just won’t calculate interest until you flip it back on.

Cash Interest Income

We also need to take into account interest income on our cash balance. Use the same formula as above, but set it to our cash beginning and ending balances.

The prompt tells us that we have a 1% interest income on cash. If you ever have a prompt that doesn’t give you an assumption here, 1% is a standard option to go with.

Interest Schedule Summary

Now that we have our interest expense and income for each item, let’s calculate our total net interest. Add everything up in a simple table at the bottom.

Make sure you have a diferent sign for your cash interest.

With this calculated, head back up to the income statement and link in our interest expense to our interest schedule.

Financing Fee Amortization

The last item in our interest schedule will be our financing fee amortization. This is the recognition of our financing fees over the term of the loan. If we had a balance sheet in this model, this amortization would reduce our financing fee line item each year.

Financing Fee Amortization = MAX(0, MIN(Total Fee Amount – All Prior Fee Amortization), Total Fee Amount / Debt Term))

This formula recognizes the fee in equal amounts each year. Note that we add in the minimum of the total fee amount and prior amortization to prevent a situation where we amortize an amount above the total fee that we had.

This would only happen if our hold period were longer than the debt term. In this model, our 7 year debt term will be longer than our five year hold period. That means you’re fine to ignore this formula quirk if you’re stretched for time, but you might as well build it in anyway.

Note that all of our tranches in this model have a 7 year term. This means we can lump our fee amortization all together into one line. However, if we had tranches with different terms, we’d have to do separate fee amortization calculations.

With this step complete, head back up to the income statement and link to our financing fee amortization.

Exit & Returns Analysis

After all that work, now it’s time to see what our returns look like from this investment. Is this a good deal or not? After all, that’s the whole point of doing an LBO model.

Investment Exit

The first thing we’ll do here is model out our sale of the business. Just like our entry, this will be our LTM EBITDA (in the final year of the projection) multiplied by our exit multiple.

This will give us our enterprise value. But, then we need to account for any debt we still have on the business, which will need to be repaid.

From our enterprise value, subtract our final year debt balance and add our final year cash balance. This gives us our equity value, or the money that we get back from the investment.

Returns Analysis

After we calculate our equity value at exit, we need to understand how that compares to the money we had to put into the deal initially (our sponsor equity from the sources & uses).

For this comparison, we’ll look at our Multiple of Invested Capital (MOIC) and Internal Rate of Return (IRR).

Our MOIC is easy. Divide our exit equity value by our sponsor equity.

The IRR metric will take into account the timing of the investment. To calculate this, drop in dates for the deal. We’ll have our transaction entry as the first date, and our transaction exit as the last date.

Then, apply our equity inflows and outflows — our sponsor equity flowed out of our pocket when we entered the deal, and the exit equity was returned to us when we exited the investment.

Apply the XIRR function to those values and dates to calculate our IRR.

That’s pretty much it – the higher the MOIC and IRR, the better the investment. There are some trade-offs between the two metrics based on timing of the deal and hold period, but that’s a topic for another day.

LBO Model Sensitivities

The final thing we can build in here is a couple sensitivity analyses to understand how our returns fluctuate based on our model assumptions. We’ll sensitize our entry and exit multiples, looking at impact to both MOIC and IRR.

Drop in a range of exit multiples on one axis and a range of entry multiples on another. Link the top left cell to either your MOIC or IRR. Set the formatting of that cell to “;;;”.

Highlight the entire table, including the multiples. Hit ALT + D + T to turn this selection into a data table. Link up to your model input assumptions, matching up the column and row to their respective inputs.

Copy this table and complete for your remaining returns metric (MOIC or IRR).

You can also fairly easily show both your MOIC and IRR in the same table, but to keep this as simple as possible we’ll ignore that option for now.

Double check your tables. At the entry and exit multiples that match your transaction, do the returns shown in the table match what you got to in your model? If not, your table has an error in there somewhere.

LBO Model & Modeling Test Conclusion

Not too complicated once you get a few practice runs under your belt. Do your first couple tests using a template, but then try to build your LBO from a blank sheet. This is the best way to truly learn, and will prevent you getting stuck if you have a blank sheet LBO modeling test.

There are a bunch of other bells and whistles you can toss in here to make the model more complex, but this simple model touches everything you need for your core returns drivers. Think of this as your 80/20 model.