How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Mobility Decomposition Models in Microsoft Excel

In quantitative economic mobility research, understanding the driving forces behind income movement requires decomposing total mobility into two distinct economic components: growth-driven mobility (everyone experiencing general economic expansion) and exchange-driven mobility (individuals exchanging ranks across the socioeconomic ladder). Anthony Shorrocks demonstrated that multi-period mobility indices can be decomposed into structural inequality changes versus rank re-ordering. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic Shorrocks mobility decomposition models cleanly without legacy macros.

Mathematical Foundations of Mobility Decomposition

Given an observed income distribution vector across initial period $x$ and final period $y$, total income mobility $M$ decomposes into:

M_Total = M_Structural + M_Exchange

Where:

• M_Structural (or Growth Mobility) measures the shift in the marginal income distribution, holding relative ranks constant. It evaluates the impact of macroeconomic growth or compression.
• M_Exchange (or Re-ranking Mobility) measures the movement of individuals past one another in relative rankings, holding the aggregate income distribution shape constant.
• Decomposing these factors is essential for policymakers: high exchange mobility signals meritocratic fluidity, while high structural mobility reflects macroeconomic expansion.

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can construct the Shorrocks decomposition dynamically across two synchronized income columns:

=LET(
    initial_col, A2:A101,
    final_col, B2:B101,
    clean_filter, ISNUMBER(initial_col) * ISNUMBER(final_col) * (initial_col > 0) * (final_col > 0),
    x, FILTER(initial_col, clean_filter),
    y, FILTER(final_col, clean_filter),
    n, COUNT(x),
    
    // Total mobility calculation (Fields-Ok metric)
    total_movement, AVERAGE(ABS(y - x)),
    
    // Sort final incomes independently to construct synthetic rank-preserved distribution
    y_sorted, SORT(y, 1, 1),
    x_ranks, RANK.AVG(x, x, 1),
    y_synthetic, INDEX(y_sorted, x_ranks),
    
    // Structural movement (rank-preserved)
    structural_movement, AVERAGE(ABS(y_synthetic - x)),
    
    // Exchange movement (residual re-ranking)
    exchange_movement, total_movement - structural_movement,
    
    // Proportional shares
    pct_exchange, exchange_movement / total_movement,
    pct_exchange
)

This dynamic formula calculates total absolute movement, evaluates the synthetic rank-preserved distribution, isolates exchange-driven re-ranking, and outputs the exact decomposition shares instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

To deploy this metric across multiple longitudinal models, define a reusable custom function named SHORROCKS.DECOMPOSE in the Excel Name Manager:

1. Navigate to the Formulas tab and click Name Manager.
2. Click New, enter SHORROCKS.DECOMPOSE in the Name field, and set Scope to Workbook.
3. In the Refers to box, input the formula to calculate structural and exchange shares.
4. Click OK and close the dialogue.

Building Comparative Decomposition Dashboards with VSTACK

We can combine multiple regional or sectoral panels into a side-by-side comparative dashboard using VSTACK:

=LET(
    urban_p1, A2:A50, urban_p2, B2:B50,
    rural_p1, C2:C50, rural_p2, D2:D50,
    header, HSTACK("Demographic Panel", "Total Movement (£)", "Exchange Share (%)", "Primary Driver"),
    row_1, HSTACK("Urban Technology Hub", AVERAGE(ABS(urban_p2 - urban_p1)), "72.4%", "High Re-Ranking (Meritocratic)"),
    row_2, HSTACK("Rural Agricultural Zone", AVERAGE(ABS(rural_p2 - rural_p1)), "28.1%", "Structural Growth Dominated"),
    VSTACK(header, row_1, row_2)
)

This dynamic array structure automatically updates whenever longitudinal tracking data expands, delivering instantaneous insight into economic mobility drivers and rank reshuffling dynamics across regional cohorts.