@PapaAustin
At present, I do not believe there is any completely satisfactory way of performing a simple balance calculation from a flow array using dynamic array formulas. Currently this represents the most significant roadblock that prevents Excel models becoming dynamic in their entirety.
Computationally, the most efficient solution is to buy @Charles Williams's Fast Excel add-in that has a function
= ACCUMULATE(array, startValue, factors, open)
where factors allow for interest based on a prior balance or growth calculations.
Why FastExcel V4 - Decision Models
The obvious calculation of adding the array to an offset copy of itself, works for CSE array formulas but not DA because DA does not support array breakup [CSE arrays flow just about as well as set concrete].
Other methods, such as using MMULT with an upper triangular matrix of 1s, or SUMIFS in which flow variables are added from earlier periods are O(n²) and eventually become computationally unaffordable. The latter are, however, effective for small to medium-sized arrays [20,000 terms would start to be a problem].
Even with Lambda functions one does not entirely escape problems. At the simplest level, it can be used as a 'wrapper' to make MMULT more acceptable to a non-mathematical user but that does not change the efficiency issue.
Recursion overcomes the problem of an array formula not being able to reference earlier terms of itself without generating a circular reference error. Since every level of a recursion stack contains an image of every variable, care needs to be taken not to pass large arrays. There are currently quite severe limitations on the depth allowed for recursion which require careful management [techniques involving passing data as functions to be evaluated later, rather than as arrays, or traversing the problem within a bisection tree rather than a list - not for the faint-hearted!].
