The Model That Moves When You Press F9
A colleague has built a three-statement credit model with a revolver. Cash is the plug, and interest on the revolver is computed on the average of the opening and closing balances - a circular reference. Iterative calculation is switched off because the file 'kept throwing errors'. In the latest run EBITDA is $60m, capex $25m, mandatory amortization $10m, cash interest on the term loan $18m, the tax rate is 25%, and the revolver goes from $0 to $22m drawn at 8.0%. Total debt is $208.8m. Pressing F9 twice moves net income from $9.1m to $8.9m and the cash balance moves with it. The covenant test (total debt / EBITDA at or below 3.50x) prints 3.48x, and the compliance certificate is due tomorrow.
1. Explain why net income changes between recalculations, and what the model is actually reporting for revolver interest.
Read the worked answer
- What is happening: with iterative calculation off, Excel cannot resolve the loop. It does not produce the correct fixed point; it returns the last value it managed to compute (or zero). Each recalculation starts from a different stale seed, so the output is path-dependent and non-deterministic. The model is not a model - it is whatever the previous keystroke left behind.
- Why the balance sheet still balances: the revolver is the plug, so assets and liabilities tie by construction. A balanced balance sheet proves nothing here. The error sits in the interest line, not in the plug.
- Quantify the understatement. Average-balance interest for the year = 8.0% x (0 + 22) / 2 = $0.88m. The stale/zero iteration is effectively charging interest on the opening balance of $0, so interest is understated by $0.88m pre-tax, $0.66m after tax.
- Direction of the bias: whenever the revolver is growing, beginning-balance interest understates the true cost. Whenever it is being repaid, it overstates it. The bias flips with the direction of the cash flow, which is exactly the kind of error that survives a review because it looks plausible in both directions.
2. What is the right fix, and what do you give up with each option?
Read the worked answer
- Option A - enable iterative calculation (max iterations 100, max change 0.001). This converges to the true fixed point and preserves average-balance interest, which is what the credit agreement actually charges. Cost: a deliberate circular reference, an error-prone file for anyone who opens it with default settings, and a value that depends on convergence settings.
- Option B - break the loop by charging interest on the opening revolver balance. Deterministic, auditable, no circularity. Cost: a known bias equal to the rate times half the change in the revolver. Fine if the revolver is small relative to total debt; material if the revolver is the swing factor.
- Option C - restructure the cash sweep so the revolver balance is a function of cash before interest, and interest is a function of the resulting balance computed in one pass (a two-step waterfall). Cleanest technically, requires rebuilding the debt schedule.
- Recommendation: Option A for the final model, with a circuit-breaker cell that overrides interest to a hard-coded value if the loop fails to converge, plus a check row that flags whether interest is within $1k of the prior iteration. Then document the setting in the model's cover sheet - the next person opens this file with default settings.
- Regardless of option: never leave iterative calculation off in a file that contains a loop. Off is not 'safe', it is 'silently wrong'.
3. Does any of this change the 3.48x covenant number, and can you sign the certificate?
Read the worked answer
- The ratio itself is unaffected. Total debt / EBITDA = 208.8 / 60.0 = 3.48x. Neither the numerator nor the denominator depends on revolver interest, so the $0.66m after-tax swing does not move the reported leverage.
- But the cushion is $1.2m of debt. At 3.50x, debt capacity is 210.0, against 208.8 used. That is 0.6% of headroom on a ratio that is being computed by a model that does not converge. Even if this particular ratio escapes the error, the certificate is only as good as the model's other outputs.
- Where the error does bite: any test that uses interest or net income - interest coverage, fixed charge coverage, FCCR, minimum EBITDA - is directly wrong by at least $0.88m pre-tax. Check whether the credit agreement has a secondary financial covenant before concluding the certificate is clean.
- The sign-off position: do not certify off a non-deterministic model. Rebuild the revolver interest as a fixed point, re-run, confirm the 3.48x is stable across three consecutive recalculations, and only then sign. If time is short, switch to opening-balance interest for the certificate and disclose the $0.88m interest understatement in the covenant compliance note.