< All Topics
Print

Circular references

A circular reference is where a calculation refers to itself directly or indirectly. For example, it’s easy to accidentally create one with interest, which is cash times an interest rate, but since interest is paid from cash, cash depends on interest which in turn depends on cash. Modellers know to avoid this by calculating interest from cash before interest, but sometimes circular references aren’t so obvious or so easy to avoid.

Detecting circularities

Openbox will automatically detect and warn about circular references during validation . It will tell you whether there are any and which calculations are involved. Note that it will detect all circularities but only tell you about one at a time. 

Dealing with circularities

Circular references are generally seen as bad practice in financial models. The options here are:

  1. Change the formulas to remove the circularity – for example, by using opening balances rather than closing balances. You will need to judge whether this is a reasonable approximation for your modelling.
  2. Continue with the circularity and use Excel’s iterative calculation mode.  
  3. Use a copy/ paste block with associated macro to iterate the model. This is the standard option.

For Option 3, you need to specify where to break the circularity – at which calculation. In a future version of Openbox, it will suggest where to break it.

Openbox will then add new items with “copy” and “paste” suffixes, and a macro. When you get to the Excel model, you press a button to run the macro. It calculates the spreadsheet, then copies the  values in the “copy” cells and pastes them into the “paste” cells. Then it calculates again and checks if the “copy” cells have changed. If they have not changed (much) then it stops, otherwise it repeats the process. This should make your model converge to the expected answer.

This is an expert feature, and we recommend you familiarize yourself with the approach before using it. You can find more information in the Financial Modelling Handbook here

Breaking circularity manually

If you know already that your model contains a circularity, you can break it by right clicking on the calculation you want to break it at, and selecting “Break circularity”. 

Openbox will insert copy/ paste blocks, and the circularity macro, as described earlier.

Circularity in the preview

When Openbox creates a preview of a model with a copy/ paste macro, it will ask you if you want to run that macro.

For very large models, this could take 30 seconds or more, because the model has to be recalculated many times.

Troubleshooting

Because the “copy” and “paste” blocks are standard Openbox calculations, they could be modified or deleted. If this happens, Openbox will warn you during validation.

As part of the process, Openbox tries to insert VBA into your model, as noted above. Excel’s security settings can prevent this. To allow access:

  1. Open Excel
  2. Click “File” then “Options”
  3. Click “Trust Center” then “Trust Center settings”
  4. Click “Macro settings”
  5. Tick the “Trust access to the VBA object model” box.
  6. Click OK as necessary until you get back to the main Excel window.
  7. Close and restart Excel.

Note that this can cause security risks, so please confirm with your organization before doing it. We only offer these steps as information and don’t take responsibility for any consequences.

See https://answers.microsoft.com/en-us/msoffice/forum/all/trust-access-to-the-vba-project-object-model/fb184cc1-ea20-443d-9a2a-9d68e6ef7564 for more information.

Leave a Reply

Your email address will not be published. Required fields are marked *

Your Cart

No Item Found
Subtotal 0.00
Shipping 0.00
Tax 0.00
Total 0.00
0