Solutions to Excel and model circular references

Description
PROBLEM
I have a circular reference in my model

SOLUTION
Circularity solutions
This paper gives working details of the four solutions to circular references in Excel models.

CIRCULAR REFERENCES
A circular reference is when a formula either directly references itself or indirectly references other cells that reference it.

THERE ARE FOUR SOLUTIONS
1/ Allow the circular reference and turn ‘iterative calculations’ on in Calculation options within Excel Options – this is an exact solution
2/ Carry out a modelling approximation to avoid the circular reference by calculating off the previous period value or balance – this is an approximate solution
3/ Rearrange the formula – this is an exact solution
4/ Use a macro to break the circular reference – this is an exact solution

EXAMPLE CIRCULAR REFERENCE PROBLEM
We consider the interest on cash problem within a financial model. The interest on cash is part of the Cash carried forward calculation that is itself part of the interest on cash calculation. This calculation regularly gives rise to a circular reference in Excel models.
This paper discusses, in detail, the four solutions to this problem.

INCLUDED IN THIS BEST PRACTICE
1/ PDF paper detailing the four solutions
2/ XLSM file with problem and 4 solutions
3/ VBA code for 4th solution