One of the ways around a circular reference is to turn on iterations. What that will do is calculate the interest on this line here, put that 100 into here, and then rework the amount of funding gap. So that would become 1100. That means we'd have to draw debt of 1100. That 1100 would go down into that line and it would then rework the amount of interest at 10% of the new amount. That new interest number would then go back into the interest line and we'd rework the funding gap, rework the debt, draw down, rework the interest, and we'd go round and round that loop. That's what iterations does. It's here on the file menu. You go to options formulas enable iterative calculation. So that's file, option, formulas, and then tick where it says enable iterative calculations. The default is it will go round that loop up to 100 times or until the numbers stop changing by more than that amount there.

You can change either of these two parameters. I'll leave them on the defaults for now. Let's see what happens.

Excel has gone round that loop extremely fast, and it's worked out that if we borrow 1111.1 that will give us 111 of interest. That 111 goes in here. Add the 1,000 of CapEx. That's a funding gap of 1111.1 and that is how much debt we draw. So it's gone round the loop. We started with this being 1000 of debt and 100 of interest. It's gone round the loop up to 100 times and it's worked out that if we borrowed 1,111, we have 111 of interest. We add that to the CapEx and this now works.