01 — Sources and uses
Sources and uses: what is bought and who pays
The sources and uses table is the transaction in one block: what the purchase costs, and how much of it debt, sponsor equity and rolled equity each fund.
Build uses from the purchase price (entry multiple × entry EBITDA, or a stated price), the refinancing of existing debt and the transaction fees; build sources from each debt tranche and the sponsor equity, with equity as the balancing figure so that sources always equal uses.
Quick rules
- Enterprise value at entry = entry multiple × the EBITDA the multiple is quoted on (last twelve months, or the current-year figure the brief states).
- Purchase equity = enterprise value − net debt at entry, whatever is refinanced. Refinancing existing debt is a separate use on its own row, never netted into the purchase price.
- Uses: purchase of equity, refinancing of existing debt, transaction and financing fees, and cash to the balance sheet if the deal funds a minimum cash balance.
- Sources: each debt tranche at its sized amount, rolled management equity if any, and sponsor equity as the plug.
- With equity as the balancing figure, total sources − total uses = 0 is true by construction and proves nothing. Check the equity instead: compare the sponsor cheque to the minimum equity contribution the terms require, as a share of total sources, and flag a shortfall.
How to fix it
- Worked example (invented): entry EBITDA 100 at 8.0x gives enterprise value 800. Existing net debt of 150 is refinanced and fees are 20, so uses total 820. With 500 of new debt, sponsor equity is the plug: 820 − 500 = 320.
- Reference the entry multiple and entry EBITDA from the assumption block; the price must move when either changes.
- Put fees on their own rows split between transaction fees (expensed or capitalised as the brief says) and financing fees (amortised over the debt life).
- Keep the table in the same order on every model you build so a reviewer's eye lands on the plug immediately.
02 — Financing structure
Financing structure and leverage
Debt is sized in turns of EBITDA and layered by seniority. Each tranche has its own rate, term, amortisation and repayment priority.
Size each tranche as a multiple of entry EBITDA, record its interest rate, term, mandatory amortisation and whether it participates in the cash sweep, and order the tranches by seniority so the schedule repays them in that order.
Quick rules
- Total leverage = total debt ÷ entry EBITDA; senior leverage counts only the senior tranches. Both are outputs to read back against what lenders would accept.
- Senior term debt amortises and sweeps first; subordinated or mezzanine debt usually has no amortisation and a higher rate, often part cash and part payment-in-kind.
- A revolver is sized as a facility limit, drawn only when cash falls short, and repaid before any optional prepayment of term debt.
- Interest rates are stated as a margin over a base rate or as a fixed coupon; the model should show both parts so a rate assumption can change on its own.
- Financing fees are capitalised and amortised over each tranche's term; their amortisation is a non-cash charge in the income statement.
How to fix it
- Worked example (invented): entry EBITDA 100, senior term loan 3.5x = 350 at 7.0% amortising 5% a year, subordinated notes 1.5x = 150 at 11.0% with no amortisation. Total leverage 5.0x, senior leverage 3.5x.
- Lay the tranches out as a table with one row per term (size, multiple, rate, tenor, amortisation, sweep participation) so the debt schedule can reference each field.
- Show cash and payment-in-kind interest on separate rows when a tranche accrues part of its coupon.
- Read total leverage and interest cover in year one against the assumption block: a structure the cash flows cannot service is a modelling error before it is a deal problem.
03 — Debt schedule
The debt schedule: one roll-forward per tranche
The debt schedule tracks each tranche from entry to exit: opening balance, mandatory repayment, optional repayment from the sweep, closing balance, and the interest on it.
Roll each tranche forward as opening + drawdown − mandatory amortisation − optional repayment = closing, calculate interest on the stated balance basis, and feed the closing balances to the balance sheet and the interest to the income statement.
Quick rules
- Closing balance = opening balance + drawdowns − mandatory amortisation − optional repayment, never below zero.
- Interest = rate × opening balance under the opening-balance convention, or × the average of opening and closing under the average-balance convention, which creates a circular reference.
- Total interest expense in the income statement is the sum of every tranche's cash interest plus payment-in-kind interest plus financing-fee amortisation.
- Payment-in-kind interest is added to the tranche's balance, not paid in cash, so it appears in the roll-forward as a drawdown-like line and is added back in the cash flow statement.
- The sum of closing balances across tranches is the total debt on the balance sheet in every period.
How to fix it
- Worked example (invented): term loan opening 350 at 7.0% with mandatory amortisation of 17.5 and an optional repayment of 30. Closing = 350 − 17.5 − 30 = 302.5. Interest on the opening balance = 350 × 7.0% = 24.5.
- Keep the same five rows (opening, drawdown, mandatory, optional, closing) for every tranche so the schedule reads as a repeated block.
- If the case requires average-balance interest, enable iterative calculation deliberately, add a circularity switch, and see the circularity section of the three-statement guide.
- Reconcile total debt movement in the schedule to the financing section of the cash flow statement in every period.
04 — Amortisation and sweep
Mandatory amortisation and the cash sweep
Mandatory amortisation is a scheduled repayment that must be paid; the cash sweep applies the cash left over after it to prepay debt in order of seniority.
Pay mandatory amortisation from operating cash flow first, hold the minimum cash balance, and apply the stated percentage of any remaining cash to optional prepayment of the most senior sweep-eligible tranche, moving to the next tranche only when the first is repaid.
Quick rules
- Cash available for optional repayment = opening cash + cash flow after interest, tax, capex and working capital − mandatory amortisation − minimum cash.
- Optional repayment on a tranche = MIN(opening balance − mandatory amortisation, MAX(0, cash available × sweep percentage)).
- Tranches sweep in seniority order: the cash left after repaying the first eligible tranche in full passes to the next.
- If cash available is negative, nothing is prepaid and the revolver draws the shortfall; the sweep formula must never produce a negative repayment.
- Mandatory amortisation continues on the original schedule after prepayments unless the terms say prepayments reduce it, capped at what is left: mandatory = MIN(scheduled amount, opening balance), so a tranche the sweep has repaid early never goes negative.
How to fix it
- Worked example (invented): opening cash 40, cash flow after debt service items 55, mandatory amortisation 17.5, minimum cash 25, sweep 100%. Cash available = 40 + 55 − 17.5 − 25 = 52.5, so the term loan is prepaid by 52.5 and closing cash is 25.
- Put the sweep percentage and the minimum cash balance in the assumption block and reference them; never embed either in the MIN and MAX formulas.
- Build the sweep in visible steps (cash available, repayment on tranche one, cash remaining, repayment on tranche two) rather than one nested formula.
- Add a check that no tranche balance goes negative and that closing cash never falls below the minimum.
05 — Exit
Exit assumptions: multiple, year and net debt at exit
Exit equity value is exit enterprise value less net debt at exit. Each of the three inputs deserves its own assumption cell and its own sanity check.
Calculate exit enterprise value as the exit multiple × exit-year EBITDA, subtract net debt at the exit date from the debt schedule, and state the exit year and any transaction costs at exit as explicit assumptions.
Quick rules
- Exit multiple defaults to the entry multiple unless the case gives a reason for expansion or contraction; a multiple above entry needs a written justification.
- Exit-year EBITDA is the figure the multiple is quoted on, from the same definition as entry EBITDA.
- Net debt at exit = sum of tranche closing balances − cash at the exit date, both from the schedule, never typed.
- Exit costs (advisers, management incentive plan payouts) reduce the proceeds to the sponsor and are stated separately.
- Show the exit-year leverage and the share of the debt repaid since entry: deleveraging is often a large source of returns and should be visible.
How to fix it
- Worked example (invented): exit-year EBITDA 130 at 8.0x gives exit enterprise value 1,040. Net debt at exit of 250 leaves equity value of 790 before exit costs.
- Reference the exit-year column of the debt schedule with an INDEX on the exit-year assumption so changing the exit year moves every figure.
- Bridge entry equity to exit equity in three lines (EBITDA growth, multiple change, deleveraging) so the reviewer sees where the return comes from.
- Test the exit multiple one turn below entry and confirm the model still runs and the returns still make sense.
06 — Returns
MOIC and IRR
Multiple of invested capital tells you how many times the equity came back; IRR tells you how fast. A model reports both, from the same cash flows.
Build a sponsor cash flow line with the equity investment as a negative at entry, any interim dividends, and the exit equity proceeds as a positive in the exit year, then calculate MOIC as total proceeds ÷ total invested and IRR with the IRR function on that line.
Quick rules
- MOIC = total cash returned to the sponsor ÷ total cash invested. It ignores timing.
- IRR is the rate that discounts the sponsor cash flows to zero; use IRR on annual columns or XIRR on dated cash flows.
- With one investment and one exit, IRR = MOIC ^ (1 ÷ years) − 1, a check that catches most spreadsheet mistakes in the returns block.
- Interim dividends or recapitalisations raise IRR more than MOIC; show them on the cash flow line so their effect is visible.
- Report returns for a range of exit years and multiples in a sensitivity table centred on the base case.
How to fix it
- Worked example (invented): equity invested 320, exit equity proceeds 790 after five years, no interim dividends. MOIC = 790 ÷ 320 = 2.47x. IRR = 2.47 ^ (1 ÷ 5) − 1 ≈ 19.8%.
- Sign the sponsor cash flow line consistently: investments negative, proceeds positive. An IRR that returns an error usually means every value has the same sign.
- Reference the exit proceeds from the exit block and the investment from sources and uses; the returns line should hold no typed numbers.
- Add the closed-form IRR check next to the IRR function and flag a difference above a rounding tolerance.
07 — Review
Common LBO errors and how to catch them
Most LBO errors are plumbing: a plug that does not plug, a sweep that repays more than the balance, an exit that reads the wrong year.
Before handing over, confirm the sponsor equity meets the minimum contribution, every tranche rolls forward without going negative, interest matches the stated basis, cash never breaches its minimum, and the exit reads the exit-year column of the schedule.
Quick rules
- Sponsor equity typed as a number instead of the balancing figure, so a change to the price breaks the table.
- Optional repayment that exceeds the remaining balance, or that runs while cash is below the minimum.
- Interest calculated on the closing balance, or on the average balance without iteration enabled, so the schedule shows an error or a stale value.
- Payment-in-kind interest deducted from cash flow or omitted from the tranche balance.
- Exit net debt typed from the last column rather than referenced from the exit-year column, so changing the exit year leaves the returns unchanged.
- IRR with all cash flows of the same sign, or MOIC that ignores interim distributions.
How to fix it
- Add a checks block: sponsor equity at or above the minimum contribution; no tranche balance below zero; closing cash ≥ minimum cash; total debt in the schedule equals the balance sheet; the closed-form IRR matches the IRR function.
- Change the exit year by one and confirm exit EBITDA, net debt and the returns all move.
- Trace one full path with the formula bar from the entry multiple to the IRR and confirm no typed value interrupts it.
Final pass
Checks before handover
- 01Sources equal uses, with sponsor equity as the balancing figure and fees on their own rows.
- 02Each tranche has its size, rate, tenor, amortisation and sweep participation in one table the schedule references.
- 03Every tranche rolls forward opening to closing and interest follows the stated balance basis.
- 04Mandatory amortisation is paid first, the minimum cash is held, and the sweep repays in seniority order.
- 05Exit value reads the exit-year column of the schedule and the exit assumptions are explicit.
- 06The sponsor cash flow line is signed consistently and MOIC and IRR reconcile to the closed-form check.
- 07A checks block flags the plumbing failures above before delivery.
StreetCred practice cases assess submitted workbooks and return cell-level feedback.
Start the free diagnostic →