Iterative Calculation Breaks Data Table

[BostonAnalyst]

Background:

I have been working on a pretty typical financial model that requires a circular reference (i.e. the amount of debt for a project is dependent on total project cost, which is dependent on the amount of debt, etc.). So I have turned on iterative calculation to handle this.

The model has a large 12-scenario sensitivity table in the first tab that is formatted using a one-dimensional data table. The data table pulls data from throughout the model, including many cells that are affected by the iterative calculation.

The model is set to calculate automatically, except for data tables.

Issue:

When I link the two cells necessary to make the circular reference, the model works as it should. It iterates and gives a sensible solution. When I go throughout the model and change difference inputs, the model changes accordingly. So far so good.

But when I hit F9 and calculate the data table on the first page, I receive #NA errors in the cells that are affected by the circular reference. Also, throughout the model, the cells affected by the circular reference also have the #NA errors. I then need to untie the circular reference and tie it back, then I'm back to square one.

Anyone have any ideas on what might be happening? I've created other models that were very similar to this using the same method (iterative calculation + data table) and haven't had any issues.

Any advice would be appreciated.

 

[DRSteele]

I spent decades as an analyst driving a model that uses iterative recalc to mimic the capital investment process. I don't know exactly what's wrong with your model, but I would suggest you vary the iteration parameters in File|Options|Formulas; start with 1 iteration and precision set to 1.000 and select manual for re-calc. Then change some numbers in the workbook, hit f9 and note where the NA errors occur. Then change to 10 iterations and precision set to 0.100, etc. It just might be that the infinite recalcs and high precision under auto recalc are too high, creating overflow. It might also be that Data tables are somehow picky about their inputs.

 

[BostonAnalyst]

Thanks for that suggestion. I've jiggered the iteration parameters a bit, but not in any rigorous way. I'll try that and report back.

 

[BostonAnalyst]

Tinkered with the iteration parameters and still no dice. I think it might have to do with the sheer size of the model. Any other suggestions are welcome, though.