About Brian DeChesare
Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.
LBO Valuation: The Power of Middle-School Math to Reverse a Model
In an “LBO valuation,” you *reverse* the standard leveraged buyout model setup and *back into* the purchase price based on the assumed exit multiple, exit year, and cash flows in the holding period; you can use Goal Seek or VBA to set this up, but you can also simplify the model and do it entirely with normal Excel formulas.
LBO Valuation: In an “LBO valuation,” you *reverse* the standard leveraged buyout model setup and *back into* the purchase price based on the assumed exit multiple, exit year, and cash flows in the holding period; you can use Goal Seek or VBA to set this up, but you can also simplify the model and do it entirely with normal Excel formulas.
In LBO modeling tests and case studies, you might be asked to create an “LBO valuation,” in which you value a company based on the maximum price that a PE firm can pay to achieve a minimum return.
For example, how much can the PE firm pay if it wants to earn a 20% annualized return over 5 years?
Since you are solving for the maximum price, the LBO valuation normally sets the “floor” for a company’s value in a deal.
If you have a traditional LBO model, you could use Goal Seek with the IRR in the exit year and back into the purchase price or purchase multiple like this:
But if you have more time, you can also use a few Excel formulas to calculate the purchase multiple based on the targeted IRR and exit year in a more robust way:
We’ll walk through the steps in this process below, starting with the “Before” file and concluding with the “After” file:
Files & Resources:
LBO Valuation, Step 1: Make Model Modifications
Start by adding fields for the Targeted IRR, Exit Year, Exit Equity Proceeds, and Required Investor Equity in the top “Assumptions” area of a standard LBO model:
You can apply Data Validation to the Targeted IRR and Exit Year based on your model setup and the number of years in the holding period.
You can use XLOOKUP to bring in the Exit Equity Proceeds from the bottom of the model in the returns area:
LBO Valuation, Step 2: Calculate the Required Investor Equity
Next, you “back into” the Investor Equity required to earn the specified IRR over this time frame.
To do this, you can use the Compound Annual Growth Rate (CAGR) formula:
CAGR = (Ending Value / Starting Value) ^ (1 / # Years) – 1
And then you can use algebraic manipulation as follows to solve for the Starting Value:
In Excel, the formula looks like this:
LBO Valuation, Step 3: Back Into the Purchase Enterprise Value and Purchase Multiple
Note that the Required Investor Equity here is NOT the same as the Purchase Equity Value.
The Investor Equity is a component of the Sources side of the Sources & Uses schedule, so you must calculate Total Sources first by adding the Debt used in this deal to the Required Investor Equity.
You’ll also have to add a few additional lines to support these calculations:
Then, since Total Sources = Total Uses, and Total Uses = Purchase Enterprise Value + Fees + Minimum Cash, you can say:
Purchase Enterprise Value = Total Uses – Fees – Minimum Cash
And then you can divide this by the EBITDA as of the deal announcement date to determine the required purchase multiple:
You can test this setup by plugging in different values, such as a 25% IRR over 5 years or a 35% IRR over 4 years, and entering the calculated purchase multiple into the assumption at the top.
If it produces the 25% or 35% IRR you are seeking in the returns area at the bottom, the formula works.
LBO Valuation: How to Make the IRR and Exit Year the Drivers and Fix Circular References
If you want to make this setup more robust by making the Targeted IRR and Exit Year model drivers, you can link the Purchase Multiple at the top to the calculated or required multiple.
This produces an immediate circular reference:
This occurs because of the fees: The transaction fees depend on the Purchase Equity Value, but the Purchase Equity Value flows from the Purchase Enterprise Value, and these fees determine the Purchase Enterprise Value:
Purchase Equity Value –> Transaction Fees –> Total Sources/Uses –> Purchase Enterprise Value –> Purchase Equity Value
The simplest fix is to make the Transaction Fees a constant $10 million so they do not change with the deal price (which is reasonable within a narrow price range):
The Financing Fees and Minimum Cash should not have any circular dependencies.
Footnotes, Fine Print, and Limitations
As noted above, any circular reference in this model disrupts the calculations and the LBO valuation setup.
So, you should remove any circular references in the interest calculations, returns calculations, and other schedules before implementing this.
Also, if the model includes a dividend recap, bolt-on acquisitions, or additional equity investments/distributions in the holding period, the simple CAGR formula for IRR will not work.
This “trick” relies on the same techniques used to make quick IRR estimates and answer LBO interview questions, and it has the same limitations.
Specifically, any cash inflows or outflows *during* the holding period complicate the IRR calculation and make it more than just the simple CAGR formula.
In theory, you could try to work around this by incorporating their effects into the formula, but in practice, it’s probably not worth the time/effort.
In complex cases like these, we recommend using Goal Seek or Goal Seek + VBA to automate the process and refresh the model whenever a key assumption changes.
