This leveraged buyout model calculator can be used to provide a quick outline indication of the viability and investor return of a five year leveraged buyout (LBO) transaction.
A leveraged buyout involves purchasing a business with a combination of debt and equity finance. The purpose of using debt is to increase the financial leverage to try and improve the return on the investment made by the equity shareholders.
Using the Leveraged Buyout Model Calculator
This LBO model assumes the transaction occurs at the start of year 1 and calculates the return attributable to equity investors at the end of year 5.
This calculator uses an iterative process in its calculations. To avoid a circular argument error the formula calculations option in Excel must be set to ‘Automatic‘ and ‘Enable iterative calculation’.
The leveraged buyout model calculator is used as follows.
1. Enter the Sales for the Last 12 Months (LTM)
Enter the value of sales revenue for the 12 months prior to the LBO transaction.
2. Enter the EBITDA Margin %
Enter the historic EBITDA margin percentage for the business. EBITDA means earnings before interest, tax, depreciation and amortization.
The leveraged buyout model calculator calculates the EBITDA based on the last 12 months sales and the historic EBITDA margin percentage.
Enterprise Value & Funding
3. Enter the EBITDA Multiple
A business is often purchased on a multiple of its EBITDA. The multiple will depend on the type of industry in which the business operates. Estimates can be found by looking at industry data and, if available, recent purchase transactions in the same industry.
The leveraged buyout model calculator calculates the enterprise value which is the value of the net operating assets of the business and represents the acquisition price in our LBO model.
The multiple entered at this stage is also used as the year 5 exit multiple later in the calculator.
4. Enter the Debt Funding %
Enter the percentage debt funding which is to be used in the LBO transaction. The LBO calculator works out the level of debt required to fund the transaction based on the enterprise value and the percentage of debt entered.
The equity value of a business is calculated by adding cash and deducting debt from the enterprise value.
Equity value = Enterprise value + Cash - Debt
For simplicity, the calculator calculates the equity value and equity percentage on the basis that the initial cash balance is zero.
5. Enter the Repayment %
The repayment percentage is used to calculate the minimum annual debt repayment allowed. For example, if the debt is 600,600 and the repayment percentage is 2%, then the minimum principal repayment on the debt each year is 12,102 (600,600 x 2%).
The buyout leverage model calculator will check to see that the available free cash flow from the business is sufficient to meet this minimum repayment and show an error message if this is not the case.
6. Enter the Interest Rate
Enter the weighted average interest rate on all debt used to fund the transaction.
7. Enter the Sales Growth Rate
Enter the annual sales growth rate. The calculator applies the growth rate to each of the next five years and calculates the sales level for each year.
8. Enter the EBITDA Margin %
Enter the EBITDA margin percentage for each of the five years. The EBITDA should encompass all costs and expenses other than interest, tax, depreciation and amortization.
9. Enter the Depreciation and Amortization %
Enter the depreciation & amortization percentage. The leveraged buyout model calculator calculates the depreciation and amortization expense as a percentage of sales revenue.
10. Enter the Income Tax Rate
Enter the tax rate to be applied. The tax rate is applied to the income before tax to estimate the income tax expense for each year.
The calculator determines the forecast net income of the business.
Free Cash Flow
The free cash flow is calculated by adjusting the net income of the business for changes in working capital, capital expenditure (capex) and expenditure on intangibles.
For each of these items enter the amount the business intends to spend as a percentage of the sales revenue of the business.
The leverage buyout model calculator calculates the free cash flow of the business.
It should be noted in this model that all free cash flow is used to make debt repayments. In the event that available free cash flow exceeds the amount necessary to clear the debt balance the excess is accumulated and adjusted for in the calculation of the year 5 equity value. No interest is allowed for on this excess cash balance.
Debt and Interest
The debt and interest summary sets out the movements on the principal debt balance after allowing for the debt repayments.
The calculator also checks to ensure that the available free cash flow is sufficient to make the minimum debt repayments and shows an error message if this is not the case. Adjustments to the input parameters must me made to remove the error messages before proceeding.
The summary also show the amount of interest paid on the debt based on the interest rate entered and the average of the beginning and ending debt balances for the period.
Exit Valuation and Return
The final section of the leveraged buyout model calculator shows at the end of year 5 the calculated EBITDA, the enterprise value based on the multiple entered in Step 3 above, and the equity value after adjusting for any remaining debt and excess cash balances.
Finally the original equity investment and the year 5 equity value are used to calculate the shareholder return on investment which, in this calculator, is represented by the internal rate of return (IRR).
For the LBO transaction to be viable the return should be greater than that required by the investors which will depend on this risks involved.
As an indication of the level of return required, for a 5 year deal a return of 2x the original investment equates to a 14.9% return, 3x equates to 24.6% and 4x to a 32.0% return.
Leveraged Buyout Model Calculator Download
The leveraged buyout model calculator is available for download in Excel format by following the link below.
Users use this leveraged buyout model excel calculator at their own risk. We make no warranty or representation as to its accuracy and we are covered by the terms of our legal disclaimer, which you are deemed to have read. This is an example of an leveraged buyout financing calculator that you might use when estimating how to calculate the investor return from a leveraged buyout. It is purely illustrative of an Excel leveraged buyout calculator. This is not intended to reflect general standards or targets for any particular company or sector. If you do spot a mistake in the LBO model Excel calculator, please let us know and we will try to fix it.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Plan Projections. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a BSc from Loughborough University.