Fee, interest during construction and the reserve all grow with the loan. Turn them into one factor and the loop solves in a line, or in six passes.
The circular reference in a project finance model comes from sizing the debt on a funding requirement that includes the cost of the debt itself: the upfront fee, interest during construction and the debt service reserve. When those costs are proportional to the loan, the loop has a closed-form answer, funding = cost / (1 − gearing × financing cost per unit of debt). On an illustrative 400.0 build at 70 per cent gearing that gives total funding of 445.78 and debt of 312.05, and five passes of a simple iteration get within 0.01 of it.
Worked in full in The Project Finance Handbook by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
A lender sizing on gearing lends a share of total uses. Total uses are the construction cost plus everything it costs to finance it, and every one of those lines grows with the loan: the arrangement fee is a percentage of the debt, interest during construction is charged on the drawn debt, and a debt service reserve funded at close is a number of months of the debt service the loan carries. Change the debt and the uses change; change the uses and the debt changes. Excel flags it as a circular reference, and the model either refuses to calculate or calculates something that depends on the order it was opened in.
All figures are illustrative, in millions. Interest during construction is simplified to the all-in rate times the build period times an average drawn share of 55 per cent, so that it is a fixed fraction of the debt; a monthly drawdown schedule changes the number, not the method.
| Input | Value |
|---|---|
| Construction cost before financing | 400.0 |
| Gearing (debt / total funding) | 70% |
| Upfront fee | 2.0% of debt |
| All-in interest rate | 7.0% |
| Construction period | 2 years |
| Average share of the loan drawn during construction | 55% |
| Repayment | 18-year annuity |
| DSRA funded at close | 6 months of debt service |
Fee factor = 2.0%
IDC factor = 7.0% × 2 × 55% = 0.0770
Annuity factor = r / (1 − (1 + r)−18) = 0.0994, so DSRA factor = 0.0994 × 6 / 12 = 0.0497
Financing cost per unit of debt, k = 0.02 + 0.0770 + 0.0497 = 0.1467
Every unit of debt raised therefore costs 0.1467 to raise and carry to completion. That number is the whole circularity.
Write funding F as cost plus financing costs, and debt D as gearing times funding:
F = C + k × D and D = g × F, so F = C + g × k × F
F = C / (1 − g × k) = 400.0 / (1 − 0.70 × 0.1467) = 400.0 / 0.8973 = 445.78
D = 0.70 × 445.78 = 312.05; equity = 445.78 − 312.05 = 133.73
Excel: =Cost/(1-Gearing*(Fee+Rate*Years*AvgDrawn+PMT(Rate,Tenor,-1)*Months/12))
The 45.78 of financing costs split into a fee of 6.24, interest during construction of 24.03 and a reserve of 15.51, which is exactly six months of the 31.02 annual debt service the 312.05 loan carries. Sources equal uses and the reserve matches the loan it reserves for: the two checks every funding table should print.
Real models rarely allow the closed form, because IDC comes from a monthly schedule, the debt is the lower of a gearing test and a cover test, and fees are tiered. The loop is then solved by passes: take a debt figure, compute the uses, re-size the debt, repeat.
| Pass | Debt in | Funding | Debt out | Change |
|---|---|---|---|---|
| 1 | 0.00 | 400.00 | 280.00 | 280.00 |
| 2 | 280.00 | 441.08 | 308.75 | 28.75 |
| 3 | 308.75 | 445.30 | 311.71 | 2.95 |
| 4 | 311.71 | 445.73 | 312.01 | 0.30 |
| 5 | 312.01 | 445.77 | 312.04 | 0.03 |
| 6 | 312.04 | 445.78 | 312.04 | 0.00 |
Each pass multiplies the remaining error by g × k = 0.1027, which is why the loop converges at all: as long as a unit of debt costs less than it raises, the iteration settles. Three ways to build it in Excel:
The rate enters twice, through IDC and through the annuity that sizes the reserve, and each extra unit of financing cost is itself 70 per cent debt-funded, so it pulls the funding requirement up again.
| All-in rate | k | Total funding | Debt | Equity | Financing costs |
|---|---|---|---|---|---|
| 5.0% | 0.1178 | 435.94 | 305.16 | 130.78 | 35.94 |
| 6.0% | 0.1322 | 440.78 | 308.55 | 132.24 | 40.78 |
| 7.0% | 0.1467 | 445.78 | 312.05 | 133.73 | 45.78 |
| 8.0% | 0.1614 | 450.93 | 315.65 | 135.28 | 50.93 |
| 9.0% | 0.1761 | 456.24 | 319.37 | 136.87 | 56.24 |
Financing costs go from 9.0 per cent of the construction cost at a 5.0 per cent rate to 14.1 per cent at 9.0 per cent. The sponsor's equity rises with them, because 30 per cent of every extra unit of financing cost is equity.
The shortcut is to break the loop by sizing debt on the construction cost alone: 70 per cent of 400.0 is 280.00, and the sponsor plans 120.00 of equity. But the 280.00 loan still has to be financed, and its own costs come to 41.08. Uses are 441.08, the debt is still 280.00, and the equity actually needed is 161.08, a shortfall of 41.08 against the plan. The project has drifted to 63.5 per cent gearing, and the sponsor funds 27.34 more equity than the correctly solved structure would have asked of it, because the 32.05 of debt the lender would have provided against the financing costs was never requested.
The loop is not an Excel nuisance to be engineered away. It is the statement that the loan pays for part of its own cost, and a model that removes it understates the debt and overstates the equity.
The funding table in the free workbook for this case is circular by construction and is solved by unrolled passes on a sheet of its own, with a cell that reads converged. The interest line is worked period by period in how to calculate interest during construction.
It works, but it is fragile: an error value can stay trapped in the loop after its cause is fixed, and the setting belongs to the Excel session, not the file. Unrolled passes, one column each, are more robust. In the worked case the error shrinks by a factor of 0.1027 per pass, so five passes take a debt figure from zero to 312.04, within 0.01 of the closed-form 312.05.
Because each unit of debt costs less than one unit to raise and carry. The error is multiplied each pass by gearing times the financing cost per unit of debt, here 0.70 x 0.1467 = 0.1027. As long as that product is below one the iteration settles; at these factors each pass removes about 90 per cent of the remaining error.
The loan is too small and the equity too large. At 70 per cent of a 400.0 cost the debt is 280.00, but financing it still costs 41.08, so the sponsor must fund 161.08 rather than the 120.00 planned. The correctly solved structure lends 312.05 and asks for 133.73 of equity.
This article is one calculation from The Project Finance Handbook. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: infrastructure, data centres and energy → · All 681 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.