Circular Reference
Adding Circularity to the Debt Schedule:
Circular references, often encountered in the computation of interest expenses where calculations are interdependent, present both challenges and opportunities for financial modeling. The capacity to toggle the circular reference on and off enables users to meticulously navigate through the complexities of iterative calculations, ensuring that the model remains both robust and adaptable to varying analytical needs. This tutorial will guide you through the steps to integrate this switch, illuminating its utility in fine-tuning interest expense calculations. By equipping your financial model with this feature, you empower users with the discretion to harness or bypass circular logic based on their specific needs, thereby elevating the model's usability and precision in financial decision-making.
First step is to adjust Excel settings to allow circular references.
Go to File / Options / Formulas
Then Enable iterative calculations.
This will allow excel to go back and forth with the calculation until the model stabilizes itself.
Now we are ready to improve the Interest expense calculation in the Debt Schedule.
When we first built the debt schedule, we used the beginning balance to calculate the Interest expense ( and the interest income from excess cash as well). However, since we don’t know exactly when this debt was issued (or what month we had excess cash), it’s more precise if we use an average between beginning balance and ending balance. But since the ending balance depends on how much cash we have and cash depends on the issuance of this debt, when we use the average for this calculation we will have a circular reference in our model. A lot of users of excel do not allow circular references and the warning of a circular reference can cause discredit on the model. Besides, when Excel is not set up to allow iterative calculations, the use of these formulas can cause errors.
Let’s start creating a switch to use on our Interest Expense formulas. Create some lines in the beginning of your debt schedule and add the two options: ON and OFF with some explanation by each.
Create a combo Box
Go to developer / insert / Combo box
Draw your combo box, click with the right button on it and choose format control.
Choose the cells you wrote On and OFF as your Input range and for cell link click on an empty cell to store the result of the combo box.
Test the combo box. When toggled ON the Cell link should show 1 and toggled OFF the cell link should show 2.
Click with the right button on the cell link and define a name for it. You can call it “Circularity”.
Now everywhere in your model you can use this 1 or 2 in a IF function to switch between formulas using circular reference or not.
That’s what we will do now.
Replace the formula where you calculate the Revolving Interest Expense by
From now on, when the switch is ON, the cell you named CIRCULARITY will be showing “1” therefore the calculation will use the average between beginning and ending balance, and when the switch is OFF, the calculation will use the beginning balance, not triggering a circular reference, since the beginning balance doesn’t change with cash changes.
You can use this now everywhere you want to use a formula that triggers circular reference. A good practice is to add another switch in the cover of the model.
Make sure you leave enough documentation to user about what it means to toggle switch on.