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, so Balance[Month[-1]] has nothing to read and gives null. Null spreads, so a bare recurrence gives you a column of nulls: correct arithmetic, and almost certainly not what you wanted.

Axmo does not invent a starting value, and that is deliberate. An engine that quietly used zero would be making a modelling decision on your behalf, and in a balance sheet the difference between an opening balance of zero and an opening balance of something is the whole model.

So you write the base case, and you have three ways to do it.

Balance = Nz(Balance[Month[-1]], Opening Balance)

The most common: read the previous period, and where there is nothing, use an input series instead. It reads as what it is.

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

Where your Time dimension declares a boundary element, this puts the opening balance in it explicitly. The boundary element is an ordinary coordinate to Axmo — nothing about it is special to the engine, which is why it works without any rule of its own.

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

And where the answer genuinely is zero, say so rather than leaving it to be inferred.

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.