The other way that we can deal with circular references is to change our assumption about when the drawdown of the debt occurs.

If this debt here was drawn on the very last day of each of the years involved, the balance for the majority of the year in the first year would've been zero, and then on the very last day, we draw some money out of our loan facility and we use that to pay for the capital expenditure. If that's the case, then we wouldn't be paying 100 in interest. We'd be paying close to zero. What we should be doing is saying it's 10%, not of 1000, but of the opening balance for the year.

And you'll notice the blue arrows. Now jump across to the right hand side to column E, because we've solved the circular reference in the first column. If I copy that across to column E, that's saying I had 1000 in debt at the beginning of the year. That's where it stayed for most of the year, and then I drew another 1100 on the last day of the year. So interest for the year is 10% of the thousand that we had in debt for most of the year. Again, we've solved the circular reference in year two, the blue arrow have now jumped to year three.

We keep copying this across. We have solved our circular reference because the amount of interest is being calculated on the amount of debt before the current year's drawdown. Therefore, we don't need to know the interest for that year to be able to work out how much debt needs to be drawn. The circular reference is solved. Now that's not going to be absolutely accurate because maybe we don't draw that down on the very last day of the year. Maybe we draw it halfway through the year or maybe on the very first day, in which case these interest numbers here are not accurate, but they do get us round the circular reference and keep our model working and updating properly as we make changes to our assumptions. The other thing we could do is use a circuit breaker. I've got a circuit breaker set up here. What goes in there is either the numbers one or zero, zero being off and one being on. And all I need to do here is include that switch in my interest calculation. So instead of minus the interest rate times by the opening balance, even if I had it on the interest rate times the middle of the year balance. There you go, there's your circular reference.

I copy that across. It all has gone wrong. It's not showing accurate numbers.

What I'm going to do is say minus if that cell, the blue cell, the circuit breaker cell, if that equals zero, then zero.

Otherwise give me the interest rate times by the balance of the debt.

I'll lock the C11 reference so it's got dollars on it, copy that across, and now turn on iterations.

The iterations work perfectly to give us an accurate result, but all I need to do is change that switch to be a zero instead of a 1.

Just pull it off the data validation menu. There we go. And now that turns off the interest calculation so I can reset the model without it causing a problem. With circularity, it's just setting all the interest to be zero. That equals zero. This is what it looks like with no circularity because there's no interest.

Then if I turn it back on, it reworks itself so I can turn circularity on or off. It does need iterations to be on, but all I've done here is added a circuit breaker in here. I'll just formula text the formula so that you can see what I've done there. I've used cell C11 and said if that's zero, don't do an interest calculation. Only if it's 1, then you take your interest rate times by the amount of debt.