Most project finance models I have opened carry a little button labelled something like “Run Sizing”. Press it and a macro copies a row of numbers, pastes it as values two rows down, checks whether the difference has gone to zero, and repeats until it has. It works. It also breaks the moment someone opens the file with macros disabled, which in a bank’s credit team is roughly everyone.
I wrote my share of those macros early in my career at RMB Infrastructure Finance. I am not proud of all of them. One had a loop counter set to 1,000 because I could not work out why it sometimes failed to converge at 100. (The answer was a sign error. It always is.)
The good news is that for the most common case, sculpting debt to a flat target DSCR with interest that is tax deductible, you do not need the macro at all. You need one line of algebra and a formula that looks backwards in time. This post walks through it.
Watch my debt sculpting explainer video on YouTube. If you are new to the topic, start with What is Debt Sculpting?.
The basic maths has no circularity
Sculpting sets debt service in each period so the DSCR comes out at exactly the target. If CFADS in period t is known and the target is D, debt service is simply CFADS divided by D.
DSt = CFADSt / D
The debt you can raise is the present value of that debt service stream, discounted at the all-in interest rate (base rate plus margin, after any hedging), period by period.
Debt0 = ∑t=1…T DSt / ∏k=1…t (1 + rk)
Nothing in there refers back to itself. Interest is opening balance times rate, principal is debt service minus interest, and the balance runs to zero in the final period by construction. If your model stopped here, you would not need a macro, and you would not be reading this.
The loop is the tax line
CFADS is a post-tax number. Tax depends on taxable income, taxable income depends on interest, interest depends on the debt balance, and the debt balance depends on how much debt service CFADS can support. Round we go.
One way to see it: split CFADS into the cash flow you would have if the project paid tax with no interest deduction, call it CFADS*, plus the tax saved on interest. With a tax rate of τ:
CFADSt = CFADS*t + τ × rt × Opent
CFADS* is clean. It comes from revenue, opex and capital allowances and knows nothing about the debt. The only troublemaker is the tax shield term, and it depends on one thing: the opening balance in that period.
The macro deals with this by guessing the debt, computing interest, recomputing tax, resizing, and pasting until it settles. The algebra deals with it by noticing that you can solve for the opening balance directly if you start at the end.
Solve it backwards
Write out one period. The closing balance is the opening balance plus interest minus debt service, and debt service is post-tax CFADS divided by D:
Closet = Opent × (1 + rt) − (CFADS*t + τ × rt × Opent) / D
Rearrange for the opening balance:
Opent = (Closet + CFADS*t / D) / [1 + rt × (1 − τ / D)]
That is the whole trick. Each period’s opening balance depends only on its own closing balance, which is the next period’s opening balance. In the final period the closing balance is zero, so you can work from maturity back to financial close and every cell is determined. The opening balance in the first repayment period is your sculpted debt.
Two sanity checks on the formula. Set τ to zero and it collapses to the plain present value recursion from the previous section. And because τ/D is usually well below 1 (27% tax over a 1.30x DSCR is about 0.21), the denominator stays comfortably above 1, so nothing blows up.
Laying it out in Excel
Keep the usual layout, time across the columns. The only unusual thing is that the opening balance row looks one column to the right. Excel is perfectly happy with that, provided the chain ends: put a zero in the column after the final repayment period, or use your operations flag to force it.
| Row | What it holds | Formula for period in column H |
|---|---|---|
| 10 | Repayment flag | 1 in repayment periods, 0 otherwise |
| 11 | CFADS* (tax with no interest deduction) | From the operations and tax blocks |
| 12 | Period interest rate | All-in rate for the period, not annualised |
| 14 | Opening balance | =H10*(I14+H11/DSCR)/(1+H12*(1-Tax/DSCR)) |
| 15 | Interest | =H14*H12 |
| 16 | Debt service | =H10*(H11+Tax*H15)/DSCR |
| 17 | Principal | =H16-H15 |
| 18 | Closing balance | =H14+H15-H16 |
Row 14 references I14, the next period’s opening balance, which is the same thing as this period’s closing balance. Row 18 then recalculates the closing balance the normal way, forwards, and it should equal I14 to the cent. That equality is your proof the algebra holds.
The tax block downstream can now use row 15 as the interest deduction like any other model. Post-tax CFADS divided by row 16 gives a flat DSCR line. If it does not, the CFADS* row is picking up something it should not.
Where it breaks
The formula assumes the tax shield is used in full, in the same period the interest is paid. Real projects misbehave in a few ways.
- Assessed losses. Early in operations, accelerated capital allowances often push taxable income below zero, so extra interest saves no tax that year. In those periods τ is effectively zero. Use a per-period effective rate (τ when taxable income before interest comfortably exceeds interest, zero when it is negative) and the backward formula still works. The awkward periods are the ones that straddle the line.
- Tax paid in arrears. If interest in period t changes tax paid in period t+1, the shield sits in the wrong column and the clean recursion is gone. On semi-annual models some people accept the timing approximation for sizing and check the resulting DSCRs. Others keep a small, well-labelled circularity here.
- Fees and IDC that scale with debt. Upfront fees and interest during construction feed project cost, which feeds the gearing cap. That loop has its own algebraic fix: if gearing g binds and fees are f per rand of debt, debt equals g times cost before fees, divided by (1 minus g times f). IDC is messier and probably a post of its own.
- More than one sizing case. Lenders often size on a base case and a downside case (P90 yield on a renewables deal, say) at different DSCRs and take the lower debt. Run the recursion twice and take the minimum, alongside the gearing cap.
If you end up with a residual loop, Excel’s iterative calculation with a circuit breaker switch is less shameful than it used to be. Just make the switch obvious and test that the model rebuilds from zero.
Four checks before the bank sees it
- Closing balance in the final repayment period is zero, and the debt is fully repaid before the tail the lenders want.
- DSCR is flat at target in every repayment period, using post-tax CFADS from the tax block, not CFADS*.
- The forward closing balance equals the next period’s backward opening balance in every column.
- Change the target DSCR by 0.05x and watch the debt move with no button pressed. If you reach for F9, something is still circular.
If you want the longer version, Hugh Hawarden and I cover sculpting in our project finance modelling book. You can also watch my debt sculpting explainer video. And if you still prefer the macro, fine. Just set the loop counter to something sensible and check your signs.