Recurrence rules

When a series can refer to itself in Axmo, what the engine requires of it, and where the first value comes from.

A closing balance is an opening balance plus the movements, and the opening balance is last period's closing balance. Written directly, that is a series that refers to itself:

Balance = Balance[Month[-1]] + Net Cash Flow

Axmo accepts this. Most of what follows is about the small number of ways it can be written that Axmo cannot accept, and why — because when a model is rejected, this is the page that says what to change.

What makes a self-reference work

The rule is short: a reference to yourself must point somewhere other than where you are standing.

Balance[Month[-1]] points at last month, so each month can be worked out once the month before it is known, and the chain runs. A bare Balance points at the cell being computed, which is a definition of itself and has no answer.

Axmo calls a reference that moves you progressive. Almost anything that names a coordinate is progressive — an offset, a fixed position, a range, a path into a hierarchy, a named element, a set of elements. What is not progressive is a bare name, and an offset that is always zero.

The same holds when several series refer to each other rather than to themselves. Opening stock refers to last period's closing stock, closing stock refers to this period's opening stock: the two form a loop, and the loop works because one of the steps moves. Every loop needs at least one step that moves. A loop where nothing moves is rejected, and the message says so.

Where the first value comes from

At the very first period there is no period before it. Balance[Month[-1]] points outside the dimension, and a coordinate that is not in range gives way in a sum — so a bare recurrence runs from the first period with nothing added to it. It does not produce a column of errors, and it needs no guard against the shape of its own dimension.

Where the opening balance genuinely is zero, the bare formula already gives you that. Writing Nz(Balance[Month[-1]], 0) says the same thing explicitly, and some modellers prefer to see the starting point stated rather than inferred.

That holds for a recurrence that adds. One that multiplies — Value = Value[Month[-1]] * 1.1 — has nothing to multiply in the first period and stays empty all the way through, so it always needs a starting value.

What Axmo will not do is invent an opening balance. That is a fact about your model rather than about the dimension, and the difference between opening at zero and opening at something is the whole of a balance sheet. Where your model has one, you supply it, and there are three ways to do so. None is primary; which fits depends on where the number comes from.

1 — In the formula.

Balance = Nz(Balance[Month[-1]], Opening Balance) + Net Cash Flow

Read the previous period, and where there is nothing, use an input series instead. Best when the opening value is a constant of the model rather than data.

2 — As a base case on position.

Balance = IF(ISBEFORE(Time), Opening Balance, Balance[Month[-1]] + Net Cash Flow)

Explicit about which coordinate is the base. Best when the first period genuinely computes differently rather than merely lacking a predecessor. Anchor on ISBEFORE(Time) rather than on a literal position — where a dimension declares a boundary element, position 1 is that element.

3 — As data.

Balance = Balance[Month[-1]] + Net Cash Flow

Load the opening figure into the boundary element, or into the first period, from the same source as the rest of the series. The formula stays bare. Best when the opening balance comes from outside the model — a closing balance carried forward, an actual — which is the common case in financial modelling. Loading into a calculated series needs the model setting Allow data load into cyclic progressive dataseries, in Model Properties; it is off by default.

The third is the one most often forgotten, because the other two are visible in the formula and it is not. It is also frequently the most honest: an opening balance usually is data.

What the engine requires

Two requirements go beyond something must move. Both exist for the same reason: before Axmo can work out what order to calculate the cells in, it has to know which cell each reference points at — and it cannot know that if the answer depends on a value it has not calculated yet.

A position cannot depend on the loop it is inside

Balance = Balance[Month[$Lag]] + Flow

This works when Lag is an ordinary series calculated before the loop. It is rejected when Lag is part of the loop itself, because Axmo would have to know Lag to decide which cell to read, and it would have to read that cell to work out Lag.

The rule covers anything inside the brackets: offsets, positions, both ends of a range, and the conditions in a WHERE. All of it must be knowable before the loop starts.

A formula picks one kind of self-reference and keeps to it

Opening Stock = Closing Stock[Month[-1]] + Closing Stock

This is rejected. One reference moves and the other does not, and a formula that does both has no consistent reading. Split it into two series, or decide which one you meant.

What cannot be written

Two patterns are outside what this mechanism does, and both are refusals rather than gaps.

A series that reads both its past and its future. Smoothing and interpolation want this, and there is no order in which to calculate it: each cell needs a neighbour that needs it back. Where you can supply the ends explicitly, the problem becomes a forward pass and a backward one, and both of those Axmo handles.

A model that solves to a fixed point. The standard case is interest charged on the same period's closing balance — where the interest is part of the balance it is computed from. Interest charged on last period's balance is not this and Axmo handles it: the reference moves, so the chain runs. What cannot be written is a value that depends on itself inside one period, and Excel offers iterative calculation for it. Axmo does not, by design. Iterative calculation arrives at an answer by repeating until the numbers stop moving, which means the answer depends on where it started and when it stopped. The same model, computed twice, need not agree. Everything else in Axmo is built so that a model and its inputs determine its outputs exactly, and iteration would trade that away for a convenience. If this is what your model needs, Excel's own iterative calculation is the right tool for it.

What happens when it goes wrong

Most problems are found when the model compiles, and the message names the series, the formula and the part of it at fault.

One kind can only be found while calculating: a position that turns out to select the cell doing the selecting. AVG(Balance[Month[$1:$N]]) is fine until N lands on the current month, and then that cell depends on itself. Axmo names the coordinate where it happened.

A failed recalculation never leaves you with half a model. Values are computed into a fresh set and only take effect if the whole calculation succeeds. If it does not, the previous values stay on the worksheet and the error is reported — so what you are looking at is always a complete answer, even when it is the previous one.

What this is called

The machinery has a name: the Advanced Cyclic Cluster Machine. A cluster is a set of series that depend on each other in a loop — one series referring to itself is a cluster of one. For each cluster, Axmo works out which cells depend on which, sorts them into an order, and calculates them in it.

You will not meet the name in ordinary use. It appears in error messages and it is worth knowing what it refers to when it does.

Why it is worth the rules

A spreadsheet can produce a rolling balance, and it takes one formula and a fill-down. What it cannot do is hold the relationship once — the fill-down writes a copy into every cell, and every copy is a chance for the pattern to break in a row nobody looks at.

In Axmo the recurrence is written once and holds for every coordinate the series covers: every month, every country, every scenario, and every combination of them. Add a country and the recurrence is already there. That is what these rules are protecting, and it is why the engine would rather refuse a formula it cannot evaluate in a defined order than accept it and produce a number it cannot stand behind.