Leveraged Buyout (LBO) Model Excel Template

Start with sources: sponsor equity plus three debt tranches. Select the four cells and click AutoSum to total them at $450.00M. Then build the uses side: the purchase of existing equity ($441.50M, which is the enterprise value minus the $8.50M closing fee), plus $5.00M of financing fees and $3.50M of advisory costs.

The two totals must match exactly. Add a check cell that subtracts uses from sources and shows zero. If someone edits a fee later, the check tells you immediately that the deal no longer balances.

Total debt is 4.5x EBITDA, split into Term Loan A at 2.5x ($112.50M), Term Loan B at 1.5x ($67.50M), and mezzanine at 0.5x ($22.50M). Each tranche is EBITDA times its multiple, and the sponsor equity is the price minus total debt.

Select the multiple cells and press Ctrl+1. On the Custom tab, enter a number format that shows the digit with one decimal followed by an x, so 10 appears as 10.0 x. This workbook writes the tranche multiples inside formulas, so a good upgrade for your own Leveraged Buyout Model Excel file is to give each multiple its own yellow input cell.

Each year has two repayments. Mandatory amortization is 10% of Term Loan A, which is $11.25M every year. The optional sweep takes 80% of the free cash flow left after that mandatory payment. In year 1, $22.00M minus $11.25M leaves $10.75M, and 80% of that is $8.60M.

Click the sweep cell and press F2 to see the two source cells highlighted. Total repayment in year 1 is $19.85M, so debt drops from $202.50M to $182.65M. Over five years the company repays $128.85M, about 64% of the original debt.

Notice that the remaining 20% of free cash flow is not tracked as cash. The model compares gross debt at exit, which is a conservative simplification. Also, free cash flow is typed in, so make sure it is already after interest and taxes when you use your own numbers.

Divide ending debt by that year's EBITDA to get the leverage ratio: 3.69x, 2.94x, 2.24x, 1.60x, and 1.02x. Apply the same custom x format here. In the sample file this row still uses a dollar format, which is a small mistake to fix.

Select the five ratios and open Home > Conditional Formatting > Color Scales. Make the lowest value green and the highest red. The row now fades from red to green, so a lender or committee member sees the deleveraging story at once. Note that year 5 lands at 1.02x, just above the sheet's target of 1.0x.

At exit, multiply year 5 EBITDA ($72.50M) by the exit multiple (10.5x) to get $761.25M of enterprise value. Subtract remaining debt of $73.65M, and the sponsor's equity is $687.60M. Divide by the $247.50M invested and MoIC is 2.78x.

For IRR, raise MoIC to the power of one-fifth and subtract one, which gives 22.69%. Open Formulas > Evaluate Formula to watch Excel work through it. This shortcut only works when there are no dividends before exit. If cash moves in between, list dated cash flows and use IRR or XIRR instead.