Circular interest: solve it in one cell, or know the error

Take a term loan whose interest is charged on the average of the opening and closing balance. Closing balance depends on how much cash is left to repay it. Cash left depends on interest. Interest depends on the closing balance. The cell refers to itself.

Modellers split three ways on what to do about it, and each camp calls the others fragile.

• Switch on iterative calculation. The ICAEW's Excel Community is unenthusiastic: "Intentional circularities, achieved by enabling iterative calculations, are not recommended", because errors can go unnoticed. For self-reference that cannot be avoided, it calls a "circularity breaker" macro the best way to manage it, with a flag for when pasted values are not live (ICAEW, Intro to financial modelling part 15).
• Paste-values macros. Ed Bodmer says he is "strongly opposed to making copy and paste macros", and that iteration plus macros means "you have destroyed your model". He argues for a user-defined function instead (Ed Bodmer, Philosophy of Circular References).
• Take the opening balance. Interest on the opening balance has no loop. Wall Street Prep calls the average balance "the standard convention" and notes the beginning balance "can also be used to avoid the circularity issue altogether" (Wall Street Prep, interest expense).

Both the ICAEW and Bodmer also mention a fourth option: remove the loop with algebra, where it is easy. Neither sets out the conditions under which it is exact, and neither puts a number on the opening-balance shortcut. This article does both.

Our answer

For one tranche or one revolver, interest on the average balance, a sweep through the period, a flat tax rate and no binding minimum cash, the circularity is linear. It has a one-cell closed form that needs no iteration, no macro and no user-defined function.

Opening-balance interest is not wrong. It is exact if the repayment is made on the last day. If repayment is spread through the period it overstates interest by about the period rate times the repayment in the period, divided by two. In an annual model with a heavy sweep that is a visible error. In a monthly model it is roughly a twelfth of that.

Outside that narrow structure the closed form stops, and the fight between the camps is the right fight.

Why the loop has a closed form

Define the pieces for one period:

• D0 is opening debt and D1 is closing debt.
• r is the rate for the period and t is the tax rate, with k = 1 - t.
• X is the cash flow available for debt service, computed as if there were no interest and no tax on interest.

Three relationships close the loop:

Interest  = r * (D0 + D1) / 2
Repayment = X - k * Interest        (cash left after interest net of its tax shield)
D1        = D0 - Repayment

Substitute the first into the second, and the second into the third. D1 now appears on both sides, multiplied by k * r / 2. Collect the terms:

D1 = ( D0 * (1 + k*r/2) - X ) / ( 1 - k*r/2 )

The coefficient k * r / 2 is 0.7 x 8% / 2 = 0.028 in the example below. It sits far below one, so the equation has exactly one solution and the formula finds it. That is also why iteration converges quickly on this loop: the problem is easy for either method. The question is only whether you want the circular reference in the file.

A revolver works the same way. Let X be negative when operations consume cash and the facility draws to cover it. Interest income on an average cash balance is another linear term and folds in the same way.

The worked example

One term loan with a 100% cash sweep. Opening debt 1,000, rate 8% a year, tax 30%, cash flow available for debt service X = 200 a year before interest and its tax shield. This is an annual model with the sweep spread through the year.

Check year 1 by hand. The closing balance is 851.85, so the average balance is (1,000 + 851.85) / 2 = 925.93. At 8% that is 74.07 of interest. Cash left to repay is 200 - 0.7 x 74.07 = 148.15, and 1,000 - 148.15 = 851.85. The loop closes on its own number, with no iteration run to get there.

The Excel version, split into named steps so a reviewer can follow each one (D_open is opening debt, Rate the period rate, Tax the tax rate, CFpre the cash flow available before interest and its shield):

HalfK     = (1 - Tax) * Rate / 2
Closing   = MAX(0, (D_open * (1 + HalfK) - CFpre) / (1 - HalfK))
Interest  = Rate * (D_open + Closing) / 2
Repay     = D_open - Closing

Interest and Repay read the Closing cell. Nothing reads back into it. The P&L takes Interest, tax takes the P&L, and no circular reference is left in the file.

For a revolver with a commitment limit, wrap the same expression: MIN(Commitment, MAX(0, ...)).

The kink is handled by the clamp

A sweep cannot repay more than is owed. Set CFpre to 1,200 and the formula gives (1,000 x 1.028 - 1,200) / 0.972 = -176.95. A negative debt balance is impossible, so the loan is cleared and MAX returns 0. Interest then reads off the clamped balance: 8% x (1,000 + 0) / 2 = 40.00, which is the true answer. Once the repayment is pinned at the balance, the loop is gone, so the clamp is correct and no iteration is needed.

What opening-balance interest costs

In year 1 of the example the opening-balance method charges 80.00 where the average method charges 74.07. The overstatement is 5.93. That equals the rate times the repayment, divided by two: 0.08 x 148.15 / 2 = 5.93. The method treats the whole year's repayment as if it arrived on the last day, so it charges interest for the half-year on average that the money was already gone.

Two consequences follow, and both are easy to misread.

The size depends on the repayment, not the rate alone. In this example the overstatement is about 8% of year-1 interest, but that is because the sweep is 200 on a 1,000 loan. At a cash flow of 100 it is about 2.3%. Do not carry 8% to another model. Compute rate x repayment / 2 for yours.

It compounds, through the opening balance. The overstated interest leaves less cash to repay, so closing debt is 4.15 too high after year 1. Each later year starts from that wrong balance. By year 3 the gap is 13.90, or 2.6% of the correct 529.46 closing balance.

Period length changes the answer. Halve the period and both the period rate and the period repayment halve, so each period's error falls to roughly a quarter. There are twice as many periods, so the annual total falls by about half. At monthly frequency the error is roughly a twelfth of the annual one. We checked month 1 of this loan (closing debt 987.97, overstatement about 0.04) and the identity rather than a full 36-month schedule, so treat "roughly a twelfth" as a scaling, not a measured result. The ICAEW's "compromise in precision" is defensible in a monthly model. In an annual model with a large sweep, it is a real number you can estimate in advance.

The strongest objection

Real models are not this tidy. Companies carry tax losses and interest limits, hold a minimum cash balance, and have several tranches. The closed form also needs a cash flow line before interest, and that line must stay reconciled to the real statements. If the narrow case is not the normal case, the algebra is a classroom trick.

We agree with most of that, and the claim above is stated for the narrow case because of it. Four things break the closed form:

1. 1.
   Several tranches that interact. Each tranche's clamp depends on the others, so the case analysis grows with every tranche you add.
2. 2.
   Tax that is not a flat rate on profit. With losses carried forward or an interest cap, tax is not a constant fraction of interest, and the formula is no longer exact. A case split on whether tax is paid can be written, but at that point a flagged loop or a function may be easier to audit.
3. 3.
   A minimum cash floor that binds. The sweep is no longer all of X less interest. The formula then applies only to the cash above the floor.
4. 4.
   Fees sized on a facility that includes them. The algebra gets long, and Bodmer, who prefers algebra for simple fees and interest during construction in an annual model, says as much.

What survives is smaller than a universal replacement. The closed form fits the sub-loop of one tranche or one revolver at a time, and it is a clean way to size the shortcut before you accept it. The cash flow before interest is not a second model. It is the existing cash flow with the interest and interest-tax lines left out, built from lines already there. It does need a reconciliation, which is the next section.

What to do differently

1. 1.
   Check the structure before reaching for a switch or macro. Is this one tranche, average-balance interest, flat tax, no binding cash floor? If yes, solve it in one cell and split the cell into named steps.
2. 2.
   Pick the interest convention from the facility's repayment dates, not from habit. A single sweep at period end means opening-balance interest is exact and there is no loop to solve. Repayments or draws through the period mean the average is the better measure.
3. 3.
   Estimate the error before calling the shortcut small. Compute rate x repayment / 2 for the period. If it is a rounding error against interest, use opening balances. If it is 8% of interest, do not.
4. 4.
   Add a balance check whichever method you use. Opening debt plus draws less repayments equals closing debt, and cash ties to the balance sheet. That check catches a stale pasted value, a stuck iteration or a drift between the cash flow before interest and the statements. It is needed under every approach.
5. 5.
   Run an audit tool. A model built on the closed form should report no circular references with iterative calculation off. If it does report one, you have a loop you did not know about.

What we do not know

• Which camp is larger, and how often iteration or pasted breakers actually cause errors in practice. Both sources argue from experience and neither gives a rate.
• Whether reviewers find a one-cell closed form harder to read than a labelled switch or macro. We split it into named steps to help, but we have no test showing people prefer it.
• How the algebra extends across several interacting tranches in a way that stays readable. We think a function or a flagged loop is probably the better tool there, and we would like to see someone prove us wrong with a worked schedule.
• The monthly size is a scaling from one month and an identity, not a full schedule.

This is general information about modelling technique, not financial advice.