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

While static inequality metrics (such as the Gini coefficient) capture economic disparity at a single point in time, they fail to reveal whether individuals remain trapped in their socioeconomic tiers or experience fluid upward and downward movement over time. High single-period inequality is far less concerning if longitudinal mobility is high. Anthony Shorrocks formulated the Shorrocks Mobility Index ($M$), which quantifies multi-period income mobility by evaluating the reduction in inequality when incomes are aggregated across multiple successive time horizons. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic Shorrocks mobility models cleanly without legacy VBA macros.

Mathematical Foundations of the Shorrocks Index

Given a longitudinal panel of $n$ individuals observed across $T$ discrete time periods, let $y_{it}$ represent the income of individual $i$ at time $t$. The multi-period aggregate income for individual $i$ is:

Y_i = SUM(y_it) from t=1 to T

Let $I(cdot)$ represent any scale-invariant inequality index (such as the Gini coefficient or Theil index). The discrete Shorrocks Mobility Index $M$ is defined as:

M = 1 - ( I(Y) / SUM( w_t * I(y_t) ) )

Where:

• I(Y) is the inequality of permanent aggregate multi-period incomes.
• I(y_t) is the inequality observed in single period $t$.
• w_t = mu_t / mu_Y represents the relative mean income weight of period $t$.
• If no mobility exists (individuals maintain identical relative ranks and income shares across all periods), $I(Y) = sum w_t I(y_t)$, and $M = 0$.
• If substantial mobility equalises permanent incomes over time, $I(Y) o 0$, and $M o 1$.

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can construct the two-period Shorrocks mobility calculation dynamically across two synchronized income columns (Period 1 and Period 2):

=LET(
    p1, A2:A101,
    p2, B2:B101,
    n, COUNT(p1),
    mu1, AVERAGE(p1),
    mu2, AVERAGE(p2),
    p_agg, p1 + p2,
    mu_agg, AVERAGE(p_agg),
    
    // Gini calculation for Period 1
    sort1, SORT(p1, 1, 1),
    w1, 2 * SEQUENCE(n, 1, 1, 1) - n - 1,
    gini1, (2 / (n^2 * mu1)) * SUMPRODUCT(w1, sort1),
    
    // Gini calculation for Period 2
    sort2, SORT(p2, 1, 1),
    w2, 2 * SEQUENCE(n, 1, 1, 1) - n - 1,
    gini2, (2 / (n^2 * mu2)) * SUMPRODUCT(w2, sort2),
    
    // Gini calculation for Permanent Aggregate Income
    sort_agg, SORT(p_agg, 1, 1),
    w_agg, 2 * SEQUENCE(n, 1, 1, 1) - n - 1,
    gini_agg, (2 / (n^2 * mu_agg)) * SUMPRODUCT(w_agg, sort_agg),
    
    // Weighted single-period baseline
    weighted_single, (mu1 / mu_agg) * gini1 + (mu2 / mu_agg) * gini2,
    
    // Shorrocks Mobility Index
    shorrocks_m, 1 - (gini_agg / weighted_single),
    shorrocks_m
)

This dynamic formula calculates single-period Gini scores, evaluates permanent aggregate income dispersion, and derives the exact Shorrocks mobility index instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

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

1. Navigate to the Formulas tab and click Name Manager.
2. Click New, enter SHORROCKS.MOBILITY in the Name field, and set Scope to Workbook.
3. In the Refers to box, input the formula to calculate mobility between two columns.
4. Click OK and close the dialogue.

Building Multi-Cohort Mobility Comparisons with VSTACK

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

=LET(
    gen_x_p1, A2:A50, gen_x_p2, B2:B50,
    gen_y_p1, C2:C50, gen_y_p2, D2:D50,
    header, HSTACK("Generational Cohort", "Shorrocks Mobility Index", "Interpretation"),
    row_1, HSTACK("Generation X Panel", SHORROCKS.MOBILITY(gen_x_p1, gen_x_p2), "Moderate Rank Mobility"),
    row_2, HSTACK("Millennial Panel", SHORROCKS.MOBILITY(gen_y_p1, gen_y_p2), "High Fluidity"),
    VSTACK(header, row_1, row_2)
)

This dynamic array structure automatically updates whenever longitudinal tracking data expands, delivering instantaneous insight into economic mobility and permanent inequality equalization across demographic groups.