Circular logic

Financial models commonly include circular logic. For example, if a business earns interest on its cash balance, is accumulating excess cash that the interest is simply adding to, and the interest is based on the average cash balance in each model period (the average of the opening and closing balances), there is circular logic:

1. the closing cash balance is dependent on the net cash flow in the period;
2. the net cash flow in the period is dependent on the interest earnt during the period;
3. the interest earnt during the period is dependent on the average cash balance in the period; and
4. the average cash balance in the period is depending on the closing cash balance in the period - this takes us back to the start of the list.

With some exceptions, most modellers agree that it is good practice to avoid circular references. Circular references are where a formula refers to its own cell either directly or indirectly. There are several ways of resolving circular logic without circular references.

Algebra

Some circular logic can be resolved with algebra. This approach does not always work, but when it does work it is generally the best approach.

Continuing the example from the introduction:

1. Average cash balance = (Opening cash balance + Closing cash balance) / 2
2. Interest = Average cash balance × Interest rate
3. Net cash flow = Cash flow excluding interest + Interest
4. Closing balance = Opening balance + Net cash flow
5. Closing balance = Opening balance + Cash flow excluding interest + Interest ◀ This comes from substituting 3 into 4.
6. Interest = ((Opening cash balance + Closing cash balance) / 2) × Interest rate ◀ This comes from substituting 1 into 2.
7. Interest = (Opening cash balance + Closing cash balance) × Interest rate / 2 ◀ This comes from rearranging 6.
8. Interest = (Opening cash balance + Opening balance + Cash flow excluding interest + Interest) × Interest rate / 2 ◀ This comes from substituting 5 into 7.
9. Interest = (Opening cash balance + Opening balance + Cash flow excluding interest) × Interest rate / 2 + Interest × Interest rate / 2 ◀ This comes from rearranging 8.
10. Interest × (1 - Interest rate / 2) = (Opening cash balance + Opening balance + Cash flow excluding interest) × Interest rate / 2 ◀ This comes from rearranging 9.
11. Interest = ((Opening cash balance + Opening balance + Cash flow excluding interest) × Interest rate / 2)/(1 - Interest rate / 2) ◀ This comes from rearranging 10.

In this example, the formula specified in the final step immediately above can be applied to calculate the interest in a way that entirely avoids referencing the closing balance. [Worth mentioning (1) that this will not work or can be more complicated if there are other relationships between interest earnt and net cash flow (especially non-linear relationships), and (2) why this approach is generally the best for the situations that it does work in]

Copy-paste

[Explain what it means (e.g. copy and paste values, or more generally take the calculated values from the "copy" range and apply to them as hardcoded values to the "paste" range), how to do it (both manually and code), conventions (defined names or robust table, clarity [formatting/styles], location [people have views but there is no obviously leading consensus approach], capping the number of iterations, check that tests whether logic is resolved, tolerance, improving speed [scaling and parallel steps])

If you need to copy-paste, all else equal:

• Copy-pasting a single cell is typically fastest.
• Copy-pasting an entire timeline in a single step is typically a little slower than copy-pasting a single cell. This is mainly because you usually have to do it more times for the circular logic to resolve, but that is not always the case.
• Copy-pasting each cell of a timeline, one cell at a time, is typically much slower than either of the above. You would only do this if there could be a need to recalculate between successive copy-pastes

The above bullet points include the caveat "all else equal", however when solving practical financial modelling problems you will typically not be choosing between the options while holding all else equal. For example, you might choose between (A) copy-pasting a single entire timeline, or (B) copy-pasting three different single cells. It could go either way as to which implementation solves most quickly, and there could be a big difference.

Goal seek

[Explain what it means and how to do it, and perhaps either reference the copy-paste section or combine with it to cover conventions, location, capping the number of iterations, testing whether the logic is resolved, tolerance (this can have an extra nuance over copy-paste as you can use a different tolerance and a difference scaling factor for solving vs for checking whether it is solved - although this could be described as part of "how to do it"), and improving speed (secant and parallel steps]