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

In quantitative economic mobility research, while mobility indices measure the degree to which individuals change relative positions over time, their mathematical complement—the Rigidity Index (or Immobility Ratio, $R = 1 – M$)—quantifies economic persistence and intergenerational class lock-in. Anthony Shorrocks established that evaluating rigidity across varying time aggregation horizons illuminates whether income inequality equalizes over lifetimes or remains rigidly entrenched. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic Shorrocks rigidity models cleanly without legacy macros.

Mathematical Foundations of the Shorrocks Rigidity Index

Given an income panel of $n$ individuals observed across $T$ discrete time periods, let $y_{it}$ represent the income of individual $i$ at time $t$. The aggregate multi-period permanent income is $Y_i = sum_{t=1}^T y_{it}$. The discrete Shorrocks Rigidity Index $R$ is defined as:

R = I(Y) / SUM( w_t * I(y_t) )

Where:

• I(Y) represents the inequality of permanent aggregate multi-period incomes.
• I(y_t) represents the inequality observed in single period $t$.
• w_t = mu_t / mu_Y represents the relative mean income weight of period $t$.
• $R in [0, 1]$. If $R = 1$, economic rigidity is total: multi-period aggregation provides zero equalization, meaning individuals maintain identical relative income shares indefinitely.
• If $R o 0$, economic fluidity is complete: lifetime aggregation eliminates cross-sectional inequality.

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can construct the two-period Shorrocks rigidity calculation dynamically across two synchronized income columns:

=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 Rigidity Index
    shorrocks_r, gini_agg / weighted_single,
    shorrocks_r
)

This dynamic formula calculates single-period Gini scores, evaluates permanent aggregate income dispersion, and derives the exact Shorrocks rigidity 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.RIGIDITY in the Excel Name Manager:

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

Building Comparative Rigidity Dashboards with VSTACK

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

=LET(
    cohort_A_p1, A2:A50, cohort_A_p2, B2:B50,
    cohort_B_p1, C2:C50, cohort_B_p2, D2:D50,
    header, HSTACK("Demographic Cohort", "Shorrocks Rigidity (R)", "Mobility (1 - R)", "Status"),
    row_1, HSTACK("Salaried Professionals", SHORROCKS.RIGIDITY(cohort_A_p1, cohort_A_p2), 1 - SHORROCKS.RIGIDITY(cohort_A_p1, cohort_A_p2), "High Rigidity"),
    row_2, HSTACK("Gig Economy Workers", SHORROCKS.RIGIDITY(cohort_B_p1, cohort_B_p2), 1 - SHORROCKS.RIGIDITY(cohort_B_p1, cohort_B_p2), "High Churn / Low Lock-in"),
    VSTACK(header, row_1, row_2)
)

This dynamic array structure automatically updates whenever longitudinal tracking data expands, delivering instantaneous insight into economic class lock-in and permanent inequality persistence across demographic cohorts.