Recurrence: balances that roll forward

A dataseries can refer to its own value in an earlier period — the roll-forward at the heart of budgets, forecasts and three-statement models. In Axmo you write the relationship once and it holds across the whole model.

Almost every financial model has a number that depends on its own past. A cash balance is last month's balance plus this month's flows. A loan's opening balance is last period's closing balance. Retained earnings accumulate. This pattern — this period equals last period plus what changed — is called a roll-forward, and it's the heart of budgets, forecasts, and three-statement models.

In a plain spreadsheet, roll-forwards are copied rather than written. You put the relationship in one cell — this month's balance is last month's, plus this month's flows — and fill it across the row, so the model becomes a chain of near-identical formulas that holds only while nobody inserts a column or deletes a period. Excel 365's SCAN does better, building the column in one formula that survives inserted rows. But make the flows depend on the balance — interest on it, or a floor under it — and both turn circular. That is where a spreadsheet stops.

Writing a roll-forward

In Axmo, you write the roll-forward directly. A dataseries can refer to its own value in the previous period:

Balance = Balance[Month[-1]] + NetCashFlow

Balance[Month[-1]] means "the Balance one month back." Axmo sees that Balance depends on its own earlier value, recognises the pattern as a valid recurrence, and computes it period by period — January, then February from January's result, and on down the timeline. No circular-reference error, no fragile copy-down.

The opening balance

A roll-forward has to start somewhere, and the two questions there are easy to run together. The first is what happens at the edge of the dimension: the month before the first month is not a cell, and reading it gives you nothing to add. A bare Balance = Balance[Month[-1]] + NetCashFlow therefore runs from the first period with nothing added to it, and needs no guard.

That is true of a roll-forward that adds. One that multiplies — growth at a rate, 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.

The second is whether your model has an opening balance — a figure it starts from, which is a fact about your business rather than about the shape of Time. Axmo invents none, and there is nothing to infer: a balance sheet that opens at zero and one that opens at a million are different models.

A sequential dimension like Time can carry a boundary element called BEFORE, the position immediately ahead of the first period. It is an ordinary coordinate — nothing is written there unless you write it — and it is the natural home for an opening balance. Put 1 000 there and January computes from it. Because the balance is a calculated series, loading a value into it needs the model setting Allow data load into cyclic progressive dataseries, in Model Properties; it is off by default.

You can also supply it in the formula, or load it with the rest of the series — Recurrence rules sets out the three ways.

Richer roll-forwards

You can build richer roll-forwards the same way. An opening and closing balance that reference each other; interest charged on last period's balance; a debt schedule that amortises over time — all of it is just dataseries referring to earlier periods, and to one another. Axmo works out the order to calculate them so the numbers come out right.

A spreadsheet can produce a single rolling balance, several ways. What it cannot do is let the balance take part in its own calculation, or hold that relationship once across every dimension of the model. Power Pivot cannot project one at all — its opening- and closing-balance functions read a balance that is already in your data. That is the difference: you're not describing data you already have, you're building the model that produces it.