Issues with iterative calculations for circular references

[moezchandani]

I am working on a financial model where circular references seem to be unfortunately unavoidable. I have enabled iterative calculations to get around this. However, this causes the financial model to fluctuate wildly. My final outputs for Project IRR and Equity IRR vary from -100% to +1000% every time I iterate the model. I have tried increasing the number of iterations to 10,000 but this slows down the model significantly without solving the issue. I've also tried to reduce the complexity of the model wherever possible (and sacrificed a lot of functionality), but that doesn't help either. Is there some way to reduce or eliminate this fluctuation without removing the circular references entirely ?

 

[DRSteele]

One suggestion is that you reduce the 'Maximum Change field' by orders of magnitude. Another is that you wrap each variable in one of the ROUND functions to reduce the significant digits in the calculation. Note that an IRR calculation is inherently iterative in its own right, so adding circular references might compound the variability so that the model never focuses on a target.

Perhaps you could paste some data and desired solution here so that we can investigate.