Wall Street Prep

LBO Modeling Test: Real Example

A Complete Step-By-Step Breakdown of a Real LBO Modeling Test with Excel Template

Learn LBO Modeling Online

Designed for investment banking and private equity professionals who want to take their LBO modeling skills to the next level.

Enroll Today

What is an LBO Modeling Test?

The LBO Modeling Test refers to a common interview exercise during the later stages of the private equity recruiting process, which usually works as follows: The interviewee will usually receive a "prompt" – a description containing a situational overview and certain financial data for a hypothetical company contemplating an LBO.

LBO Modeling Test Format

  • Work in Excel: Unlike the paper LBO, which is a pen-and-paper exercise given in earlier stages of the PE recruiting process, in an LBO Modeling Test candidates are given access to Excel and expected to construct an operating and cash flow forecast, financing sources & uses and ultimately determine the implied investment returns and other key metrics based on the information provided in the prompt.
  • Time Limit: The various LBO Excel modeling tests you encounter throughout the recruitment process will most commonly be either 30 minutes, 1 hour or 3 hours depending on the firm and how near you are to the final stage before offers are made.
  • Prompt Format: In some cases, you will be provided a brief prompt consisting of a few paragraphs on a fictitious scenario and be asked to build a quick model from scratch – whereas in others, you may be given the confidential information memorandum (CIM) of a real acquisition opportunity to put together an investment memo alongside a LBO model to support your thesis. For the latter, the prompt is usually left vague intentionally and it will be asked in the form of a "share your thoughts" open-ended context.

Basic vs Standard LBO Modeling Test

This LBO Modeling Test is a good place to start to make sure you've got the mechanics.  But for investment banking analysts interviewing for PE, expect a more challenging LBO Modeling test like this one

LBO Modeling Test Grading Criteria

Every firm has a slightly different grading rubric for the LBO Modeling Test but at its core, it boils down to two criteria:

  1. Accuracy: How well do you understand the underlying mechanics of a LBO model?
  2. Speed: How quickly can you complete the task without a loss in accuracy?

For more complex case studies (where you will be given 3+ hours), your ability to interpret the output of the model and make an informed investment recommendation will be just as important as your model flowing correctly with the right linkages.

In-Person LBO Modeling Test Spectrum: In-person modeling tests take a few different forms and the test administered varies by PE fund:

LBO Modeling Test Example

Let's get started! Below is an illustrative prompt on a hypothetical buyout:

LBO Model Test Instructions

A private equity firm is considering the leveraged buyout of JoeCo, a privately-owned coffee company. In the last twelve months (“LTM”), JoeCo generated $1bn in revenue and $100mm in EBITDA. If acquired, the PE firm believes JoeCo's revenue can continue to grow 10% YoY while its EBITDA margin remains constant.

To fund this transaction, the PE firm was able to obtain 4.0x EBITDA in Term Loan B (“TLB”) financing – which will come with a seven-year maturity, 5% mandatory amortization, and priced at LIBOR + 400 with a 2% floor. Packaged alongside the TLB is a $50mm revolving credit facility (“revolver”) priced at LIBOR + 400 with an unused commitment fee of 0.25%. For the last debt instrument used, the PE firm raised 2.0x in Senior Notes that carries a seven-year maturity and an 8.5% coupon rate. The financing fees were 2% for each tranche while the total transaction fees incurred were $10mm.

On JoeCo’s balance sheet, there is $200mm of existing debt and $25mm in cash, of which $20mm is considered excess cash. The business will be delivered to the buyer on a "cash-free, debt-free basis", which means the seller is responsible for extinguishing the debt and keeps all the excess cash.  The remaining $5mm in cash will come over in the sale, as this is cash that the parties determined is required to keep the business operating smoothly.

Assume for each year that JoeCo’s depreciation & amortization expense ("D&A") will be 2% of revenue, capital expenditures ("Capex") requirement will be 2% of revenue, the change in net working capital ("NWC") will be 1% of revenue, and the tax rate will be 35%.

If the PE firm were to purchase JoeCo at 10.0x LTM EV/EBITDA on 12/31/2020 and then exit at the same LTM multiple after a five-year time horizon, what would the implied IRR and cash-on-cash return of the investment be?

Before We Begin ... Download the Excel Template

Use the form below to download the Excel file used to complete the modeling test. Keep in mind that while most firms will provide the financials in an Excel format that you could use as a "guiding" template, you should still be comfortable with creating a model starting from scratch:

;

Step 1. Model Assumptions

Entry Valuation

The first step of the LBO modeling test is to determine the entry valuation of JoeCo on the date of initial purchase.

By multiplying JoeCo’s $100mm LTM EBITDA by the entry multiple of 10.0x, we know the enterprise value at purchase was $1bn.

Since this deal is structured as a "cash-free debt-free" transaction (CFDF), the sponsor is not assuming any JoeCo debt or getting any of JoeCo's excess cash. This has an important implication for the model: From the sponsor's perspective there is no net debt, and thus the equity purchase price equals the enterprise value.

The private equity firm is essentially saying: “JoeCo can have the excess cash sitting on its balance sheet, but on the basis that JoeCo will pay off its outstanding debt in return.”

Most PE deals are structured as cash-free debt-free. The notable exceptions are go-private transactions where sponsor acquires each share for a defined offer price per share and thus acquires all assets and assumes all liabilities.

Transaction Assumptions

Next, we will list out the transaction assumptions provided. The transaction fees were $10mm – this is the amount paid to investment banks for their M&A advisory work, as well as to lawyers, accountants, and consultants that helped on the deal. These advisory fees are treated as a one-time expense, as opposed to being capitalized.

The 2% financing fee refers to the costs incurred while raising debt capital to fund this transaction. This financing fee will be based on the total magnitude of debt used and will be amortized over the tenor (term) of the debt – which is seven years in this scenario.

The minimum cash balance required post-closing of the transaction (i.e. "Cash to B/S") was stated as $5mm. This means that JoeCo needs $5mm in cash-on-hand to continue operating and meet its short-term working capital obligations.

Debt Assumptions

With the entry valuation and transaction assumptions filled out, we can now list out the debt assumptions related to each debt tranche, such as the turns of EBITDA ("x EBITDA"), pricing terms, and amortization requirements.

The amount of debt that a lender has provided is expressed as a multiple of EBITDA (also called a “turn”). For example, we can see $400mm was raised in Term Loan B as the amount was 4.0x EBITDA. In total, the initial leverage multiple used in this transaction was 6.0x – since 4.0x was raised from TLB and 2.0x in Senior Notes.

Moving onto the columns on the right, the "Rate" and "Floor" are used to calculate the interest rate of each debt tranche.

The two senior secured debt tranches, the Revolver and Term Loan B are priced off LIBOR + a spread (i.e. priced at a "floating rate"). This means that the interest rate paid on these debt instruments will fluctuate based on LIBOR ("London Interbank Offered Rate"), the global standard benchmark used to set lending rates.

The general convention is to state the pricing of debt in terms of basis points ("bps") rather than by "%". The "+ 400" just means 400 basis points, or 4%. Therefore, the interest rate pricing on the Revolver and TLB will be LIBOR + 4%.

You may have noticed the Term Loan B tranche has a 2% "Floor", this is the minimum amount that would need to be added to the spread. LIBOR will often fall below the floor rate during periods of low interest rates such as the past decade and this feature is meant to ensure that a minimum yield is received by the lender.

For example, if LIBOR was at 1.5% and the floor was 2.0%, the interest rate on this Term Loan B would be 2.0% + 4.0% = 6.0%. But if LIBOR was at 2.5%, the interest rate on the TLB would be 2.5% + 4% = 6.5%. As you can see, the interest rate cannot fall below 6% because of the floor.

Primer on Debt Financing

The third tranche of debt used, the Senior Notes, is priced at 8.5% (i.e. priced at a "fixed rate"). This type of pricing is simpler because regardless of whether LIBOR goes up or down, the interest rate will remain unchanged at 8.5%.

In the final column, we can calculate the financing fees based on the amount of debt raised. Since $400mm was raised in Term Loan B and $200mm was raised in Senior Notes, we can multiply each by the 2% financing fee assumption and sum them up to arrive at $12mm in financing fees.

Step 1 Formulas Used

Purchase Enterprise Value = LTM EBITDA ×  Entry Multiple

Debt Amount ("$ Amount") = Debt EBITDA Turns × LTM EBITDA

Financing Fees ("$ Fee") = Debt Amount × % Fee

Step 2. Sources & Uses Table

In the next step, we will build out the Sources & Uses schedule, which lays out how much it will cost in total to acquire JoeCo and where the required funding will come from.

It is recommended to start on the "Uses" side and then complete the "Sources" side afterward since you need to figure out how much something costs before thinking about how you will come up with the funds to pay for it.

Uses Side

To start, we have already calculated the “Purchase Enterprise Value” in the previous step and can directly link to it. This $1bn is the total amount being offered by the private equity firm to acquire the equity of JoeCo.

We must recall that JoeCo's cash balance cannot dip below $5mm post-transaction. As a result, the "Cash to B/S" in effect will increase the total funding required – hence, it will be on the "Uses" side of the table.

To finish the Uses section, the $10mm in transaction fees and $12mm in financing fees were already calculated earlier and can be linked to the relevant cells.

In total, $1,027mm in capital will be required to complete this proposed acquisition of JoeCo, and the "Sources" side will now tell us how the PE firm intends to finance the acquisition.

Sources Side

We will now list out how the PE firm came up with the necessary funds to meet the cost of purchasing JoeCo.

Since there was no mention of the revolving credit line being drawn, we can assume that none was used to help fund the purchase. The revolver is generally undrawn at close but could be drawn from if needed.

Think of the revolver as a "corporate credit card" meant for use during emergencies – this line of credit is extended to borrowers by lenders to make their financing packages more attractive (i.e. for the Term Loan B in this scenario) and to provide JoeCo a "cushion" for unexpected liquidity shortages.

A Term Loan B (“TLB”) is provided by an institutional lender and is generally a senior, 1st lien loan with a 5 to 7 year maturity and low amortization requirements. The amount of TLB raised was calculated earlier by multiplying the 4.0x TLB leverage multiple by the LTM EBITDA of $100mm – thus, $400mm in TLB was raised to fund this purchase.

The third debt tranche raised were Senior Notes at a leverage multiple of 2.0x EBITDA, so $200mm was raised. Senior Notes are junior to secured bank debt (e.g. Revolver, Term Loans), and provide a higher yield to compensate the lender for undertaking the additional risk of holding a debt instrument lower in the capital structure.

Now that we have determined how much the firm needs to pay and the amount of debt funding, the “Sponsor Equity” is the plug for the remaining funds needed.

If we add up all the funding sources (i.e. the $600mm raised in debt) and then deduct it from the $1,027mm in “Total Uses”, we see $427mm was the initial equity contribution by the sponsor.

Step 2 Formulas Used

Sponsor Equity = Total Uses – (Revolver + Term Loan B + Senior Notes Amounts)

Total Sources = Revolver + Term Loan B + Senior Notes + Sponsor Equity

Step 3. Free Cash Flow Projection

Revenue and EBITDA

Now the Sources & Uses table has been completed and the transaction structure has been determined, the free cash flows ("FCFs") of JoeCo can be projected.

To begin filling out the forecast, we start with Revenue and EBITDA since most of the operating assumptions provided are driven as a certain percentage of revenue. As a general modeling best practice, it is recommended to list out all the drivers in the same section near the bottom.

The prompt stated the year-over-year ("YoY") revenue growth will be 10% throughout the holding period with the EBITDA margins held constant from the LTM performance. While the EBITDA margin was not explicitly stated in the prompt, we can divide the LTM EBITDA of $100mm by the $1bn in LTM revenue to get a 10% EBITDA margin.

Once you have inputted the revenue growth rate and EBITDA margin assumptions, we can project the amounts for the forecast period based on the formulas below.

Operating Assumptions

As stated in the prompt, D&A will be 2% of revenue, Capex requirements are 2% of revenue, the change in NWC will be 1% of revenue, and the tax rate will be 35%.

All these assumptions will remain unchanged throughout the forecast period; therefore, we can "straight-line" them (i.e. reference the current cell to the one on the left).

Net Income

Recall the formula for free cash flow before any revolver drawdown / (paydown) begins with net income. Therefore, we need to work our way down from EBITDA to net income ("the bottom line"), meaning the subsequent step is to subtract D&A from EBITDA to calculate EBIT.

We are now at Operating Income (EBIT) and will subtract "Interest" and the "Amortization of Financing Fees".

The interest expense line item will remain blank for now as the debt schedule has not yet been completed – we will return to this later.

For the financing fees amortization, we can compute this by dividing the total financing fee ($12mm) by the tenor of the debt, 7 years – doing so will leave us with ~$2mm each year.

The only expenses remaining from Pre-Tax Income (EBT) are the taxes paid to the government. This tax expense will be based on JoeCo's taxable income, therefore we will multiply the 35% tax rate by EBT.

Once we have the amount in taxes due each year, we will subtract that amount from EBT to arrive at net income.

Free Cash Flow (Pre-Revolver)

The FCFs generated are central to a LBO as they determine the amount of cash available for debt amortization and the servicing of the due interest expense payments each year.

To calculate the FCF, we will first add back D&A and the Amortization of Financing Fees to Net Income since they are both non-cash expenses. We calculated them both earlier and can just link to them but with the signs flipped since we are adding them back (i.e. more cash than net income showed).

Then, we subtract out Capex and the change in NWC. An increase in Capex and NWC are both outflows of cash and decrease the FCF of JoeCo, thus make sure to insert a negative sign at the front of the formula to reflect this.

In the final step before arriving at FCF, we will deduct the mandatory debt amortization associated with the Term Loan B. We will keep this part blank and return it once the debt schedule has been finalized.

Step 3 Formulas Used

Total Uses = Purchase Enterprise Value + Cash to B/S + Transaction Fees + Financing Fees

Revenue = Prior Revenue × (1 + Revenue Growth %)

EBITDA = Revenue × EBITDA Margin %

Free Cash Flow (Pre-Revolver) = Net Income + Depreciation & Amortization + Amortization of

Financing Fees – Capex – Change in Net Working Capital – Mandatory Amortization

D&A = D&A % of Revenue × Revenue

EBIT = EBITDA – D&A

Amortization of Financing Fees = Financing Fees Amount ÷ Financing Fees Amortization Period

EBT (aka Pre-Tax Income) = EBIT – Interest – Amortization of Financing Fees

Taxes = Tax Rate % × EBT

Net Income = EBT – Taxes

Capex = Capex % of Revenue × Revenue

Δ in NWC = (Δ in NWC % of Revenue) × Revenue

Free Cash Flow (Pre-Revolver) = Net Income + D&A + Amortization of Financing Fees – Capex – Δ in NWC –  Mandatory Debt Amortization

Note: If a line item has "Less" at the front, confirm that it is shown as a negative outflow of cash, and vice versa if it has "Plus" in front. Assuming you followed the sign conventions as recommended, you can just sum up net income with the five other line items to arrive at the pre-revolver FCF.

Step 4. Debt Schedule

This is the trickiest part of the LBO Modeling Test. In the previous step, we calculated the free cash flow available before any revolver drawdown / (paydown). The missing line items that we had skipped over earlier will be derived from the debt schedule to complete those FCF projections.

To take a step back, the purpose of creating this debt schedule is to keep track of JoeCo's mandatory payments to its lenders and assess its revolver needs, as well as calculate the interest due from each debt tranche.

The borrower, JoeCo, is legally required to paydown debt tranches in a specific order (i.e. waterfall logic) and must abide by this lender agreement. Based on this contractual obligation, the revolver will be paid off first, followed by the Term Loan B, and then the Senior Notes.

The Revolver and TLBs are the highest in the capital structure and have the highest priority in the case of a bankruptcy, hence carry a lower interest rate and represent "cheaper" sources of financing.

For each debt tranche, we will utilize roll-forward calculations, which refer to a forecasting approach that connects the current period forecast to the prior period after accounting for the line items that increase or decrease the ending balance.

Roll-Forward Approach ("BASE" or "Cork-Screw")

Roll-forwards refers to a forecasting approach that connects the current period forecast to the prior period:

This approach is very useful in adding transparency to how schedules are constructed. Maintaining strict adherence to the roll-forward approach improves a user's ability to audit the model and reduces the likelihood of linking errors.

Revolving Credit Facility ("Revolver")

As mentioned in the beginning, the revolver functions similarly to a corporate credit card and JoeCo will drawdown from it when it is short on cash and will pay off the balance once it has cash in excess.

If there is a shortage of cash, the revolver balance will rise – this balance will be paid down once there is a surplus of cash

The revolver sits at the very top of the debt waterfall and has the highest claim on the JoeCo's assets if the company were to be liquidated.

To start, we will create three line items:

  1. Total Revolver Capacity
    The "Total Revolver Capacity" refers to the maximum amount that can be drawn from the revolver, and it comes out to $50mm in this scenario.
  2. Beginning Available Revolver Capacity
    The "Beginning Available Revolver Capacity" is the amount that can be borrowed in the current period after deducting the amount already drawn in previous periods. This line item is calculated as the total revolver capacity minus the beginning of period balance.
  3. Ending Available Revolver Capacity
    The "Ending Available Revolver Capacity" is the amount of the beginning available revolver capacity minus the amount drawn from in the current period.

For example, if JoeCo has drawn $10mm to date, the beginning available revolver capacity for the current period is $40mm.

To continue building out this revolver roll-forward, we link the beginning of period balance in 2021 to the amount of revolver used to fund the transaction in the Sources & Uses table. In this case, the revolver was left undrawn from and the beginning balance is thereby zero.

Then, the line that comes after will be "Revolver Drawdown / (Paydown)".

The formula for the "Revolver Drawdown / (Paydown)" in Excel is shown below:

The "Revolver Drawdown" comes into play when the FCF of JoeCo has turned negative and the revolver will be drawn from.

Again, JoeCo can borrow at most up to the Available Revolver Capacity. This is the purpose of the 1st "MIN" function, it ensures no more than $50mm could be borrowed. It is entered as a positive because when JoeCo draws from the revolver, it is an inflow of cash.

The 2nd "MIN" function will return the lesser value between the "Beginning Balance" and the "Free Cash Flow (Pre-Revolver)".

Take notice of the negative sign in front – in the case that the "Beginning Balance" is the smaller value of the two, the output will be negative and the existing revolver balance will be paid down. The "Beginning Balance" figure cannot turn negative as that would imply that JoeCo paid down more of the revolver balance than it had borrowed (i.e. the lowest it can be is zero).

On the other hand, if the "Free Cash Flow (Pre-Revolver)" is the lesser value of the two, the revolver will be drawn down from (as the two negatives will make a positive).

For instance, let's say that JoeCo's FCF has turned negative $5mm in 2021, the 2nd "MIN" function will output the negative free cash flow amount, and the negative sign placed in front will make the amount positive – which makes sense since this is a drawdown.

This is how the revolver balance would change if JoeCo's pre-revolver FCF had turned negative by $5mm in 2021:

As you can see, the drawdown in 2021 would be $5mm. The ending balance of the revolver has increased to $5mm. In the next period, since JoeCo has sufficient pre-revolver FCF, it will paydown the outstanding revolver balance. The ending balance in the 2nd period has thus returned to zero.

To calculate the interest expense associated with the revolver, we first need to get the interest rate. The interest rate is calculated as LIBOR plus the spread, "+ 400". Since it was stated in terms of basis points, we divide 400 by 10,000 to get .04, or 4%.

Although the Revolver has no floor, it is a good habit to put the LIBOR rate in a "MAX" function with the floor, which is 0.0% in this case. The "MAX" function will output the larger value of the two, which is LIBOR in all the forecasted years. For example, the interest rate in the 2021 is 1.5% + 4% = 5.5%.

Note: If LIBOR were stated in basis points, the top line would look like "150, 170, 190, 210, and 230". The formula would change in that LIBOR (Cell "F$73" in this case) will be divided by 10,000.

Now that we have calculated the interest rate, we can multiply it by the average of the beginning and ending revolver balance. If the revolver remains undrawn throughout the entire duration of the holding period, the interest paid will be zero.

A circularity will be introduced into our model once we link the interest expense back into the FCF forecast. Therefore, we have added a circularity toggle in case the model breaks. Basically, what the formula above is saying is that if the toggle is switched to "1", then the average of the beginning and ending balance will be taken. However, if the toggle is switched to "0", just the ending balance will be used to avoid creating a circularity.

Finally, the revolver comes with an unused commitment fee, which is 0.25% in this scenario. To calculate this annual commitment fee, we multiply this 0.25% fee by the average of the beginning and ending available revolver capacity, since this represents the revolver amount not being used.

Term Loan B ("TLB")

Moving onto the next tranche in the waterfall, the Term Loan B will be forecasted in a similar roll-forward but this schedule will be simpler given our model assumptions.

The only factor that impacts the ending TLB balance is the scheduled principal amortization of 5%. For each year, this will be calculated as the total amount raised (i.e. the principal) multiplied by the 5% mandatory amortization.

While it is less relevant to this scenario, we have wrapped a "-MIN" function to output the lesser number between the (Principal * Mandatory Amortization %) and the Beginning TLB Balance. This prevents the principal amount paid down from exceeding the balance remaining.

For example, if the required amortization was 20% per year and the holding period was 6 years, without this function in place – JoeCo would still be paying the mandatory amortization in year 6 despite the principal being fully paid off (i.e. the beginning balance in Year 6 would be zero, so the function will output the zero rather than the mandatory amortization amount)

Keep in mind, the amortization amount is based on the principal amount regardless of how much of the debt has been paid down to date. In other words, the mandatory amortization of this TLB will be $20mm each year until the remaining principal will be due in one final payment at the end of its maturity.

The interest rate calculation for TLB is shown below:

The formula is the same as the revolver, but this time – there actually is a LIBOR floor (2%). Since LIBOR is 1.5% in 2021, the "MAX" function will output the larger number between the floor and LIBOR – which is the 2% floor for 2021.

The 2nd part is the spread of 400 basis points divided by 10,000 to arrive at 0.04, or 4.0%.

Given how LIBOR is 1.5% in 2021, the TLB interest rate will be calculated as 2.0% + 4.0% = 6.0%.

Then, to calculate the interest expense – we take the TLB interest rate and multiply it by the average of the beginning and ending TLB balance.

Notice how as the principal is paid down, the interest expense decreases. Contrast this to the mandatory amortization, in which the amount paid remains constant regardless of the principal paydown.

The interest expense is approximately ~$23mm in 2021 and falls to ~$20mm by 2025.

However, this dynamic is less apparent in our model given the mandatory amortization is only 5.0% and we are assuming no cash sweeps (i.e. prepayment using excess FCF is not allowed).

Senior Notes

Relative to equity and other riskier notes/bonds that the PE firm could have potentially used as funding sources, Senior Notes are higher up in the capital structure and considered to be a “safe” investment from the perspective of most lenders. However, Senior Notes are still below bank debt (e.g. Revolver, TLs) and usually unsecured despite the name.

One characteristic of these Senior Notes is that there will be no required principal amortization, meaning the principal will not be paid until maturity.

We saw how with the TLB, the interest expense (i.e. the proceeds to the lender) decreases as the principal is gradually paid down. So, the lender of the Senior Notes chose to neither require any mandatory amortization nor allow prepayment.

As you can see below, the interest expense is $17mm each year, and the lender receives an 8.5% yield on the $200mm outstanding balance for the entire tenor.

Forecast Completion

Now that the debt schedule is done, we can return to the parts of the FCF forecast that we skipped over and left blank.

First, the interest expense line item will be calculated as the sum of all of the interest payments from each debt tranche, as well as the unused commitment fee on the revolver.

Then to complete the forecast, we will link the mandatory amortization amount due from the TLB directly to the "Less: Mandatory Amortization" line item on the forecast, right above the "Free Cash Flow (Pre-Revolver)".

For more complex (and realistic) transactions where various debt tranches require amortization, you would take the sum of all of the amortization payments due that year and then link it back to the forecast.

Our free cash flow projection model is now complete with those two final linkages made.

Step 4 Formulas Used

Available Revolver Capacity = Total Revolver Capacity – Beginning Balance

Revolver Drawdown / (Paydown): "=MIN (Available Revolver Capacity, –MIN (Beginning Revolver Balance, Free Cash Flow Pre-Revolver)"

Revolver Interest Rate: "= MAX (LIBOR, Floor) + Spread"

Revolver Interest Expense: "IF (Circularity Toggle = 1, AVERAGE (Beginning, Ending Revolver Balance), Ending Revolver Balance) × Revolver Interest Rate

Revolver Unused Commitment Fee: "IF (Circularity Toggle = 1, AVERAGE (Beginning, Ending Available Revolver Capacity), Ending Available Revolver Capacity) × Unused Commitment Fee %

Term Loan B Mandatory Amortization = TLB Raised × TLB Mandatory Amortization %

Term Loan B Interest Rate: "= MAX (LIBOR, Floor) + Spread"

Term Loan B Interest Expense: "IF (Circularity Toggle = 1, AVERAGE (Beginning, Ending TLB Balance), Ending TLB Balance) × TLB Interest Rate

Senior Notes Interest Expense = "IF (Circularity Toggle = 1, AVERAGE (Beginning, Ending Senior Notes), Ending Senior Notes Balance) × Senior Notes Interest Rate

Interest = Revolver Interest Expense + Revolver Unused Commitment Fee + TLB Interest Expense + Senior Notes Interest Expense

Note: A common feature in LBO models is a "cash sweep" (i.e. optional repayments using excess FCFs), but this was excluded in our basic model. For this reason, the "Free Cash Flow (Post-Revolver)" will be equal to the "Net Change in Cash Flow" since there are no more uses of cash.

Step 5. Returns Calculation

Exit Valuation

Now that we have projected the financials of JoeCo and the net debt balance for the five-year holding period, we have the necessary inputs to calculate the implied exit value for each year.

The first input will be the "Exit Multiple Assumption", which was stated as being the same as the entry multiple, 10.0x.

Next, create a new line item for "Exit EBITDA" which simply links to the EBITDA of the given year. We will grab this figure from the FCF forecast.

We can now calculate the "Exit Enterprise Value" by multiplying the Exit EBITDA by the Exit Multiple Assumption.

Just as we did in the 1st step for the entry valuation, we will then deduct the net debt from the enterprise value to arrive at the "Exit Equity Value".

The total debt amount will be the sum of all of the ending balances in the debt schedule, while the cash balance will be pulled from the cash roll-forward in the FCF forecast.

Return Metrics

In the final step, we will calculate the two return metrics we were instructed to by the prompt:

  1. The Internal Rate of Return (IRR)
  2. Cash-on-Cash Return (aka MOIC)

The Internal Rate of Return (IRR)

To determine the IRR of this investment in JoeCo, you first need to gather the magnitude of the cash (outflows) / inflows and the coinciding dates of each.The initial equity contribution by the financial sponsor needs to be inputted as a negative since the investment is an outflow of cash. In contrast, all cash inflows are inputted as positives. But in this case, the only inflow will be the proceeds received from the exit of JoeCo.

Once the "Cash (Outflows) / Inflows" section has been completed, enter "=XIRR" and drag the selection box across the entire range of cash (outflows) / inflows in the relevant year, insert a comma, and then do the same across the row of dates. The dates must be formatted correctly for this to work properly (e.g. “12/31/2025”, rather than “2025”).

Cash-on-Cash Return (aka MOIC)

Otherwise referred to as the multiple-on-invested-capital (MOIC), the cash-on-cash return is calculated as the total inflows divided by the total outflows from the perspective of the PE firm.Since our model is less complex with no other proceeds (e.g. dividend recaps, consulting fees), the MOIC is the exit proceed divided by the $427 initial equity investment.

To perform this in Excel, use the "SUM" function to add up all the inflows received during the holding period (shown in green font), and then divide by the initial cash outflow in Year 0 (shown in red font) with a negative sign in front.

Step 5 Formulas Used

Exit Enterprise Value = Exit Multiple × LTM EBITDA

Debt = Revolver Ending Balance + Term Loan B Ending Balance + Senior Notes Ending Balance

IRR: "= XIRR (Range of Cash Flows, Range of Timing)"

MOIC: "=SUM (Range of Inflows) / – Initial Outflow"

Assuming an exit in Year 5, the private equity firm was able to 2.8x from its initial equity investment in JoeCo and achieve an IRR of 22.5% throughout the holding period.

This concludes our basic LBO Modeling Test tutorial – we hope you found the explanations intuitive and stay tuned for the next article in this LBO modeling series!

LBO Modeling on the Job

On the job, you will build two types of LBO models most frequently:

  1. Mini-Models (i.e. "Short Form" LBO models)
    These are usually similar to the level of complexity you'll encounter in the LBO Modeling Test we just covered (maybe a little more complex). These are used at the pre-letter of intent (LOI) stage of a deal, when the PE firm has received a Teaser, signed an NDA and received a confidential information memorandum ("CIM")  from the investment banker which contains high level financial data.  At this point the PE firm is just trying to figure out whether the deal could make sense from a returns perspective at a high level - they haven't dug into the due diligence yet.
  2. Fully Integrated LBO Model with Operating Model
    At later stages of the deal process, once the PE firm gets access to the data room and more detailed financial data, LBO models become far more advanced and contain fully integrated financial statements, as well as a variety of accounting, tax and transaction adjustments not usually tested in the recruiting process.  These are the models private equity firms hire us to train their Associates on. To learn how to build these more advanced models, enroll in Wall Street Prep's LBO Modeling course.

Full LBO Online Training

For the fully integrated LBO Model with Operating Model, we developed a step-by-step video training program containing the same instructional material trusted by the leading private equity firms to train their analysts and associates. We are proud that this program is now used at 4 of the top 5 largest private equity firms to train their own newly minted PE Associates.

To enroll in the course, click here.

Upon purchase, you will gain immediate access to the most comprehensive course on LBO Modeling available. 

Master LBO Modeling Our Advanced LBO Modeling course will teach you how to build a comprehensive LBO model and give you the confidence to ace the finance interview.
Learn More
Comments
avatar
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.