LBO Value Creation Analysis

1. Introduction

Which factors are driving most of the sponsor returns?

It can be tricky disentangling the different value drivers contributing to your rosy 25% IRR. It’s important to do so, however, for many reasons:

  • Is debt paydown or EBITDA growth contributing most of the returns?
  • Are these growth assumptions defensible?
  • Uh oh, we’re assuming 90% of the value creation comes from debt paydown and multiple expansion…

You get the idea.

This can be useful on both the buyside (before presenting your model to a superior) and the sellside (making sure that sellside case is somewhat defensible).

This tutorial is going to build off the prior two articles, and you’ll learn how to put together a value creation bridge - showing where the sponsor returns are really coming from.

Here is what the completed analysis looks like:

You can reference the completed excel file.

2. Fundamentals

Sponsors grow the equity value of their investments using two levers:

  1. Increasing the Total Enterprise Value (TEV)
  2. Debt Paydown - thereby increasing the portion of Total Enterprise Value allocated to equityholders.

Value creation from the sponsor’s perspective is:

Value Creation = Exit Equity Value - Equity Invested

This can be expanded to separate the impact of debt paydown from the change in Enterprise Value. These are the two levers from above:

Value Creation = Change in Enterprise Value + Debt Paydown

While debt paydown is simple to measure (that’s what the LBO model tracks), the change in enterprise value can be separated into different variables.

3. Decomposing Change in Enterprise Value

Enterprise Value, otherwise known as Transaction Value, is usually calculated:

Transaction Value = Adj. EBITDA x Transaction Multiple

Therefore, the change can be separated into these two buckets:

  1. Change in multiple (a.k.a. Multiple Expansion) and
  2. Change in EBITDA.

Change in EBITDA can be further decomposed into revenue growth and margin enhancement. For example, cost savings might not contribute any revenue growth, but would increase the EBITDA margin. We’re keeping the change in EBITDA in a single bucket here though, because in our dummy operating case we keep margins constant. Therefore, all of the EBITDA growth comes from revenue growth.

4. Takeaways

In our illustrative output above, you can see that ~50% of total value creation comes from EBITDA growth, and since margins are constant, we assume revenue is the primary driver.

On the buyside, it would be important to diligence the specific assumptions underlying that revenue growth. Especially for a larger company, you would probably have a detailed operating build for each of the different business segments. Maybe one segment in particular is driving most of the growth.

On the sellside, you know that sponsors will do this analysis, and they will focus on the key diligence points. You can help management prepare for the areas that will receive the most attention.

5. FAQ

Why are you adding fees to calculate the initial sponsor equity?

Because we’re calculating how much money the sponsor needs to invest, and they need to pay for fees. Therefore, bigger fees require a greater sponsor investment.

Why are you subtracting fees from the sponsor exit value?

Again, the sponsor needs to pay for fees. This value creation analysis is net of fees. Any money the sponsor receives comes after paying down debt and transaction fees.

For the entry calculations, why are you using total debt (600mm) vs. net debt (595mm)?

If you were wondering this, congrats - you’re being a careful reader.

The entry column must match the sources & uses. We assume 5mm minimum cash balance, so that cash isn’t available for the transaction. It’s the cash required to run the company and fund working capital. Therefore, we’re treating the 5mm of cash as an operating asset and ignoring it for the purposes of the transaction.

So why are you using net debt to calculate the exit value?

Good job picking up on this inconsistency - you may make an astute MD one day.

The entry column must match the sources & uses. That’s why we’re excluding the minimum cash balance there.

The exit column returns should match the returns included in the LBO. Therefore, we need to use the same net debt figure. In the LBO returns section, we try to keep it simple by calculating net debt as all debt minus all cash.

This leaves us with the aforementioned inconsistency in the treatment of operating cash. It’s important to recognize that this inconsistency stems from how we treat operating cash in the LBO. It is not a new assumption introduced in the value creation analysis.

If you spotted this inconsistency while exploring the value creation analysis, then I would argue that this exercise was successful. It led you to think critically about our assumptions and what really generated the modeled returns.

Hold up, why aren’t you subtracting fees in the LBO returns calculations?

Alright, you caught me. That’s another inconsistency. Generally, for high-level LBOs like this one, you don’t subtract future transaction fees in the returns calculations.

We’re including the fees in the value creation analysis, because it felt inconsistent to include the impact of the fees at entry and not at exit (especially when they’re side-by-side).

If you set the exit fees to 0, the calculated exit equity value and MoIC will match the values calculated in the LBO.

6. Next Steps

Try the rest of our private equity modeling tutorials:

  1. Easy LBO
  2. Intermediate LBO Guide
  3. Advanced LBO Guide
  4. Ability-To-Pay Analysis
  5. Dividend Recap Guide
  6. Private Equity Modeling Guide

Ability-To-Pay Analysis

1. Introduction

How much can a financial sponsor afford to pay?

This is the question that an ability-to-pay analysis strives to answer. On the sellside, bankers use this analysis to frame sponsor bids. On the buyside, sponsors perform similar sensitivity analyses when determining potential bids. The key assumptions are the operating projections and the financing case.

Here is what the completed analysis looks like:

Try to build the ability-to-pay analysis on your own, but you can use this completed version as a reference.

2. Calculate Equity Invested

You should build your ability-to-pay analysis after building your LBO, because many of the key inputs come directly from the LBO.

The key concept to understand is: in an ability-to-pay analysis, you work backwards.

What does that mean?

Well, we know most sponsors have an IRR threshold of 20% - 25%. They won’t be interested in a transaction if they don’t expect to achieve an IRR within that range. Since we’re taking the current operating case as a given, we know the 5-year projected EBITDA, so we can calculate the transaction value at exit. We have the assumed financing case as well, so we know what the cumulative debt paydown and future net debt will be.

Equity Value = Transaction Value - Net Debt

Using the projected EBITDA and a range of exit multiples, calculate the implied transaction value at exit. Then, subtract future net debt (at exit) to derive the future equity proceeds to the sponsors.

Next, using the IRR threshold (20% - 25%), calculate the maximum equity check size. There are a couple ways to do this:

Option 1

Max Equity Invested = Equity Value @ Exit / (1 + IRR) ^ 5

Option 2

Calculate the MoIC as an intermediate step. MoIC = (1 + IRR) ^ 5
Then divide future equity value by MoIC. It’s the same thing, just broken into 2 steps.

Now we have the maximum equity check size, but we need to calculate the implied entry multiple.

3. Calculate Total Sources

To go from maximum equity investment to implied entry multiple, we need to back out the sources and uses. Given our cash-free, debt-free transaction, this is pretty simple, but it can be more complicated.

In addition to the calculated sponsor investment, we have the debt financing assumptions from the LBO. Since we’ve been lumping management rollover and sponsor equity together for returns calculations, we don’t need to add the management rollover amount. It’s implicitly included in the maximum equity investment.

Calculate total sources:

Total Sources = Maximum Equity Investment + Total Debt Raised

4. Implied Transaction Multiple

Next, let’s subtract any refinanced debt (there is none) and expenses. All remaining sources of cash can go towards the equity purchase price. Combining the equity purchase price with net debt, we get the implied transaction value (here, equal to equity purchase price). Divide by 2016A Adj. EBITDA to derive the entry multiple.

Note: we could have subtracted transaction expenses from total sources to calculate the implied transaction value, but we wanted to lay out all of the sources & uses line items.

5. Check Your Work

There’s a neat way to check your work: You can plug the implied transaction multiple back into the LBO as the entry multiple, and the calculated IRR (in the Sponsor Returns section) should exactly match the corresponding IRR threshold.

Example: Use the implied entry multiple assuming a 20% IRR over 5 years and a LTM exit multiple of 9.0x. If you plug that implied entry multiple into the Transaction Assumptions section, the 5-year IRR should be exactly 20.0%.

If yours doesn’t match, double check your model against the provided Excel file.

6. Next Steps

Try the rest of our private equity modeling tutorials:

  1. Easy LBO
  2. Intermediate LBO Guide
  3. Advanced LBO Guide
  4. LBO Value Creation
  5. Dividend Recap Guide
  6. Private Equity Modeling Guide

Training LBO (Model Included)

1. Introduction

This guide will provide basic instruction on modeling LBOs. Specifically, you will learn:

  • How to build a full-featured LBO model
  • Some key Excel functions
  • Core financial concepts and terminology
  • How to model different types of debt

This guide will not serve as:

  • An introductory accounting course
  • A “from-the-ground-up” Excel course

If this model is too complicated, start with our Easy LBO Guide.

LBO Overview

LBO means “Leveraged BuyOut.” Two main characteristics differentiate LBOs from other types of acquisitions:

  • Instead of a “corporate” buying another “corporate,” e.g., Amazon buying Whole Foods, in a LBO, a Financial Sponsor (a.k.a. private equity firm) is the buyer
  • The sponsor uses a mix of debt (hence “Leveraged”) and equity to buy the company. The equity is the money that the financial sponsor invests, and sponsors raise debt financing from banks / the capital markets.

Do sponsors buy the whole company?
In LBOs, the sponsor generally buys a controlling stake in the company. Management and/or the seller may retain some equity.

Why does a sponsor use debt?
A sponsor uses debt so that they can buy the company with less of their own cash (equity). Over time, as the company pays down its debt, the equity value will grow as a percentage of the total enterprise value, thereby increasing the value of the sponsor’s investment.

Total enterprise value (TEV) = equity value + net debt.

If enterprise value is increasing and net debt is shrinking, the sponsor is simultaneously using two levers to increase their equity value:

  1. growing TEV and
  2. increasing the percentage of TEV owned by equityholders.

Debt Primers

If you don’t have a decent understanding of the different types of debt used in LBOs, spend ~15 minutes perusing the guides below. A quick read will give you the background knowledge you need.

2. Getting Started

Download the following files:

  • Blank Model - you’ll build your own model with this template.
  • Completed Model - for your reference. This Excel file includes a tab for each step of the tutorial. Note: the numbering starts at 3; the first step is the tab labeled “LBO_3” - “3. Filling in Transaction Assumptions.”

You should try to follow along, step-by-step, building your own model from the blank file.

Our model template is built vertically within a single tab, and is divided into the following sections:

  • Transaction Assumptions
  • Income Statement
  • Working Capital Schedule
  • Balance Sheet
  • Statement of Cash Flows
  • Debt Schedule
  • Interest Expense Schedule
  • Credit Metrics
  • Returns Calculations

For simpler LBOs, like this one, we prefer a single-tab, vertical format, because it makes it easier to link between sections and trace dependencies.

The working capital schedule and balance sheet are often unnecessary, but we’re including them here so you can see how all the pieces fit together. Likewise, the debt schedule and interest expense calculations are usually bundled together, but since they’re a bit longer here, we split them up.

3. Filling in Transaction Assumptions

At the top of the “LBO” tab, you can find the Transaction Assumptions section. This is where we’re going to start, and there’s a lot going on, so we’ll go through a brief overview:

  • The Sources & Uses section is one of the key parts of any financial model. Quite literally, it describes what your sources of cash are, and what you’re using them for. In LBOs, your sources will be a mix of debt and equity financing, and your main use will be purchasing the target company (“Purchase Equity”)
  • The Transaction Assumptions section is where we’ll calculate the transaction value / equity value. In an acquisition, “transaction value” is equivalent to “total enterprise value” (TEV). Therefore, equity value = transaction value - net debt. We’re going to assume a cash-free, debt-free transaction, which means the sponsor is buying the company with no debt to refinance and no excess cash (there will be a little bit of operating cash).
  • The Financing Assumptions section contains financing assumptions for various tranches of debt (we provide these). We’ll show you how to use these inputs to perform the financing calculations required in a LBO.

Transaction Value & Equity Value

  1. Link the LTM Adj. EBITDA from the income statement (use 2016A - we’re assuming an illustrative transaction close at FYE 2016).
  2. Calculate the Transaction Value (LTM Adj. EBITDA x Transaction Multiple).
  3. Link the “Cash On-Hand” from Sources as the cash balance.
  4. Link “Refinance Existing Debt” from Uses as the debt balance.
  5. Equity Value = Transaction Value - Debt + Cash
    Hint: transaction value and equity value should be the same in this case (cash-free, debt-free).

Management Rollover
In a management rollover, the management team agrees to “roll over” part of their equity stake as an investment alongside the financial sponsor. A management rollover is not uncommon, and sponsors like it, because it keeps the management team even more incentivized. If check size (how much money a sponsor is able to invest in a single deal) is a concern, a management rollover can help mitigate that.

Here we’re assuming a 10% rollover. This means that the management team owns at least 10% of the existing equity and agrees to keep 10% invested alongside the sponsor. 10% is probably a bit high, but not unheard of. Multiply the rollover % by the calculated equity value.

4. Purchase Accounting

We’re using overly simplified purchase accounting, because frankly, purchase accounting isn’t very important for LBOs.

The core concept of purchase accounting is that the value paid for an asset must be capitalized on the balance sheet. In the case of an acquisition, the asset being acquired is the company itself and its bundle of assets and liabilities.

In most acquisitions (including this one), the price paid exceeds the existing book value of net assets. Therefore, the purchase price is allocated to the net assets by writing them up to fair value.

Example: you buy a manufacturing company for \$500mm. The net assets have a book value of \$100mm, so there’s a \$400mm delta, but the fair value of the company’s manufacturing PP&E is actually \$200mm. On the balance sheet of the post-acquisition company, the PP&E is written up to \$200mm. But that doesn’t cover the full delta - so what next? Any remaining purchase price is allocated to an indefinite-lived intangible asset called goodwill (“indefinite-lived” is just an accounting term that means it doesn’t have a finite life. Theoretically, it could last forever).

Here we’re assuming that the book value of net assets (excluding the existing goodwill) is equal to fair value, so the entire excess purchase price is allocated to goodwill.

  1. Set Total Equity Value equal to the Equity Value you just calculated above.
  2. Link the appropriate asset & liability fields from the balance sheet.
    • Remember to subtract out the existing goodwill (goodwill functions as the plug; we don’t care about the prior plug – we’re calculating it again here).

5. Debt Financing

Now we’ll fill out the Debt Financing section below the provided financing assumptions. Here we’ll calculate the actual amount of debt raised and associated fees.

  1. For the Revolver, link to the hard-coded “Revolver Draw” assumption under Sources. The assumed draw is $0, which is standard practice. It would be unusual to fund the transaction with the Revolver. The Revolver functions as a safety net, and it is used to fund any short-term cash shortages.
  2. Calculate the $ raised for the Term Loan B, Senior Notes and Subordinated Notes using the supplied leverage multiples and LTM Adj. EBITDA.
    • Simply multiplying the leverage multiple by Adj. EBITDA can result in oddly specific debt balances (e.g., $179.4mm of senior notes), whereas companies raise round numbers of debt.
    • Use the mround function and the “Minimum Debt Increment” assumption found under Other Assumptions to get nice, round debt balances for each tranche:

      =mround(leverage * EBITDA, minimum debt increment)

  3. Calculate the financing fees for each tranche by multiplying the calculated debt balance by the fee % from the Financing Assumptions table.
    • For the revolver fee, multiply the financing fee % by the revolver commitment.

6. Sources & Uses

Now we’ll fill out the Sources & Uses using our work from the preceding sections.

  1. Under Sources, link the individual debt tranches from the Debt Financing section.
  2. Link Management Rollover from Transaction Assumptions.
  3. Under Uses, set Purchase Equity equal to Equity Value from Transaction Assumptions.
  4. Link Transaction Expenses from Other Assumptions.
  5. Link the Total Financing Fees from the Debt Financing section.
  6. Now the only remaining line item is Sponsor Equity; set it equal to Total Uses - the sum of all other sources.

    Sponsor Equity = Total Uses - Sum(All other sources)

Your Sources & Uses should now balance.

7. Income Statement

Let’s fill out the PF Income Statement. The historical revenue, EBITDA and D&A are already in place, and we’ve included all the assumptions you need in a memo below the income statement.

  1. Grow Revenue by multiplying the prior year by (1 + growth rate).
  2. Calculate projected EBITDA by multiplying margin by Revenue.
  3. Calculate D&A with the % of sales.
  4. Subtract D&A from EBITDA to arrive at EBIT.
  5. Leave Net Interest Expense blank for now, but calculate EBT as EBIT + blank Net Interest Expense row.
    • EBT equals EBIT for now, but we will add Net Interest Expense in the future.
  6. Calculate tax expense by multiplying the tax rate found in Other Assumptions by EBT.
  7. Calculate Net Income by adding the tax expense (negative) and EBT.

8. Working Capital Schedule

We’re going to use Revenue, COGS and historical working capital to build out the Working Capital Schedule.

  1. Set Sales equal to the Revenue line in the Income Statement.
  2. Calculate COGS by multiplying Sales by the COGS memo line in the Income Statement.
  3. Link the 2016A working capital line items from the Balance Sheet:
    • Net Accounts Receivable
    • Inventory
    • Other Current Assets
    • Accounts Payable
    • Accrued Expenses
    • Other Current Liabilities
      We’ll use these to calculate the historical ratios, which will be the basis for our projections.
  4. Calculate the historical DSO, DIH, DPO and % of sales metrics. For metrics based on days, use the provided 360-days cell.
    • DSO = Net Accounts Receivable / Sales * 360 Days
    • DIH = Inventory / COGS * 360 Days
    • DPO = Accounts Payable / COGS * 360 Days

      Cash conversion cycle is a common metric for working capital efficiency. Cash conversion cycle = DIH + DSO - DPO. Time that cash is invested as inventory + time that the accounts receivable are outstanding - the time that the company can delay paying its account payables.

  5. Once you’ve calculated the historical ratios for 2016A, set the ratios in all the future years equal to 2016A. This is a simplifying assumption - in a real LBO, it would be important to diligence all these assumptions and have a detailed view on how/why a company’s working capital profile might change.
    • By holding these ratios (and COGS as % of Sales) constant, we’ve made working capital a linear function of Sales.
  6. Use the projected working capital ratios (held constant) and the projected Sales & COGS to calculate the projected working capital line items.
    • Example: Projected Inventory = DIH / 360 * COGS
  7. Calculate Net Working Capital (Current Assets - Current Liabilities) in each year.
  8. Calculate the (Increase) Decrease in NWC as: prior year Net Working Capital - current year Net Working Capital. We do it this way (so that increases are negative), because we’re going to link this line directly to the Statement of Cash Flows. Increases in NWC are a use of cash, and decreases are a source of cash.

9. PF Balance Sheet

We’re assuming that the transaction closes at Fiscal Year End 2016. FYE 2016 has already come and gone, but best practice is to use the latest available balance sheet. For a public company, you’d use the latest 10-K/10-Q.

The historical 2016A balance sheet that we have here is the company’s standalone balance sheet (assuming no transaction). We’re going to calculate the PF 2016 balance sheet - what the balance sheet would look like immediately after closing the transaction.

When preparing a PF balance sheet, incorporate all line items from the Sources & Uses. You can set balance sheet line items that aren’t affected by the Sources & Uses equal to the standalone balance sheet.

  1. Set all working capital line items equal to 2016A:
    • Net Accounts Receivable
    • Inventory
    • Other Current Assets
    • Accounts Payable
    • Accrued Expenses
    • Other Current Liabilities
  2. Set Net PP&E and Other Noncurrent Liabilities equal to 2016A.
  3. The PF Shareholders’ Equity = Sponsor Equity + Management Rollover - Transaction Expenses. You can completely ignore the historical equity balance.
  4. Debt = sum(all sources of debt financing).
    • Make sure to include the line for the revolver draw even though it’s 0.
  5. Other Noncurrent Assets = 2016A balance + Financing Fees.
    • Financing Fees are capitalized and then amortized over the term of the debt instruments, and best practice is to stick the capitalized fees in Other Noncurrent Assets.
  6. Goodwill = PF Goodwill, which you calculated in the Purchase Accounting section.
  7. Set Cash & Cash Equivalents = 2016A cash balance - “Cash On-Hand” + “Fund Cash Balance.” Since these latter two are both $0, PF cash = 2016A cash.

Your PF balance sheet should now balance.

Getting the balance sheet to balance can be tricky. A good way to think about it is that if the historical balance sheet balances and the sources and uses section balances, the combination should also balance. Every line item in sources & uses must be accounted for in the PF balance sheet. The sources are generally straightforward. Goodwill and any purchase accounting write-ups are the bridge between the equity purchase price and historical net assets.

10. Statement of Cash Flows

  1. Link Net Income from the income statement. You can ignore the historical years; we only care about the cash flows going forward.
  2. Add back D&A (noncash expense).
  3. Link to the (Increase) Decrease in NWC from the Working Capital Schedule.
  4. Leave Noncash Interest Expense blank for now.
  5. Calculate projected CapEx as a % of Revenue (assumption found in the memo section below the Income Statement).
  6. Add up the Cash from Operating Activities and Levered Free Cash Flow lines.
  7. Link the 2016 Ending Cash Balance from the PF 2016 cash from the balance sheet.
  8. Set the 2017E Beginning Cash = to the 2016 Ending Cash (in statement of cash flows).
  9. Calculate Cash Available for Debt Repayment:
    • Add Levered Free Cash Flow.
    • Subtract the Minimum Cash Balance (from “Other Assumptions” section).
    • The Minimum Cash Balance ensures that a minimum amount of cash remains - the cash needed for ordinary operations.
  10. Leave the Mandatory Amortization and Optional Prepayment lines blank.
  11. Add back the Minimum Cash Balance above Ending Cash.
  12. Then calculate Ending Cash:

    Ending Cash = sum(Cash Available for Debt Repayment, Mandatory Amortization, Optional Prepayment, Minimum Cash Balance).
    We add back the Minimum Cash Balance, because it was never spent. It was just taken out of Cash Available for Debt Repayment.

  13. Carry over these formulas through the rest of the projection period.

Interest Income
Now we’ll calculate Cash Interest Income. Although it’s immaterial, it’s still best practice to include this. We’re going to use an “if” statement to manage circular references.

Average Cash Balance = if (CIRC = 1, average(Beginning Cash, Ending Cash), Beginning Cash)
The circ is found under “Other Assumptions.”

Calculate Cash Interest Income by multiplying the provided interest rate by the Average Cash Balance for each year.

11. Debt Schedule

  1. To start, link the PF 2016 ending debt balances from the Sources & Uses. Sum them up in the total line.
  2. Calculate the Undrawn Revolver Commitment:

    Undrawn revolver commitment = max(0, revolver commitment - the drawn balance)
    Include “max(0, “ because it should never be a negative number. You can drag this formula across.

  3. In 2017E - 2024E, link the Cash Available for Debt Repayment from the Statement of Cash Flows.

Mandatory Amortization
We’ve already included the amortization schedule for you, but generally, term loans are the only debt instruments with mandatory amortization. Mandatory amortization consists of regular scheduled principal payments. Senior notes and bonds pay interest every year, but principal is only repaid at maturity.

Calculating the mandatory amortization is not as simple as multiplying the % by the beginning principal amount.

Mandatory Amortization = -max(0, min(PF balance * annual schedule %, prior year ending balance))
The “max(0, min(“ clause is a great way to constrain the value of a cell - never falls below 0.

In English, mandatory amortization is the lesser of the scheduled amortization or the remaining balance at the beginning of the year (the beginning of this year = the end of the prior year). Remember to lock the PF 2016E debt column when multiplying by the annual amortization %.

Cash Available for Optional Prepayment = Cash Available for Debt Repayment + Total Mandatory Amortization

Optional Prepayment
Calculating Optional Prepayment can be tricky, especially for the Revolver. Remember that the Revolver functions as a safety net. Therefore, if Mandatory Amortization exceeds the Cash Available for Debt Repayment (resulting in negative Cash Available for Optional Prepayment), the company will draw on the Revolver to make up the gap. On the other hand, if there’s cash left over, the company should pay down any outstanding Revolver balance.

This is best represented using an “if“ statement:

Revolver Optional Prepayment = if(Cash Available for Optional Prepayment > 0, -max(0, min(last year’s ending Revolver balance, Cash Available for Optional Prepayment)), -min(0, Cash Available for Optional Prepayment))

Word to the Wise
You’ll notice we’re not limiting the revolver amount by the revolver commitment. As one astute reader pointed out, this is technically wrong. We’re doing it this way, because if you limit the revolver but have insufficient cash flows, you wind up with a negative cash balance, which is also wrong.

In practice, if your LBO model draws on the revolver at all, that’s cause for concern, and you likely need to reevaluate your assumptions. The revolver is a financing source of last resort, and you should not be drawing on it in your forecast.

The Optional Prepayment for the other debt tranches is more straightforward. And by stacking debt tranches like this, we can use one formula to apply to all tranches.

The debt tranches are organized in order of priority – the Revolver gets paid down first, then the Term Loan B, and then the notes. So the Optional Prepayment for the Term Loan B depends on if there’s anything left over after paying down the Revolver. The notes have the same relationship with the Term Loan B, etc.

You can apply this formula to the Optional Prepayment of all remaining tranches (excluding the Revolver):

Tranche Optional Prepayment = -max(0, min(prior year ending balance + Mandatory Amortization, Cash Available for Optional Prepayment - all prior Optional Prepayments)))

Let’s explain this. The Optional Prepayment for a given tranche must be the lesser of the remaining balance (last year’s ending balance + this year’s Mandatory Amortization) and the Cash Available for Optional Prepayments - all preceding (higher priority) Optional Prepayments this year.

Compare your formula with the completed version and drag across all Optional Prepayment cells.

Ending Debt Balances
Now that we have the Mandatory Amortization & Optional Prepayment for each tranche, we can calculate the ending debt balances for each year.

  1. Go back up to the ending debt balance section and set each 2017 ending balance:

    Tranche balance= max(0, prior year ending balance + Mandatory Amortization + Optional Prepayment).

  2. Carry this formula across for all tranches and projected years.
  3. Finally, link the Total Mandatory Amortization and Total Optional Prepayments back to the Statement of Cash Flows.

Note: typically, you can’t repay outstanding notes as soon as you’ve paid down the Term Loan B, as we’re doing here. Debt investors who buy high-yield bonds generally don’t want to be repaid early without a fee. If the company is doing well enough to repay the debt early, that means that the company is less risky, and investors would probably want to keep their capital deployed. Early repayment of notes is a simplifying assumption, and it’s typical to model LBOs this way. Alternatively, you could accrue a cash balance and/or refinance the notes.

12. Interest Expense Schedule

We’ve provided an illustrative LIBOR curve. If you’re unsure what this is, or what it’s used for, you should pause and read some of the debt primers provided at the beginning of this tutorial.

We include the Undrawn Revolver as a debt balance, because the company must pay a small amount of interest expense for the unused commitment.

For the Interest Rate Schedule, we’ve used a custom number format to achieve the “FIXED” vs. “FLOAT” formatting. You can google “Excel custom number format” to learn more. Here, where it says “FLOAT,” the cell value = 1, and where it says “FIXED,” the cell value = 0.

  1. Calculate the debt balances. For each tranche, the debt balance should be the average balance from the beginning to the end of a given year. Use the following formula, similar to what we did when calculating Cash Interest Income:

    Debt balance = if (CIRC = 1, average(prior year balance, this year balance), prior year balance).
    If the circ switch is off, then the interest expense will be calculated using the beginning debt balance, which removes the circular reference.

  2. Calculate the interest rate for each tranche.
    • For FIXED tranches, the interest rate should just be the stated rate.
    • For FLOAT tranches, the interest rate should be the sum of LIBOR and the stated rate. We could include more advanced functionality, such as a LIBOR floor, but it would be immaterial. You can read more about the intricacies of the various debt instruments in the primers.
  3. Calculate interest expense for each tranche by multiplying the debt balance by the scheduled interest rate.
    • When the CIRC switch = 1, interest expense is calculated with the average debt balance.
  4. Next we’re going to calculate the “PIK” interest for the notes. For each year, compare the number of years elapsed to the PIK term, and if the number of years elapsed is less than or equal to the PIK term, we’ll include that debt tranche’s interest expense as PIK interest.
    • For the first 3 years, the subordinated notes “PIK.”
    • Since PIK interest is not paid in cash, it’s deemed part of noncash interest expense, which is added back in the statement of cash flows.

      Although Payment-In-Kind (PIK) interest is uncommon in the current financing environment, it pops up occasionally, and it’s an important concept to understand. When notes, or other debt, have a PIK feature, interest expense is not paid in cash for a number of years. Instead, the interest expense is added to the debt balance, so the debt balance accrues over time. PIK interest is a way for LBO investors to manipulate when cash payments occur. An investor might use a debt tranche with PIK interest expense to layer on additional debt, while keeping the interest expense manageable in the early years. The LBO investor would be counting on paying down more senior tranches of debt, alleviating some of the interest expense, before the PIK interest flips back to cash payment. This is an aggressive, riskier technique. Generally speaking, notes and mezzanine debt can have PIK interest. You can learn more about PIK interest here.

  5. Calculate financing fee amortization - the other type of noncash interest expense. Capitalized financing fees are amortized over the life of the corresponding debt tranche. This annual amortization is included in Net Interest Expense, but is a noncash expense. Use the following formula:

    Tranche fee amortization = max(0, min(total fee - sum(prior amortization), total fee / debt term))

    • In English, take the lesser of (the total fee minus cumulative prior amortization) and the calculated annual amortization.
    • By locking the appropriate columns, you can drag this formula across all years and all tranches. Try to get it on your own, but compare with the completed sheet if you’ve having trouble.

Now we have all the components of interest expense. Interest Expense (including PIK interest) + Financing Fee amortization = Total Interest Expense; less Cash Interest Income = Net Interest Expense.

Likewise, we can calculate Total Noncash Interest Expense as PIK Interest + Financing Fee Amortization.

13. Putting the Pieces Together

Fixing the Debt Schedule

After calculating PIK Interest Expense, you may have realized we’re not properly accounting for it in the Debt Schedule. We’re going to fix that now. In the ending debt balance formula for the notes, add the corresponding PIK Interest Expense each year. For the first 3 years, the Subordinated Notes Ending Debt Balance should be increasing.

Adding Net Interest Expense to the Income Statement

Now, we’ll add Net Interest Expense to the Income Statement, and we’re going to include CIRC protection. This is redundant, because we already included a CIRC statement in our debt balance / Interest Expense calculation, but redundancy is better than debugging a broken model.

Use the following formula:

Net Interest Expense = if(circ=1, -Net Interest Expense from Interest Expense Schedule, 0)
Make sure you’re using net interest expense, which includes the Noncash Interest Expense and the Cash Interest Income.

Finalizing Statement of Cash Flows

Now let’s link Noncash Interest Expense into the Statement of Cash Flows as an add-back to Net Income. We do this, because Net Interest Expense reduces Net Income, but this portion of Net Interest Expense is a noncash expense. For the first 3 years, Noncash Interest Expense will be relatively high, because it includes the PIK Interest Expense.

Projected Balance Sheet

Next, we’re going to finish the Balance Sheet. The most important thing to remember when building out a projected Balance Sheet is that every line item in the Statement of Cash Flows must be reflected in the balance sheet. If there’s a single line item from the Statement of Cash Flows that is not properly incorporated in the Balance Sheet, your Balance Sheet won’t balance.

  1. Link Cash & Cash Equivalents in each future year to the Ending Cash on the Statement of Cash Flows.
  2. Link working capital line items from the Working Capital Schedule:
    • Net Accounts Receivable
    • Inventory
    • Other Current Assets
    • Accounts Payable
    • Accrued Expenses
    • Other Current Liabilities
      These line items are accounted for in the (Increase) Decrease in NWC line on the Statement of Cash Flows.
  3. Keep Goodwill constant in all future years. Just set it equal to the prior year.
  4. Keep Other Noncurrent Liabilities constant in all future years.
  5. Shareholders’ Equity = prior year Shareholders’ Equity + Net Income.
  6. Link Debt to the corresponding total debt balance in the Debt Schedule.
    • The changes in the debt balance include the following line items: Mandatory Amortization, Optional Prepayment and PIK interest (included in Noncash Interest Expense).
  7. Other Noncurrent Assets = prior year balance - Financing Fee Amortization for the given year.
    • Now that we’re including Financing Fee Amortization, we’re fully incorporating the Noncash Interest Expense line from the Statement of Cash Flows.
  8. Net PP&E = prior year Net PP&E + CapEx - D&A.
    • Basically, it grows every year by the amount of new capitalized assets less D&A. It’s constant here, because as a simplifying assumption, we’ve made CapEx = D&A.

The balance sheet should now balance, because we’re incorporating all line items from the statement of cash flows.

14. Credit Metrics

Every LBO model should include basic credit metrics, because these metrics and the credit profile will inform debt investors’ appetite to fund the transaction. They indicate the potential credit risk.

Credit Ratios
The two main types of credit ratios are leverage ratios and interest coverage ratios:

  • Leverage ratios - compare the amount of debt and the company’s cash flow (EBITDA).
  • Interest coverage ratios - compare the company’s cash flow (EBITDA) and the annual interest expense burden.
  1. Link to the corresponding debt balances along with the EBITDA and CapEx.
    • Senior Secured Debt = Revolver + Term Loan B
    • Senior Debt = Revolver + Term Loan B + Senior Notes
  2. Cash Interest Expense = Total Interest Expense (before Financing Fee Amortization and Cash Interest Income) - PIK Interest Expense.
  3. For 2016 Cash Interest Expense, I generally set it equal to 2017 Cash Interest Expense as a shortcut. A better way is to calculate the full amount of Interest Expense (i.e., the starting debt balances multiplied by the applicable interest rates, less any PIK’d interest).
    • 2017 Cash Interest Expense should be lower than that amount, because debt is paid down over the course of the year, which leads to a lower average debt balance.
  4. Fill out leverage and interest coverage ratios.
    • The ratios should be pretty self-explanatory, but refer to the completed sheet in case you have any issues.

15. Sponsor Returns

MoIC and IRR
When performing a LBO analysis, we measure sponsor returns using the Multiple of Invested Capital (MoIC) and the Internal Rate of Return (IRR). Sponsors get paid based on MoIC, but their investors care about IRR, so both are important. Even if a particular investment has a lackluster IRR, a decent MoIC is good for the sponsor, because they get a share of the dollar profits (not based on IRR). That being said, the fund IRR will be important for marketing their next fund to investors.

MoIC is literally the multiple of money made, e.g., the sponsor and its investors made 3x their money. IRR is the discount rate that would be required in order for the sponsor to achieve a $0 NPV from the investment. These are closely related concepts - the difference is that IRR takes into account the timing of cash flows, whereas MoIC measures only the dollars. You can Google to learn more.

Exit Multiple Sensitivity
Using line items from the rest of the model, and a range of exit multiples, we’ll calculate the sponsor’s returns (MoIC and IRR). Showing a range of exit multiples is helpful, because it highlights the impact that the exit multiple can have on returns. PE firms often hold investments 5+ years, and the valuation of a particular asset can shift dramatically over that time frame. In the best case scenario, the sponsor buys an asset at the bottom of the cycle, and valuations subsequently skyrocket. An exit multiple that’s substantially higher than the entry multiple is a great way to make money, but the opposite can happen, too.

When a sponsor sells an investment for an exit multiple that’s higher than the entry multiple, that’s called Multiple Expansion. It’s an elusive, mythical beast, and you can read more about it here.

  1. Start by linking Adj. EBITDA, Debt and Cash.
  2. Calculate Net Debt.
  3. Calculate Enterprise Value based on the exit multiples on the left (Enterprise Value = exit multiple x LTM Adj. EBITDA).
    • The highlighted row corresponds to the entry multiple (i.e., constant entry and exit multiples).
  4. Equity Value = corresponding Enterprise Value (which varies by exit multiple) - Net Debt.
  5. Set Investment equal to the total equity invested: Sponsor Equity + Management Rollover.
    • In a real transaction, there would be a complicated split of investment proceeds between management and the PE firm. A higher-level analysis, like this one, generally just lumps them all into one bucket.
  6. Calculate MoIC by dividing Equity Value by Investment.
  7. Above the IRR section, add a row for the number of years elapsed. This will make it easier to calculate the IRRs. You can enter the years elapsed manually or you can use the Year function:

    Years Elapsed = Year(current column date) - Year(PF 2016 date).
    (You can refer to the date row at the top of the returns schedule).

  8. Calculate IRR with the following formula:

    IRR = MoIC ^ (1/ Years Elapsed) - 1

Next Steps

Congrats, you’ve completed the training LBO!

Try the rest of our private equity modeling tutorials:

  1. Ability-To-Pay Analysis
  2. LBO Value Creation
  3. Dividend Recap Guide
  4. Advanced LBO Modeling
  5. Private Equity Modeling Guide