In our webinar on managing circular references, Joshua Grimm (opens in a new tab), who leads our project finance modelling courses in Asia Pacific, gave modellers a detailed look at handling circularity in financial models. Circular references often cause errors and make models harder to follow, so modellers need to know how to manage them. This article summarises the session. You can watch the full webinar.
Joshua works on renewable energy and other infrastructure models and meets circular references in them regularly. He helps modellers deal with them, particularly on our Advanced Project Finance Modelling course.
The logic of circular references
A circular reference occurs when a formula refers back to its own cell, directly or indirectly, and creates a loop. That can lead to calculation errors and makes the model hard to understand and troubleshoot. Joshua described the two common kinds: accidental circularities caused by errors in formulas, and logical circularities that come from the model’s logic itself.
“It’s important that we understand what circular references are, where they occur, and how we can pick those up. When you see a circular reference error, don’t panic. Press OK and follow the arrows to understand how and why the circularity has occurred.”
Best practice for managing circular references
- Identify and understand the circularity. When the error appears, use Excel’s tracing features to follow the arrows to the source.
- Simplify the calculation. Break a complex calculation into simpler parts to find the exact point of circularity.
- Use an algebraic solution. Where possible, simplify or reorder the calculation to remove the circularity, which may mean back-calculating or estimating values.
- Use a circularity break as a last resort. Separate the calculation that causes the circularity from the rest of the model, store its result as a hard code and use that value in later calculations.
- Use a VBA macro. For models that are updated often, a copy-paste macro can automate the circularity break.
“Ultimately, if we can solve the problem algebraically, simplifying things, reordering things, back calculating, estimating, approximating, that may actually give us a better result.”
Interest during construction: a demonstration
Joshua demonstrated how to manage the circularity that interest creates during construction. Calculating interest on the closing balance creates a circular reference, because the closing balance depends on the interest. His recommendation was to calculate interest on the opening balance and assume cash flows occur at the end of the period. That simplifies the calculation and removes the circularity.
“This is a commonly accepted simplifying assumption in project finance modelling. We define that cash flows occur at the very end of the period, and now we can use the opening balance to calculate our interest without introducing circularity.”
Summary
The webinar closed with a summary and questions from participants. Joshua stressed understanding the logic behind any circular calculation, following best practice to manage it and using tools such as VBA macros to automate circularity breaks.
“When you’re managing your circular references, definitely try to understand where the circularity is, why it’s occurring, and if it is logical. Use algebra and simplifying assumptions if appropriate, and make sure all parties involved are satisfied with the steps you are taking in the model.”
Tutorials to read next
- Copy-paste macros in financial models: a best practice approach to copy-paste macros, and how to apply the code reliably and efficiently in different situations.
- Average DSCR in financial modelling: the two ways to calculate the average debt service coverage ratio and the limits of each.
- Cash sweep analysis in project finance: how a cash sweep calculation works and what it shows about refinancing risk and repayment ability.
- The Excel IRR function and other ways to calculate IRR: how to calculate IRR in Excel and avoid the common pitfalls.
- Estimating terminal value: ways to calculate terminal value in a project finance model, often a large share of a discounted cash flow valuation.