How to Build an LBO Returns Model, Year by Year

A buyout returns model is built in seven steps: fix the entry, balance the sources and uses, set the operating case, build the debt schedule year by year, cut the circularity, build the exit, and run the checks. On this invented structure the senior term loan falls from Rs 9,00,00,00,000 to Rs 3,91,85,00,000 over five years, and net debt at exit is Rs 7,91,85,00,000.

Something small enough to hold in one hand comes first. A couple buys a second-hand tempo to run deliveries for a cluster of shops in one market. The couple put in Rs 3,00,000 of what they had saved and borrow Rs 9,00,000 against the tempo itself. Every month the freight money comes in, the diesel and the driver and the servicing are paid, and every rupee that is left over goes straight to the lender. That was the arrangement, so nothing is kept back for a rainy day.

Four years later they want to know one thing: how much of the Rs 9,00,000 is gone, and what is the tempo worth now? Answering that takes a table. Not a clever table. A boring one, with a row for each year and a column for what came in, what interest was charged, what was repaid, and what is still outstanding. The entire craft of a buyout returns model is that table, built carefully, on a company instead of a tempo.

The procedure for building it is worked all the way through below on one invented structure, so that every figure lands in view. Sthira Capital Partners, an invented sponsor, has bought Sankalp Industrial Systems Limited and holds it for five years. The structure itself, the demands of each lender and the source of the eventual return are covered separately. Building the table starts at the point where all of that has been agreed and somebody now has to turn it into numbers.

What are the seven steps, and why does the order bind?

Seven, and they are not a suggestion. Fix the entry. Balance the sources and uses. Set the operating case. Build the debt scheduleA year by year table of borrowing, interest, repayment and closing balances. year by year. Cut the circularity. Build the exit. Run the checks. Steps one and two settle what was paid and what was borrowed. Step three settles the cash. Only after all three does any balance actually move.

The debt schedule is the interesting part, so people who have never built one of these assume it comes first. It cannot. The schedule's whole purpose is to work out how much cash is spare at the end of each year, and that figure is called cash available for debt serviceEBITDA less interest, cash tax, capital expenditure and the working capital movement.. Cash available for debt service has five inputs: earnings before interest, tax, depreciation and amortisation (EBITDA), interest, cash tax, capital expenditure, and the movement in working capital. Two of those five are decided nowhere except in the operating case. A schedule started before the operating case is written down is guessing two of five inputs, and every closing balance in the table is then wrong by an amount nobody can see.

Silence is what makes the error dangerous. A schedule built on a guessed capital expenditure line still adds up. Every row still ties to the row above it. Nothing is flagged. The table is simply about a different company from the one that was bought.

What do steps one and two hand over, and what is left to do?

Steps one and two hand over four numbers and two contracts, and all six are taken as given here. The entry is 8.50 times Year 0 EBITDA of Rs 2,88,00,00,000, being an enterprise value of Rs 24,48,00,00,000, and after the claims against the business are settled the price for all the equity is Rs 20,08,00,00,000. Total uses are Rs 27,20,00,00,000 and total sources are the same Rs 27,20,00,00,000, with the sponsor's own equity of Rs 12,00,00,00,000 as the balancing line. How that statement is built as an object, and why each item sits on the list it sits on, is covered separately.

The two contracts are what matter for everything that follows. Borrowing at completion is Rs 9,00,00,00,000 of senior term loan at 9.50 per cent, amortising entirely by cash sweepA term applying all spare cash to repaying borrowing rather than retaining it., and Rs 4,00,00,00,000 of subordinated notes at 13.00 per cent, bulletBorrowing repaid in one instalment at maturity, so no repayment happens along the way. borrowing with no amortisation and no sweep at all. The debt schedule has to obey those two contracts and nothing else, and everything in the table below is a consequence of them.

Both rates are this invented structure's own contracted rates, and they carry no implication that a lender would provide these amounts on these terms or that a business of this size could carry this obligation. The two rates are arithmetic inputs to a worked example rather than facts about the cost of borrowing anywhere.

How does the buyer's own operating case differ from the company's forecast?

In exactly two lines, and the discipline of that is worth more than the two changes themselves. Sthira Capital Partners underwrote its own operating case rather than adopting the company's standalone forecast. Underwriting a case that way is normal, and the honest way to present one is to write down every single line where it differs. Here the list is short.

Revenue is unchanged. EBITDA is unchanged, at Rs 3,16,80,00,000, Rs 3,45,60,00,000, Rs 3,74,40,00,000, Rs 4,03,20,00,000 and Rs 4,32,00,00,000 in Years 1 to 5. Depreciation and amortisation are unchanged, at Rs 52,80,00,000 rising to Rs 72,00,00,000. Nothing above the EBITDA line moves at all. Because revenue and EBITDA are untouched, nothing that follows can be resting on an operating improvement the buyer has not been shown to make.

The last column is the only place the two changes become one number, so check it. Capital expenditure across the five years falls from Rs 7,22,00,00,000 in the standalone case to Rs 4,50,00,00,000, a difference of Rs 2,72,00,00,000. The working capital movement falls from Rs 90,00,00,000 to Rs 60,00,00,000, a difference of Rs 30,00,00,000. Together they free Rs 3,02,00,00,000 of cash across the hold, and every rupee of it will end up in the sweep column of the schedule.

The kind of changes these are matters too. Both are spending decisions the buyer controls directly. Neither requires a customer to behave differently, a price to hold, or a market to grow. A case built on spending decisions is far easier to underwrite than one that needs the margin to expand. Each change can be pointed at and tested against what happens if it does not hold, so a reader can audit it too.

What are the five lines that make up one year of the schedule?

Five, in a fixed order, and the order is the whole trick. Year two is year one with a different opening balanceThe borrowing outstanding at the start of a year, before that year's repayment. and nothing else, so one year done is all five done.

Work Year 1 through by hand once and the table stops being a black box. Interest first: Rs 9,00,00,00,000 of senior at 9.50 per cent is Rs 85,50,00,000, and Rs 4,00,00,00,000 of notes at 13.00 per cent is Rs 52,00,00,000, so total interest is Rs 1,37,50,00,000. Profit before tax is Rs 3,16,80,00,000 less Rs 52,80,00,000 of depreciation less that interest, being Rs 1,26,50,00,000. Cash tax at 25.0 per cent is Rs 31,62,00,000, and that rate is this company's own assumed effective rate, invented and labelled as an assumption everywhere it appears.

Now the line that matters. Cash available for debt service is Rs 3,16,80,00,000 of EBITDA, less Rs 1,37,50,00,000 of interest, less Rs 31,62,00,000 of cash tax, less Rs 90,00,00,000 of capital expenditure, less Rs 12,00,00,000 of working capital movement. The remainder is Rs 45,68,00,000. All of it goes to the senior loan, so the senior loan closes the year at Rs 8,54,33,00,000. Note that depreciation reduces the tax bill without ever leaving the bank account, so it appears in the tax line and nowhere else.

One detail in that ladder repays attention. The working capital movement is a small line, only Rs 12,00,00,000 against EBITDA of Rs 3,16,80,00,000, or under four per cent. Being small, and not feeling like a payment, the movement is also the line people forget. Left out, the first year's sweep reads Rs 57,68,00,000 instead of Rs 45,68,00,000, more than a quarter too high. Every later opening balance is built on the one before it, so that error grows rather than staying where it started.

What does the whole five year schedule look like when it is printed?

Like this, with all five years printed in full. A schedule visible only one row at a time is not a schedule. The schedule is set out as two tables purely so the figures fit. The first holds what happened to the year's cash, the second what happened to the balances. Both tables hold the same five rows.

The subordinated notes are absent from both tables for a reason: they never move. The notes sit at Rs 4,00,00,00,000 in every one of the five years, so closing net debt is always the closing senior loan plus that same Rs 4,00,00,00,000. No cash line appears either. Nothing is retained.

Now read the sweep column downwards and something odd happens. The sweep runs Rs 45,68,00,000, Rs 71,73,00,000, Rs 99,64,00,000, Rs 1,29,54,00,000 and Rs 1,61,57,00,000. The column more than triples. Meanwhile EBITDA is rising by a flat Rs 28,80,00,000 every single year, a smaller and smaller percentage each time. The sweep grows more than three times faster than the earnings that fund it, and the extra comes entirely from interest falling as the balance falls.

The self-reinforcing mechanic at the centre of the whole structure sits in that column, and it is worth saying in plain words. Every rupee repaid this year is a rupee that is not charged interest next year. The interest saved becomes more cash available. The extra cash available becomes a bigger repayment. Interest runs from Rs 1,37,50,00,000 in Year 1 down to Rs 1,04,57,00,000 in Year 5, a fall of Rs 32,93,00,000, and every rupee of that fall ends up in the sweep. The same thing happens on a home loan when somebody makes an extra payment early: the benefit is not the payment, it is every month of interest that payment removes.

What exactly is a cash sweep, and which borrowing receives it?

A cash sweep is a term in the loan agreement that says spare cash does not stay in the company. Spare cash is applied to repaying the borrowing instead. The couple with the tempo accepted exactly that arrangement: whatever is left after diesel and the driver goes to the lender, not into a savings account.

Three questions settle how a sweep behaves in the arithmetic, and this structure answers all three the simple way. What is swept? All of the cash available for debt service, a hundred per cent of it. How much is retained? None. Which borrowing receives it? The senior term loan, and only the senior term loan.

The senior loan taking every rupee of the sweep is why one balance falls in every year of the table and the other never moves at all. The subordinated notes are bullet borrowing. The notes receive their 13.00 per cent a year in cash and nothing else, and the whole Rs 4,00,00,00,000 stays outstanding until it matures. From the schedule's point of view the notes are simply a fixed charge of Rs 52,00,00,000 a year that never gets smaller. The lender waits longer and stands behind somebody else, and that is exactly why the notes cost 350 basis points more than the senior loan.

There is a practical consequence in this that a reader can carry away from the diagram. Because the sweep only reaches one tranche, the falling total in the last column is not spread evenly across the borrowing. Every rupee of the Rs 5,08,15,00,000 fall in borrowing over five years came out of one loan. A schedule where both tranches shrank while only one of them was swept would show a defect without any arithmetic at all.

Where is the circularity, and why does it have to be cut?

The one genuinely difficult thing in this model comes here, and everything else really is bookkeeping. The order of the five lines is the place to look. Interest is charged on a balance. The cash left over after interest determines the sweep. The sweep determines the balance. Following that round arrives back at the start.

The chain closing on itself is a circularityA calculation that depends on its own output, here between interest, the sweep and the balance., and in a spreadsheet it announces itself immediately: three cells refer to each other and the file either refuses to calculate or puts up a warning. A model that has not decided how to cut that chain either fails to calculate at all, or produces an answer nobody can check by hand.

The loop is not a flaw in anybody's thinking. The loop is a real feature of the arrangement. The borrowing genuinely is being repaid through the year, so the balance interest is charged on genuinely is falling while the interest is accruing. The loop is the arithmetic being honest about that. Somebody has to decide where to break the loop, write the decision down, and apply it everywhere.

Which way does this model cut the loop, and what does the choice cost?

There are two ways, and this schedule uses the first one.

Way one, used here: charge interest on the opening balance. With that done, the loop disappears completely. Interest for the year no longer depends on anything that happens during the year, so each year computes in a single pass, top to bottom, and there is nothing to iterate. Better still, every figure in the table becomes checkable by hand in about fifteen seconds. Year 2 shows it. The opening senior balance is Rs 8,54,33,00,000. At 9.50 per cent that is Rs 81,16,00,000. Adding Rs 52,00,00,000 on the notes gives Rs 1,33,16,00,000, exactly the printed figure. The same check on any of the five years ties.

Way two: charge interest on the average balanceThe mean of the opening and closing balances, a more accurate base for interest and the source of the loop., meaning the mean of the opening and the closing balances. Borrowing that is repaid through the year genuinely does carry a lower average balance than its opening balance, so way two is more accurate. The closing balance is one of the things being worked out, so way two also reintroduces the loop. The loop is then resolved either by turning on iterative calculationA spreadsheet setting that resolves a loop by repeating passes until the change falls below a tolerance. in the spreadsheet, or by running a handful of passes by hand until the figures stop moving.

Neither is wrong. The two give different answers and both look equally finished, so failing to say which one was used is the actual mistake.

A difference nobody can size is a difference nobody can argue about, so size this one now. On Year 1 the opening senior balance is Rs 9,00,00,00,000 and the closing is Rs 8,54,33,00,000, so the average is about Rs 8,77,16,00,000. Interest on that average would be about Rs 1,35,33,00,000 rather than Rs 1,37,50,00,000. The choice moves roughly Rs 2,17,00,000 of interest, and it moves it in a known direction. The balance is falling all year, so charging on the opening balance always overstates interest slightly. The schedule here uses the opening balance throughout, and mixing the two conventions is precisely the failure described further down.

How is the exit built, and what has to match the entry?

The exit is built exactly the way the entry was built, only backwards, and this is the step where models most often quietly stop meaning anything. Year 5 EBITDA is Rs 4,32,00,00,000. Applied at the same 8.50 times the buyer paid on the way in, the exit enterprise value is Rs 36,72,00,00,000. Net debt of Rs 7,91,85,00,000 is the last row of the schedule built above. Taking it off leaves exit equity of Rs 28,80,15,00,000.

The multiple is identical at both ends by assumption. The structure assumes no expansion in the multiple at all, so no part of the eventual return can come from the market simply paying more for the same earnings. Where the return did come from is covered separately; here the figures are that the structure produced 2.40 times the money and 19.14 per cent a year over five years, and those two are outputs of the schedule rather than inputs to it.

The rule that binds the exit is that the exit bridgeThe walk from exit enterprise value back to equity, using the same lines as the entry. must use the same lines as the entry bridge, including the ones that are now nil. That is not pedantry. Three lines that carried real figures at entry are zero at exit, and each of them is zero for a stated reason. The minority interest is nil because it was bought out at completion. Cash is nil because nothing is retained; the sweep takes it all. Non-operating assets are nil because they were sold at their carrying value on day one and appear as a source of funding rather than as an asset the buyer still holds.

If any one of those three lines is silently dropped from the exit bridge rather than carried at nil, the walk still adds up and the mistake is invisible. Write them out, put a nil against each with its reason beside it, and the check becomes something a colleague can do in a minute rather than something only the person who built the model can do.

Which seven checks show that a finished model is sound?

Seven, all mechanical, all runnable on figures that are already in the tables above, and together they take a few minutes rather than a review. None of them requires judgement. Judgement is for the assumptions, and the seven checks are for the arithmetic.

Run them on the schedule above and here is what each one produces. Check one: sources equal uses at Rs 27,20,00,00,000 on both sides, to the rupee. Check two: in every year the closing senior balance equals the opening balance less the sweep, and the sweep never exceeds the balance outstanding. A sweep larger than the loan would mean the model repaid borrowing that was not there. Check three: interest ties to the stated rates on the stated opening balances in all five years, at Rs 1,37,50,00,000, Rs 1,33,16,00,000, Rs 1,26,35,00,000, Rs 1,16,88,00,000 and Rs 1,04,57,00,000.

Check four: cash available for debt service is positive in every single year, running from Rs 45,68,00,000 to Rs 1,61,57,00,000. The model never needs a plugA figure inserted to make a model balance, and a sound schedule never needs one. and never borrows more in order to pay its own interest. Check four has the most teeth in it. If cash available goes negative in any year, the shortfall has to be funded from somewhere, and in a spreadsheet that almost always happens silently through a balancing figure that nobody notices.

Check five: the exit bridge uses the same lines as the entry bridge, and no premium is applied to any borrowing at either end. Check six: the five sweeps sum to the fall in borrowing. Rounding lives in that sum, and it is dealt with below. Check seven: the money multiple and the annual rate agree over the hold. Rs 28,80,15,00,000 over Rs 12,00,00,00,000 is 2.40 times, and 2.40 times compounded over five years is 19.14 per cent a year and nothing else. If those two disagree, one of them has been typed rather than computed.

What is written when rebuilt figures differ by a lakh?

One sentence, and then the work moves on. The situation is live in the table above. The five printed sweeps are Rs 45,68,00,000, Rs 71,73,00,000, Rs 99,64,00,000, Rs 1,29,54,00,000 and Rs 1,61,57,00,000, and they add to Rs 5,08,16,00,000. Borrowing runs from Rs 13,00,00,00,000 at completion down to Rs 7,91,85,00,000 at exit, a fall of Rs 5,08,15,00,000. The two totals are a lakh apart.

Nothing is broken. The schedule is computed on unrounded balances and printed to the nearest lakh, so five printed figures added together can miss a total by a lakh or two, and the correct response is to say so rather than to adjust anything. Where it comes from is exact. Year 1 closes at Rs 8,54,32,50,000 before rounding, precisely half a lakh. The figure prints as Rs 8,54,33,00,000, and the printed opening less the printed sweep gives Rs 8,54,32,00,000. One row, one lakh, carried into the total.

A gap of that size is a rounding differenceA small discrepancy caused by printing rounded figures, and stated rather than corrected., and a rounding difference is a different animal from a defect. The test that separates them is simple and it is worth applying in that order every time.

The order matters because both wrong responses are tempting. Reporting a lakh as a defect wastes a reviewer's afternoon and teaches the person who built the model to stop trusting the reviewer on the things that do matter. Nudging one figure so the column adds up tidily is much worse. The nudge breaks the tie between the printed number and the arithmetic that produced it, and the next person to rebuild the schedule from the inputs will not be able to reproduce the table at all. The discipline is the same one that governs every figure in this worked example: round at the end for display, never in the middle, and never rebuild one figure out of another figure's printed form.

And if the difference is larger than rounding could produce? The error usually sits in the rebuild's own arithmetic, so start there. Then it is reported, plainly, with the two figures side by side. A schedule that has been rebuilt independently and reconciles is worth a great deal more than one that has only ever been read.

What does the finished model still not tell anybody?

A great deal, and being clear about that is part of building one properly. The model says how this structure behaves if these figures happen. The model does not say whether they will.

The model does not say that any of the five years will look like the row printed against it. Every EBITDA figure in the table is a forecast, and the schedule takes those forecasts as given in order to isolate the arithmetic of the borrowing. Taking the forecasts as given is the single largest thing the model leaves out. A year where EBITDA lands ten per cent below the row does not just reduce that year's sweep. The shortfall also raises every subsequent year's interest, and higher interest reduces every subsequent sweep. The same self-reinforcing mechanic is running the other way.

Nor does the model say that the borrowing could be arranged on these terms, that a lender would provide these amounts, or that this business could carry this obligation. Each of those is a question about the world, and the table is arithmetic on an invented structure over an invented five years. It does not say whether the price paid was sensible, whether the return is good or poor, or whether Sankalp Industrial Systems Limited is cheap or expensive at any figure.

The model also does not give the range around its own answer. Every figure in it rests on the entry multiple, the exit multiple, the two rates, the assumed effective tax rate and the two operating lines the buyer changed. A move in any one of those moves the whole table. How far the answer travels when the entry price moves is covered separately, and so is the decomposition of where the return came from.