Excel project finance model – unstable circular reference in monthly funding waterfall (single sheet)

Hi all,

We’re dealing with a structural issue in an advanced real estate development model built entirely within a single Excel sheet.

The model distributes monthly project cash flows (costs and revenues), and funding follows a strict waterfall:

1. Equity first
2. Then plot financing
3. Then construction financing

Monthly funding need is driven by cash deficits. Debt accrues interest, and outstanding balances roll forward month by month.

The issue is that funding draw, cash balance, outstanding debt, and interest are interconnected within the same period. This creates a circular reference in the intermediate funding calculation.

What’s interesting is that the model does converge to a stable result — but only after a manual workaround:

• We temporarily overwrite the funding formula row with static values across all months.
• Let Excel fully recalculate.
• Reinsert the original formulas.
• Then the model stabilizes and produces consistent results.

So effectively, we are “seeding” the system to break a feedback loop before Excel can settle into equilibrium.

Clearly this is not a robust solution.

We’re looking for structural modelling advice:

• What is best practice for building a monthly funding waterfall where cash deficits drive draws, but draws also affect cash and interest?
• How would you structure this deterministically in a single sheet without unstable circular references?
• Is the right approach to base funding need purely on cumulative deficit logic?
• Or is iterative calculation acceptable if properly structured?

We are using the default “Unsolved” flair and will mark the post as “Solved” by replying “Solution Verified” once an answer resolves the issue.

Appreciate any insight from those experienced in project finance / development modelling.