A borrowing forecast must fund the interest on the new borrowing

A forecast can need additional borrowing to pay operating bills and then need more borrowing to pay the interest on that draw. If the interest formula refers to debt and the debt formula refers to interest, the spreadsheet contains a circular relationship. The underlying economics may be valid, but the calculation needs an explicit method and timing assumptions.

Microsoft defines a circular reference as a formula referring to itself directly or through other cells. Its guidance describes intentional iterative calculation as well as methods for finding unwanted loops. Enabling iteration alone does not establish that a financial model's equation or result is correct.

Start by identifying the cash gap before the financing items, the opening debt, the draw dates, the interest period and the payment date. A monthly model often simplifies these details. State that simplification so the reviewer can tell whether the circularity reflects a real same-period funding need or an incorrect date assumption.

Suppose opening debt is $200,000 and the operating cash gap is $100,000 before current interest. Assume a 1% interest rate for this period, the entire new draw is outstanding throughout the period, and all interest is paid at the end using the same facility. Ignore fees and repayments in this first illustration.

If the new draw is X, interest is 1% of $200,000 plus X. The draw must therefore equal $100,000 + 1% × ($200,000 + X). Rearranging gives 99% × X = $102,000, so X is approximately $103,030.30.

Closing debt is $303,030.30 and period interest is $3,030.30. The new cash exactly funds the $100,000 operating gap and that interest. Drawing only $102,000 would cover interest on the opening debt but leave interest on the new borrowing unfunded under this assumed timing.

A model might instead approximate period interest using opening debt plus half the new draw. Then X = $100,000 + 1% × ($200,000 + X/2). Rearranging gives 99.5% × X = $102,000, so the draw is approximately $102,512.56 and interest is $2,512.56.

The roughly $517.74 difference from the first case is not a solver tolerance. It comes from assuming less time outstanding for the new borrowing. An average-balance approximation needs a reasonable balance path; it is not automatically the lender's contractual calculation.

If material draws occur on known dates, use a dated balance schedule with the agreed interest convention. A draw used to pay interest only on the final day should not automatically earn a full month's interest in that schedule. The appropriate timing can remove part of an apparent circularity.

Interest may accrue during one period and be paid in the next. If so, it affects the accrual schedule before it affects the current cash gap. Borrowing to pay it in the wrong month can overstate the present draw and understate the later payment need.

Some agreements permit unpaid interest to be added to a balance; others require a cash payment or treat nonpayment differently. Model the actual terms. A formula that adds every unpaid interest amount to principal is not evidence that the lender has agreed to that treatment.

Keep accrued interest, cash interest paid and debt principal movements identifiable in the schedule. That separation helps reconcile the model to statements and avoids treating an interest expense as though it were automatically a principal repayment or cash outflow on the same date.

A period with surplus cash needs a separate repayment rule. Allowing the same draw formula to become negative may accidentally imply a repayment larger than outstanding debt or available cash. Define the permitted draw and repayment ranges, then retain any cash surplus after debt reaches its contractual minimum balance.

If the illustrative facility limit is $300,000, only $100,000 of additional principal is available from the $200,000 opening balance. The full-period model requires more than that. Capping the draw at $100,000 does not make the remaining interest disappear; it produces an unfunded cash need under the stated assumptions.

Show that need explicitly and identify the date it arises. A spreadsheet that forces both cash and debt to their target values can conceal the infeasibility. The reviewer should see that the available facility cannot satisfy every modeled payment without another action.

Fees, minimum cash balances and draw conditions can introduce additional constraints. Add each on its actual basis and date. A percentage fee deducted from proceeds, for example, means the gross increase in debt differs from the usable cash received, so the original simple equation must be revised.

For a more complex model, controlled iteration may be appropriate. Retain the starting values, stopping rule and maximum iterations. Check that the resulting cash, debt and interest equations all reconcile within a tolerance meaningful to the decision, rather than accepting the absence of an error message.

Try a simple case with an algebraic answer, such as the example above, to verify the calculation design. Also inspect cases with no funding gap, a facility limit and a repayment. Piecewise rules can create behavior that a smooth central case does not reveal.

Keep intentional loops distinguishable from accidental references elsewhere in the workbook. A global iteration setting can allow an unrelated mistake to remain hidden. Document which relationship requires repeated calculation and why its solution is economically meaningful.

The report should show opening debt, operating cash before financing, draws, repayments, cash interest, fees, closing debt and remaining availability. Include the timing convention used for interest and any unmet funding amount. Those rows allow the treasury owner to compare the plan with the actual agreement.

Preserve the model version when terms or timing change. A revised borrowing requirement should be explainable through the operating gap, price of funding or dates, rather than appearing as an unexplained change in a circular formula's output.