When you enter formulae which include references to other data, it is easy to create circularities, either accidentally or deliberately. STEM will optionally take an iterative approach when it detects circular references in such formulae. For example, if two scalar or time-series inputs,
x
and y are defined as:
x = 1 + y (1)
y = 0.5 · x (2)
STEM will calculate the values x = 2 and y
= 1 simply by re-calculating the two formulae over and over in turn until the values converge.
This iteration starts from the values held by x and y
before the formulae were entered, and converges quickly if the previous values were zero. However, if you first enter a very large value for
y, such as 1e+100, and then re-enter the formula, STEM will not reach these equilibrium values in the default 100 iterations. You can now press <F9> to force STEM to re-calculate all formulae, and effect a further 100 iterations.
You can configure this behaviour:
- Select Calculation… from the Options menu. The Calculation Options dialog is displayed.
- Enter the desired options – see Figure below.
- Press <Enter> or click OK to close the dialog.
The Calculation Options dialog
These options are governed independently in the Editor and Results programs, so, for example, you can choose to prevent any circularities in the Editor, whilst retaining the iterative solution for the balance sheet.
Note: For technical reasons, the <F9> accelerator does not work when this dialog is visible, but you can press <Alt+N> or click the Iterate Now button to see the immediate effect of changing any options.
Note: The iteration options are governed separately in the Editor and Results programs
Divergence and uniqueness
The previous example was carefully chosen to demonstrate how iteration can
be used successfully to resolve a set of circular references, and indeed the uniqueness of the solution can easily be verified by substituting the definition of
y
in (2) into (1) and re-arranging to show that 0.5 · x = 1, whence x = 2 and y = 1. The fact that it converges is intuitively obvious from the fact that, for each iteration, STEM adds 1 to the value for
y, and then divides by 2, so whatever the initial value, it will rapidly progress towards 1 as the iteration progresses.
However for a general set of circular equations, there is no such guarantee of convergence, nor even uniqueness. If the 0.5 in the previous example is replaced by 2, giving:
x = 1 + y
y = 2 · x
you will see that the values grow in magnitude with each iteration, quickly reaching floating-point overflow conditions (± effective infinity), so that in this case iteration is not successful. In fact the same substitution technique reveals that there
is
a unique solution to these equations, x = –1 and y
= –2, and indeed, if you first enter the value –2 for y, and then re-enter the formula, STEM will not diverge from those particular values. This is an
unstable equilibrium, which means that any initial value for y, other than exactly –2, will cause the values to diverge rapidly.
As a more interesting example, try setting:
x = 2 + y
y = 3/x
being careful to define x first in order to avoid division by zero! If the initial value for y
is not –2, STEM will quickly calculate x = 3 and y
= 1; but using substitution again you can see that there is in fact another solution, x
= –1 and y = –3. However, a little experimentation shows this second equilibrium to be unstable.
The purpose of these examples is simply to show that iteration cannot provide a universal solution to the general problem, x = f(x), and that care must be taken to ensure that any circular references you choose to introduce into a series of inputs or results collectively pose a problem which has a convergent and unique solution.