How to Model a Revolver in Excel

A revolving credit facility is the model's shock absorber: it draws cash when the business runs short and pays down when it generates surplus. Modeling it means enforcing a minimum cash balance, sweeping excess cash to repay the draw, and handling the interest circularity that the sweep creates. Done cleanly, the revolver keeps cash from ever going negative.

What a revolver does in a model

A revolver is a flexible line of credit. The model draws on it whenever projected cash would otherwise fall below a minimum balance, and repays it whenever cash sits above that minimum. This keeps the balance sheet's cash line at or above the floor in every period.

It fits at the bottom of the cash flow waterfall. After operating, investing, and mandatory financing flows, the model computes cash available, compares it to the minimum, and either draws or repays the revolver to close the gap. Revolver interest then flows back to the income statement.

Build it step by step

Lay out the revolver block below the cash flow statement: cash available before revolver, the draw or repayment, the ending revolver balance, and the interest line. The draw logic keys off a minimum cash input.

Compute cash before the revolver, then test it against the minimum to decide whether to draw or sweep.

1. Compute cash available before the revolver from the cash flow statement.
2. Required draw is =MAX(0, minimum_cash - cash_before_revolver).
3. Available repayment is =MIN(beginning_revolver, MAX(0, cash_before_revolver - minimum_cash)).
4. Ending revolver balance is =beginning + draw - repayment.
5. Revolver interest is =rate * AVERAGE(beginning, ending) or on the beginning balance to stay non-circular.

=MAX(0,50-30) draws 20 to bring cash up to the 50 minimum.

The interest circularity and how to handle it

Revolver interest depends on the balance, the balance depends on the sweep, the sweep depends on cash available, and cash available depends on interest expense. That loop is a circular reference. Excel cannot resolve it unless you either break the loop or enable iterative calculation.

The clean options are to compute interest on the beginning balance, which removes the loop entirely, or to calculate interest on the average balance with iterative calculation turned on and a circularity switch. The switch is a cell that forces interest to zero, breaking the loop so you can find and fix errors, then toggling it back on.

• Beginning-balance interest: no circularity, slightly less precise.
• Average-balance interest with iterative calc: precise, needs File > Options > Formulas > Enable iterative calculation.
• Keep a circularity breaker cell to zero out interest while auditing.
• Use Find Dependents on the interest cell to see every link feeding the loop.

Pitfalls and what reviewers check

The most dangerous failure is a revolver that draws and repays in the same period, or one that lets cash fall below the minimum because the sweep formula references the wrong cash line. Wrapping the draw in MAX(0, ...) and the repayment in MIN(beginning_revolver, ...) prevents negative balances and over-repayment.

Reviewers check that cash never breaches the minimum, that the revolver never goes negative, and that flipping the circularity switch returns the model to a clean calculable state with no #REF! or 0 errors stuck in cached cells. Mapping the dependents of the interest cell is the fastest way to confirm exactly which cells feed the circular loop before you enable iteration.