Financial
Running Cumulative Balance, Compound Wealth Index, and High-Water Mark (SCAN & REDUCE Formulas)
Generate step-by-step intermediate accumulator states (running cash balance, compounded wealth index, and running high-water mark) alongside terminal totals.
Intermediate state trajectories (SCAN) vs. terminal state (REDUCE)
In functional spreadsheet modeling, SCAN outputs the running accumulator after every period—such as an account's ending cash balance after each deposit/withdrawal or an investment's compounded NAV index—whereas REDUCE outputs only the final ending state.
Anchored expanding ranges like =$G$2 + SUM($B$2:B2) for additive cash flows, step-chained compounding E3 = ROUND(E2 * (1 + D3), 4) for multiplicative returns, and MAX($E$2:E3) for high-water marks expose every intermediate step cleanly for audit trails.
| Period | Net Cash Flow | Running Cash (SCAN) | Periodic Return | Wealth Index (Base 100) |
|---|---|---|---|---|
| P1 | +250 | 1,250 | +10.0% | 110.000 |
| P2 | -180 | 1,070 | -5.0% | 104.500 (HWM = 110) |
| P3 | +320 | 1,390 | +8.0% | 112.860 |
| P4 | -90 | 1,300 (Terminal) | +5.0% | 118.503 (Terminal) |
Anchored Expanding Cash Balance & Terminal REDUCE Check
Adds cumulative net cash flows from $B$2 through the current row to opening balance $G$2.
=$G$2 + SUM($B$2:B5)Compounded Wealth Index & Running High-Water Mark
Tracks compounded base-100 wealth across periodic returns D2:D5 and captures the highest level achieved through any period.
=MAX(E2:E5)Try the example
Use the original values or change them to test your understanding.