LBO Model Test Example: Illustrative Prompt
Let’s get started! An illustrative prompt on a hypothetical leveraged buyout (LBO) can be found below.
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?
LBO Model Test – Excel Template
Use the form below to download the Excel file used to complete the modeling test.
However, 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 the 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.
“Cash-Free Debt-Free” Transaction
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.
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 (CFDF). The notable exceptions are go-private transactions where the 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.
- Transaction Fees: 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 who helped on the deal. These advisory fees are treated as a one-time expense, as opposed to being capitalized.
- Financing Fees: The 2% deferred 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.
- Cash to B/S: The minimum cash balance required post-closing of the transaction (i.e. “Cash to B/S”) was stated as $5mm, which means 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 instance, we can see that $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“), meaning that the interest rate paid on these debt instruments fluctuates 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%.
The Term Loan B tranche has a 2% “Floor”, which reflects the minimum amount required to be added to the spread. LIBOR will often fall below the floor rate during periods of low-interest rates, so 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.
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.
Uses Side
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.
- Purchase Enterprise Value: To start, we have already calculated the “Purchase Enterprise Value” in the previous step and can directly link to it. The $1bn is the total amount being offered by the private equity firm to acquire the equity of JoeCo.
- Cash to B/S: 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.
- Transaction Fees and Financing Fees: 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.
Therefore, $1,027mm in total capital will be required to complete this proposed acquisition of JoeCo, and the “Sources” side will now illustrate how the PE firm intends to finance the acquisition.
Sources Side
We will now outline how the PE firm came up with the necessary funds to meet the cost of purchasing JoeCo.
- Revolver: 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.
- Term Loan B (“TLB”): Next, a term loan B 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.
- Senior Notes: The third debt tranche raised was 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
Thus far, the Sources & Uses table has been completed and the transaction structure has been determined, meaning that the free cash flows (“FCFs”) of JoeCo can be projected.
To start the forecast, we begin with Revenue and EBITDA since most of the operating assumptions provided are driven by a certain percentage of revenue.
As a general modeling best practice, it is recommended to place all the drivers (i.e. operating assumptions”) grouped together 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
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 an 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).
Next, 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.
For the time being, we’ll 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 + Mandatory Amortization
- 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
If a line item has “Less” at the front, confirm it is reflected 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
The debt schedule is arguably 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 pay down 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 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 draw down 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 JoeCo’s assets if the company were to be liquidated.
To start, we will create three line items:
- 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.
- 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.
- 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, 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 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 pay down 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 2021 is 1.5% + 4% = 5.5%.
Note that 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.
Once we link the interest expense back into the FCF forecast, a Circularity will be introduced into our model. Therefore, we have added a circularity toggle in case the model breaks.
Basically, what the formula above is saying is:
- If the toggle is switched to “1”, then the average of the beginning and ending balance will be taken
- If the toggle is switched to “0”, then a zero will be output, which removes all the cells populated with “#VALUE” (and can be toggled back to use the average)
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)
The amortization amount is based on the original debt principal 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 is a LIBOR floor of 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 mandatory amortization, in which the amount paid remains constant regardless of the principal paydown.
The approximate interest expense is ~$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
The debt schedule is now done, so we can return to the parts of the FCF forecast that we skipped over and left blank.
- Interest Expense: 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.
- Mandatory Amortization: In order 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), 0) Ă— Revolver Interest Rate
- Revolver Unused Commitment Fee: “IF (Circularity Toggle = 1, AVERAGE (Beginning, Ending Available Revolver Capacity), 0) Ă— 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), 0) Ă— TLB Interest Rate
- Senior Notes Interest Expense = “IF (Circularity Toggle = 1, AVERAGE (Beginning, Ending Senior Notes), 0) Ă— Senior Notes Interest Rate
- Interest = Revolver Interest Expense + Revolver Unused Commitment Fee + TLB Interest Expense + Senior Notes Interest Expense
As a side note, one 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.
- Exit Multiple Assumption: The first input will be the “Exit Multiple Assumption”, which was stated as being the same as the entry multiple, 10.0x.
- Exit EBITDA: In the next step, we’ll 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.
- Exit Enterprise Value: We can now calculate the “Exit Enterprise Value” by multiplying the Exit EBITDA by the Exit Multiple Assumption.
- Exit Equity Value: Similar to how we did in the first 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 is 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 (and Net Debt = Total Debt – Cash)
Internal Rate of Return (IRR)
In the final step, we will calculate the two return metrics we were instructed to by the prompt:
- Internal Rate of Return (IRR)
- Cash-on-Cash Return (aka MOIC)
Starting with 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”).
Multiple on Invested Capital (MOIC)
The multiple-on-invested-capital (MOIC), or “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 (green font), and then divide by the initial cash outflow in Year 0 (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”
Basic LBO Modeling Test: Interview Answers
In closing, if we assume an exit in Year 5, the private equity firm was able to fetch a 2.8x MOIC on its initial equity investment in JoeCo and achieve an IRR of 22.4% throughout the holding period.
- Internal Rate of Return (IRR) = 22.4%
- Multiple on Invested Capital (MOIC) = 2.8x
Thanks for this post – it is very useful! A question on the calculation of IRR and MOIC, what did you do to make the functions applied to all the cells on the right and give out correct answer? i tried to use apply the formulas to the right use… Read more »
Hi, Rohit, Good question. This is hard to do because each exit year calculation is in a different row, so you cannot simply copy to the right. You could put the calculation of IRR and MOIC off to the right, and copy the formula down, and then you could copy… Read more »
why is the amount under the revolver a dash (-) and not 50million?
Hi Danielle – the revolver is assumed to be left undrawn on the initial date of purchase, and the formatting is set to display zero as a dash.
Hi. If in a CFDF txn, the minimum cash stays on the balance sheet and is not taken by the seller, why do we need to show it in the Uses ? Doesnt showing the min cash in the uses imply that the buyer will fund the min cash using… Read more »
Hi, SK,
If the minimum cash is kept in the company as it is turned over to the buyer, then you are correct, there is no need to show cash to B/S as a use of funds. But that also means it is not strictly a CFDF transaction.
BB
Hi Brad, Thank you for all of your responses here. They are very helpful. I understand that the CFDF article conventionally means the seller takes all the cash regardless of whether it is necessary for the operation or it is excess cash. However, we cannot read as so from the… Read more »
Hi, S U, That is a good catch of a slight inconsistency. But it could go either way: It could be totally CFDF where the seller keeps all cash, then the buyer provides an injection of cash in uses of funds to run the business, in which case they probably… Read more »
Hi,
In Step 3. could you please explain why amortization of financing fees is included as part of net income but mandatory amortization of term loan isn’t? Shouldn’t net income include all debt related repayments? Thanks.
Hi, Krishna, Amortization of financing fees is an accrual expense that reduces net income but is added back on the cash flow statement. It is treated like non-cash interest expense, part of the cost of the loan. Amortization of loan principle is not a cost of the loan, it is… Read more »
How long should it take to build this model from scratch (blank excel) and complete the exercise? thanks
If starting from scratch, the basic modeling test should take approximately 45 minutes to complete (and an hour at most).
thank you!
Yes, thank you, Justin!
Hi, thanks a lot for the post, very helpful. I have a question regarding the circularity. Even if I look at the tab “Complete” (which includes the circularity toggle), it still gives a circularity error. So, if I switch the toggle from 1 to 0, and then to 1 again,… Read more »
Hi, Emilie,
Make sure in your calculation settings (Alt F T F) you have checked ‘enable iterative calculation’.
BB
Hi Brad,
Thank you for this post.
In the exercice can you explain how LABOR (%) grows by +0,2 PP each year? E.g. from 1,5% in Y1 to 1,7% in Y2.
How are we supposed to know LABOR basis point in the exercise?
I think I missed smt there 🙂
Hi, Dalongeville,
The LIBOR is a projection of what the rates are expected to be in the futures market. LIBOR is now discontinued, so you would want the SOFR rate, and you can find a projection of that here: https://www.chathamfinancial.com/technology/us-forward-curves
BB
Hi WSP team, I have an odd question – if the sponsor is to buy the target company on a CFDF basis, then shouldn’t the use of fund side begin with purchased equity value rather than the purchased enterprise value? If it is due to the use of entry EV/EBITDA… Read more »
Hi, Aries, No, the use of funds in a CFDF transaction is enterprise value plus fees (possibly plus cash to the B/S). EV is cash free debt free, that is the point; it is the value of the core operations of the business, independently of how it is financed and… Read more »
Why is the cash flow not used to pay off the TLB and senior notes? (i.e. why do we not use the min(max) formula instead of assuming only the mandatory amortization amount to be paid off every year?)
Hi, Jimmy,
Good question. Our standard LBO test does use excess cash flows to pay down the TLB. Unlikely we will pay the senior notes early, as bonds usually have call protection to prevent early repayment.
BB
The model is really very helpful …I have learned from basic to advanced about LBO modelling just from these few pages of wall street prep.
Glad to hear, Sushant!
Nvm, saw an earlier screenshot.
Thanks for the article – I have a question about transactions that occur on a cash free debt free basis. in a CFDF basis, does the seller take ALL of the cash (including cash needed for daily operations)? OR does the seller just take the excess cash (and you assume… Read more »
Hi, Andrew, Yes, typically the seller takes all the cash, which is why the buyer needs to inject some cash as a use of funds. And yes, technically the value of core operations should include some minimum cash, and in that sense, enterprise value is not exactly the same as… Read more »
For the equity value calculation at the end, shouldn’t we exclude the 5m in cash if it is not excess cash and necessary to run the business
Hi, Cynthia, Here is the answer I just gave Andrew above about the cash that is needed to run the business, which should answer your question a well. The exit enterprise value should not include the value of the cash to run the business, we just assume it is done… Read more »
Hello, just two questions regarding the IRR. 1) What do we calculate FCF (post revolver) if at the end we use a multiple of EBITDA and exit value for the calculation of IRR? 2) In the table above IRR increases as the exit years get delayed. I’ve seen some models… Read more »
Hi, Leo, EBITDA is just a way to estimate the enterprise value that we will sell the business for, whereas FCF post revolver helps us to measure how much excess cash we have to pay down debt, and that will increase what is left over for equity value at the… Read more »
Hi, For the revolver, in this scenario it seems that it is set to draw only if pre-revolver fcf is negative but shouldn’t we need to adjust it to reflect the 5mn in minimum cash each year? I realize that because the CFs are all positive that this doesn’t impact… Read more »
Hi, Avery, Great question, and yes, it should account for the $5mm minimum cash. But that is why we base the revolver draw not only on the pre-revolver FCF from that year, but on the cash available which takes into account the beginning cash balance, the minimum cash balance, and… Read more »
Hi, I was wondering if the formula of the UFCF is missing something.
If we start with NI, isn’t it UFCF = NI + D&A + I (1-t) – CAPEX – Change in NWK?
I don’t see I (1-t) being added back.
Can we also start with NOPAT?
Hi, Anthony, That would be the correct formula for Unlevered FCF, and yes, for UFCF, we would preferably start with NOPAT (which is EBIT * (1-tr). However, in this LBO model, what we mean by FCF is not UFCF, but the FCF available to pay down the revolver, and after… Read more »
Hi,
For the calculation of unlevered FCF, aren’t we supposed to add back interest expense x (1-t) ?
Unlevered FCF = Net income + depreciation and amortization + interest expense (1-t) – capital expenditures – change in net working capital
Hi, Leo, That would be the correct formula for Unlevered FCF. However, in this LBO model, what we mean by FCF is not UFCF, but the FCF available to pay down the revolver, and after the revolver is paid, the increase in the cash balance. So, it needs to incorporate… Read more »
Great thanks !
You’re welcome!
Why didn’t we add the $50mm under “$ amount” for revolver in “Debt Assumptions”? Why are we just including it in the debt schedule?
Hi Lily – the revolving credit facility is assumed to be undrawn on the date of initial purchase. The $50 million is the revolver’s predefined capacity, i.e. the maximum amount that can be drawn if the borrower experienced a cash shortfall. If left undrawn, the revolver is a source of… Read more »
Hi, how would you account for the transaction fee in the final returns calculation?
Thanks a lot for your help and great tutorial!
Hi, Lisa, As long as the transaction and financing fees are included in uses of funds, then the amount of equity that the sponsor has to inject in order to come up with the total sources of funds will be impacted by those fees, and therefore the final return calculation… Read more »
Got it, thanks a lot Brad, great tutorials!
You’re welcome, Lisa!
Hi, in case I have a lot of unused cash and no debt to pay off before the purchase, can I use it as a source of funding? If so, how can I add this step to the model?
Thanks a lot!
D.
Hi, Diana,
Yes, if a company has excess cash and no debt to pay off, that cash is netted against the purchase price as a ‘source of funds’ in the LBO model.
BB
Hi, in the FCF pre revolver, why do we subtract “mandatory amortization? Is it a real cash out flow?
Hi, Alexandre,
Yes, they are real cash outflows. Mandatory amortization (or principal payments) must be made before any revolver payments are made, so we need to include them in pre-revolver FCF.
BB
So that means we can possibly use the revolver to pay the mandatory amortization of other debts?
Hi, Anqi,
Yes, we could use the revolver to make a mandatory amortization payment if we had no other source of funds.
BB
Why are we adding back the amortization of financing fees? I understand that it’s a form of amortization, but it seems to me that it’s a cash expense?
Hi, Chris,
It is a non-cash expense. The fees are paid up front in cash out of the proceeds from the debt issuance, like a prepaid expense. But they are expensed over the life of the debt as a non-cash expense.
Brad
This makes sense. Thank you for the clarification!
You’re welcome!
In step 3 there is already interest expense, how is that possible?
because it is stated that for a while we leave it as a blank
Jonibek:
That’s only because we are using screenshots from the completed model.
Best,
Jeff