Bridgesheet

Financial modelling6 min read

Revolver LBO Model: Drawdowns and Repayments in Excel

The revolver in an LBO is a credit line that the company draws on when its cash falls below a minimum and repays as soon as it can. This article shows how to build it in Excel, with two formulas, a five-year worked example and the traps that distort the debt schedule.

Stacks of gold coins of increasing height
Photo: Unknown · CC0 · Rawpixel

What the revolver does in an LBO

The revolver is the revolving credit facility (RCF) of an LBO. The banks open a limit, for example €8m, which the company draws on when it runs short of cash and repays when cash becomes surplus again. Any amount repaid can be drawn again, hence the word "revolving". In a financial model, the revolver lbo model line acts as a shock absorber: it stops cash from falling below the minimum the company needs to operate.

The difference from the conventional debt tranches is clear. Senior A, senior B or mezzanine are drawn in full on the day of the buyout and repaid according to a schedule, as the article on the LBO model and repayment schedule shows. The revolver, by contrast, often starts at zero and moves every year according to need.

It serves three purposes:

  • Funding seasonal troughs in working capital (the cash tied up in inventory and customer receivables, net of supplier payables);
  • Absorbing a bad year without missing the repayments on the term debt;
  • Showing lenders that the plan holds even when cash flows turn negative.

The parameters of a revolver

Parameter What it sets Typical range
Limit (commitment) Maximum amount that can be drawn 5% to 15% of total debt, or one to two months of revenue
Rate on the drawn amount Interest on what is used Reference rate (Euribor) plus a margin close to the senior margin
Commitment fee Cost of the undrawn portion Often 30% to 40% of the margin
Minimum cash Threshold below which the company draws Set by management, often a few weeks of costs
Term Date by which the line must be renewed or repaid Close to that of the senior debt

The typical ranges depend on the market and the deal; the credit agreement is what counts. The point most often forgotten is the commitment fee: a line that is never drawn still costs money every year.

The two revolver formulas in Excel

The whole logic fits in two lines, calculated each year (or each quarter) after the term debt service.

1. The cash shortfall or surplus. Start from opening cash, add the year's cash flow and deduct the minimum cash:

Surplus = Opening cash + Cash flow for the year − Minimum cash

2. The drawdown and the repayment. If the surplus is negative, draw, up to the remaining capacity. If it is positive, repay, up to the drawn balance:

Drawdown = MIN(MAX(-Surplus, 0), Limit - Opening balance)

Repayment = MIN(Opening balance, MAX(Surplus, 0))

The closing revolver balance is then the opening balance, plus the drawdown, minus the repayment. Whatever surplus remains after the revolver is repaid goes into the cash sweep (the early repayment of term debt) or builds up as cash.

Worked example: a revolver LBO model over five years

Take Groupe Morel, a fictional distributor acquired in an LBO. After the buyout, the company launches a plan to open two warehouses: in the first two years, the cash flow available after term debt service is negative. The assumptions:

  • €8m revolver, undrawn at the start;
  • 6.0% rate on the drawn amount and a 1.2% commitment fee on the undrawn portion, both calculated on the opening balance;
  • opening cash and minimum cash of €1.5m;
  • cash flow available after term debt, before revolver costs: −2,400k€, −1,100k€, +1,800k€, +2,600k€ and +3,000k€.
Year Cash flow before revolver Interest Fee Cash flow after costs Drawdown Repayment Revolver balance Closing cash
0 0 1,500
1 −2,400 0 96 −2,496 2,496 0 2,496 1,500
2 −1,100 150 66 −1,316 1,316 0 3,812 1,500
3 1,800 229 50 1,521 0 1,521 2,291 1,500
4 2,600 137 69 2,394 0 2,291 0 1,603
5 3,000 0 96 2,904 0 0 0 4,507

In k€, rounded. Year 1 fee: 8,000 × 1.2% = 96k€. Year 2 interest: 2,496 × 6% = 150k€.

Read the table line by line. In year 1, the company burns 2,496k€: it draws that amount, and its cash stays at €1.5m. In year 2, it draws another 1,316k€; the balance peaks at 3,812k€, or 48% of the limit. From year 3, the cash flow turns positive and repays the revolver first. In year 4, the balance is cleared and 103k€ of surplus is left. In year 5, the 3,007k€ above the minimum is available for the senior debt cash sweep.

Over the five years, the revolver costs 516k€ in interest and 377k€ in commitment fees, a total of 893k€. More than 40% of that cost pays for capacity the company does not use.

What happens if the limit is too low

Let us rerun the calculation with a revolver of only €3m. In year 2, the company needs 1,253k€ but has only 564k€ of capacity left. Its cash falls to 811k€, 689k€ below the minimum. In a real deal, that is the signal that you need either a higher limit, a phased investment plan, or more equity at entry.

The annual model trap

An annual model reads cash at 31 December. Yet the working capital of a distributor swells during the year, ahead of the big selling seasons. If the intra-year peak exceeds the year-end level by €2m, Groupe Morel's maximum need is not 3,812k€ but about 5,800k€. The €8m limit covers it; a €5m limit would have looked sufficient in the annual model and would have broken in September. For a seasonal business, run the revolver on a quarterly or monthly model, at least for the first year. The article on working capital explains where these gaps come from.

Five revolver modelling traps

  1. Circularity. If revolver interest is calculated on the year's average balance, it depends on the drawdown, which depends on the interest. Calculate it on the opening balance, as here, or add a switch that breaks the loop.
  2. Order of priority. The revolver is repaid before the term debt cash sweep. Sweeping the senior debt while the revolver remains drawn artificially reduces interest and flatters the IRR.
  3. The ignored limit. Without the MIN on remaining capacity, the formula draws beyond the line. Add an alert line that lights up when closing cash falls below the minimum.
  4. The forgotten fee. It applies to the undrawn portion, not to the whole limit nor to the drawdown.
  5. The revolver that funds lasting losses. A balance that never comes back down is no longer a shock absorber but term debt in disguise. Lenders sometimes require a clean-down: bringing the balance to zero for a few days a year to prove that the line really serves seasonality.

How the revolver changes the reading of an LBO

The revolver barely changes enterprise value. It changes risk. A plan that holds without a revolver leaves the whole line available for bad surprises; a plan that draws half of it as early as year 2, like Groupe Morel, has less headroom. In the leverage calculation (debt divided by EBITDA), the drawn balance counts as debt: in year 2, it adds €3.8m to the numerator, just when the company is at its most indebted.

Before setting the limit, flex the year 1 and year 2 cash flows by plus or minus 20% in a sensitivity table, and read the peak drawdown in each case. The limit should be chosen on the cautious scenario, not on the central plan.

Test your debt structure. Bridgesheet's Excel LBO model links sources and uses, four debt tranches, cash sweep and covenants. It does not include a revolver: it flags each year in which cash falls below the minimum, which shows where a credit line would be needed and for how much. This is an estimate, not a certified valuation or investment advice.