The real bottleneck here is not the math. It's the mental model most of us carry about what a spreadsheet can do. The user who posted this built a 20-year reserves model, then tried to wrap it in dynamic arrays to test sensitivities across multiple years. They hit the classic wall: Excel does not support arrays of arrays, and no amount of clever nesting will change that. But the workaround is not a hack. It is a shift in how you structure the calculation itself.
What this user is doing is exactly right: they are using `MAP` to push a sensitivity array into a `LAMBDA`, and they understand that `SCAN` is the natural tool for rolling balances. The problem is that `SCAN` carries a single accumulator forward, while `MAP` returns an array of results. Combining them naively triggers the `#CALC` error because the helper functions are not designed to return a two-dimensional array from a single pass. That is not a failure of understanding. It is a limitation of the tool, and the workaround is to rethink the layout so that each year becomes its own independent calculation, rather than trying to force a single formula to hold the entire timeline.
The practical takeaway is this: if you want dynamic sensitivity tables that recalculate rolling balances across multiple scenarios, you need to separate the sensitivity dimension from the time dimension. Use `MAP` to iterate over the sensitivity array, but inside that `LAMBDA`, compute the entire 20-year balance as a single scalar result. That means using `SCAN` for the rolling balance, but returning the final year's value, or the entire series, as a row or column that `MAP` can then stack. The user's instinct to use `SCAN` is correct. The missing piece is treating the output of `SCAN` as a complete vector, not as a value to be combined with another array in the same pass.
This matters beyond this specific model. Anyone building financial projections, reserve calculations, or any multi-period forecast with sensitivity analysis will eventually hit this same wall. The solution is not to abandon dynamic arrays, but to use them with the grain of the calculation engine. That means structuring formulas so that each `LAMBDA` returns a single value, even if that value is the result of a `SCAN` over an entire timeline. It is less elegant on the surface, but it works reliably, and it is far more maintainable than dragging formulas across a grid.
If you are stuck on a similar problem, stop trying to force `MAP` and `SCAN` into a single expression. Break the problem into two layers: one for the sensitivity input, and one for the rolling calculation. Use `MAP` on the outside to loop over the sensitivities, and `SCAN` on the inside to compute the full balance series for each one. Then return the entire series as a spill range. It will not be a one-liner, but it will work, and it will teach you more about how `LAMBDA` actually thinks. That is the real unlock.