How to Calculate Cumulative Growth Rates with Excel Dynamic Arrays
Question details
The user needs to calculate compounded, sequential growth over multiple periods using a starting value and a named range of growth percentages.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying successive growth percentages sequentially so that each period's rate compounds based on the result of the previous period.
- Observed behavior
- Using a standard array formula like A1*(1+TR) fails because it applies every rate in the array to the single starting value rather than compounding sequentially.
Ensure you are using Microsoft 365 or Excel 2021 and later, as the functions required to perform dynamic array iterations (SCAN and LAMBDA) are not available in older versions.
Use the SCAN and LAMBDA Functions
This method uses SCAN to iterate through the array of rates and LAMBDA to apply a custom compounding calculation to each step.
The SCAN function applies a LAMBDA function to each value in an array and returns an array that contains each intermediate value. This makes it perfect for running totals or cumulative compounding tasks.
Click on the empty cell where you want the sequence of cumulative growth results to begin spilling.
Type the formula: =SCAN(A1, TR, LAMBDA(previous, rate, previous*(1+rate))). Replace 'A1' with the cell reference of your initial starting value, and 'TR' with your array or named range of growth percentages.
Press Enter. The formula will automatically evaluate each rate against the previously calculated result and spill the compounded values into adjacent cells.
Use SCAN with the PRODUCT Function
A concise alternative using the built-in PRODUCT function for compounding, ideal if you prefer avoiding custom LAMBDA definitions.
Calculate Dynamic Arrays Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced modern array formulas, including SCAN and LAMBDA. You can seamlessly perform complex compounding and sequential calculations without altering your workflow.
- 1. Open WPS Spreadsheet: Launch WPS Office and open WPS Spreadsheet to access your data.
- 2. Load your workbook: Open the existing file that contains your initial starting values and the arrays of growth percentages.
- 3. Apply the SCAN formula: Select an empty cell and input your dynamic array formula, such as =SCAN(A1, TR, LAMBDA(a,b, a*(1+b))).
- 4. Generate the results: Press Enter to execute the formula and watch the cumulative values dynamically spill into the correct range.
Frequently Asked Questions
Why doesn't standard array multiplication work for cumulative growth?
Standard array multiplication, such as A1*(1+TR), multiplies the single starting value independently against every rate in the array. Cumulative growth requires a sequential evaluation where each period's calculation depends on the result of the preceding calculation, which standard multiplication cannot do dynamically.
What Excel versions support the SCAN and LAMBDA functions?
The SCAN and LAMBDA functions are available in Microsoft 365 and Excel 2021 or newer. If you are using Excel 2019 or earlier, these formulas will result in a #NAME? error.
How can I calculate cumulative growth without dynamic arrays?
If you don't have access to dynamic array functions, you can calculate cumulative growth manually using relative cell references. Enter your initial value in a cell (e.g., B1), and in the cell below it (B2), type =B1*(1+C2) where C2 is your growth rate. Drag this formula down the column to calculate the running results.
