Originally published: 21/04/2020 09:36
Publication number: ELQ-69081-1
View all versions & Certificate

# Solutions to Excel and model circular references

Circular references in Excel models: four solutions.

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

This Best Practice includes
1x PDF paper detailing the four solutions, 1x XLSM file with problem and 4 solutions

Discuss

## Further information

### Objectives

To provide practical & working solutions to circular references in Excel models.

### Use it if

This tool is best for analysts and modellers who wish to add to their practical and theoretical modelling knowledge.
This tool is best for analysts and modellers who wish to add only best practice modelling knowledge.

See all

## Any questions on Solutions To Excel And Model Circular References?

The user community and author are here to help. Go ahead!