Interest on Average Debt: Macro, Iteration or Algebra

Somewhere in most project finance models there’s a cell calculating interest on the average of opening and closing debt. Somewhere near it there’s a button. Mine was wired to a macro called Funding_Paste. (More on that shortly. It’s five lines long and it does the job.)

Interest on the average balance creates a circular reference and Excel complains. You then have three ways out: a macro that copies and pastes until things stop moving, Excel’s iterative calculation setting, or a bit of algebra. Most of us have used all three. The more useful question is which one, and when, and whether you needed the loop at all.

Check you need the average first

Average balance interest is a habit people bring over from corporate models. Debt moves at some unknown point during an annual period, so the average is a fair guess.

Project finance debt doesn’t behave like that. Repayments land on fixed dates, normally every six months, and interest accrues on what’s outstanding over the interest period. Line your model periods up with the payment dates and interest is just opening balance times rate. No loop. It’s also what the agent bank will actually bill.

So read the interest clause before you write any VBA. In operations the circularity usually doesn’t need to exist. Worse, the average version understates interest in any period with a big repayment, because it quietly assumes half the repayment happened on day one.

Construction is where it’s real

During construction you draw throughout the period to pay the EPC contractor, so the opening balance understates what was really outstanding. Interest during construction (IDC) is capitalised and funded, partly by the same loan. Drawdown drives the balance, the balance drives IDC, IDC drives the funding requirement, and that drives the drawdown. Round you go.

With gearing g, a period rate r and capex C:

Draw_t = g × (C_t + IDC_t)

IDC_t = r × (Open_t + Draw_t / 2)

The half drawdown is the usual mid-period assumption. Commitment fees, upfront fees and DSRA funding tangle themselves into the same loop, but IDC does most of the damage, so I’ll stick with it.

The copy-paste macro

Here’s mine, more or less as I wrote it:

Sub Funding_Paste()

    Do While Range("Funding_Delta") <> 0

        Range("Funding_Paste").Value = Range("Funding_Copy").Value

    Loop

End Sub

Three named ranges do the work. Funding_Copy is the calculated funding row. Funding_Paste is a row of hard-coded values that the rest of the model reads instead. Funding_Delta adds up the differences between the two. Paste, let Excel recalculate, check, repeat.

It’s lean: one line of work inside the loop, no counters or scaffolding, and anyone who knows the range names can follow it. The one tweak worth making is to the exit test, which waits for the delta to hit exactly zero. Floating point arithmetic doesn’t always oblige, and a loop sitting 0.0000001 away will run until someone finds Ctrl+Break. A safer version checks against a tolerance and gives up after a set number of passes:

Sub Funding_Paste()

    Dim i As Long

    Do While Abs(Range("Funding_Delta").Value) > 0.0001 And i < 100
        Range("Funding_Paste").Value = Range("Funding_Copy").Value
        i = i + 1
    Loop

    If i = 100 Then MsgBox "Funding has not converged"

End Sub

The bigger problems aren’t in the code. Change an input and the pasted values are stale until someone presses the button. Data tables recalculate without running macros, so every sensitivity built on one uses the base case funding. And lenders’ advisers often open models with macros disabled, which turns your button into a decorative rectangle.

Iterative calculation

File, Options, Formulas, tick Enable iterative calculation. Excel loops the circular cells up to 100 times by default and stops early once nothing moves by more than 0.001. An IDC loop usually settles in a few passes.

The trouble is everything else it does. An error inside the loop, say a #DIV/0! from a blank input, gets passed round and round and is still there after you’ve fixed the input. So you need a circuit breaker, a switch that zeroes IDC and lets the loop start clean.

The setting also comes from the first workbook Excel opens in a session, so your model can behave differently on someone else’s laptop. It stops Excel warning you about new circular references, including the one a colleague adds by accident next month. And if it hasn’t converged after 100 passes it just stops and shows you the last one. No message.

The algebra

Put the drawdown formula into the IDC formula and IDC turns up on both sides. Collect terms:

IDC_t = (r × Open_t + ½ × r × g × C_t) / (1 − ½ × r × g)

Everything on the right is known at the start of the period. Work out IDC, then drawdown, then the closing balance. No loop, no button. I tested it against a brute-force loop over five periods at 4.5% a period and 75% gearing, and they agree to six decimal places.

In the sheet it looks like this:

Interest during construction formulas for the period in column H

Row	What it holds	Formula for period in column H
20	Capex	From the construction schedule
21	Opening debt	=G24
22	IDC	=(H21*Rate+0.5*Rate*Gearing*H20)/(1-0.5*Rate*Gearing)
23	Debt drawdown	=Gearing*(H20+H22)
24	Closing debt	=H21+H23
25	Check	=H22-Rate*(H21+H23/2), should be zero

Row 25 is the one people skip. Keep it.

The same move works for interest earned on average cash, or a loan that capitalises interest on its average balance:

Close_t = [Open_t × (1 + r / 2) + Flow_t] / (1 − r / 2)

It falls over on anything non-linear. Put a MIN, MAX or IF inside the loop (a facility limit, a sweep capped at available cash, tax losses switching on and off) and there’s no tidy rearrangement left. That’s where the macro earns its keep.

So which one?

My order now: opening balance in operations if the facility agreement allows it. Algebra for construction, with the check row underneath and the derivation written down somewhere so the next modeller doesn’t think it’s witchcraft. The macro, with a tolerance and a counter, for loops that run through a MIN or MAX. Iterative calculation last, with a circuit breaker and a note on the cover sheet saying it’s switched on.

The sculpting version of this problem, where the loop runs through the tax shield, is in the previous post: Sculpting Debt to a Target DSCR Without a Macro.