How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Asymmetric Decile Downside Shorrocks-Prais Mobility Models in Microsoft Excel

Measuring downward mobility across longitudinal income deciles requires isolating the erosion of socio-economic status from aggregate transition noise. The Shorrocks-Prais framework evaluates the trace of the transition matrix relative to perfect mobility. By decomposing the formulation to evaluate strictly downside departures and authoring it in modern Microsoft Excel with VSTACK and LAMBDA, analysts can build real-time econometric models without legacy macros.

Mathematical Foundations of Downside Shorrocks-Prais Mobility

The classical Prais-Shorrocks index evaluates $M(P) = (n – ext{Tr}(P)) / (n – 1)$. To isolate downside mobility, we evaluate the proportion of diagonal departure attributable strictly to downward decile transitions $i > j$:

$$M_{SP, down} = rac{1}{n – 1} sum_{i > j} P_{ij} cdot left(rac{i – j}{n} ight)$$

Authoring the Dynamic LAMBDA Formula

Create a named formula SHORROCKS_PRAIS_DOWNSIDE inside Excel’s Name Manager:

=LAMBDA(transition_matrix,
    LET(
        n, ROWS(transition_matrix),
        row_seq, SEQUENCE(n, 1, 1, 1),
        col_seq, SEQUENCE(1, n, 1, 1),
        is_downside, row_seq > col_seq,
        distance_factor, IF(is_downside, (row_seq - col_seq) / n, 0),
        weighted_val, IF(is_downside, transition_matrix * distance_factor, 0),
        prais_downside, SUM(weighted_val) / (n - 1),
        prais_downside
    )
)

Stacking Multi-Period Longitudinal Results with VSTACK

Consolidate multi-decade panel survey data into an automated summary dashboard using VSTACK:

=LET(
    cohort1990, Panel1990[#Data],
    cohort2000, Panel2000[#Data],
    cohort2010, Panel2010[#Data],
    cohort2020, Panel2020[#Data],
    sp1, SHORROCKS_PRAIS_DOWNSIDE(cohort1990),
    sp2, SHORROCKS_PRAIS_DOWNSIDE(cohort2000),
    sp3, SHORROCKS_PRAIS_DOWNSIDE(cohort2010),
    sp4, SHORROCKS_PRAIS_DOWNSIDE(cohort2020),
    headers, HSTACK("Observation Window", "Downside Shorrocks-Prais Index"),
    r1, HSTACK("1990-2000", sp1),
    r2, HSTACK("2000-2010", sp2),
    r3, HSTACK("2010-2020", sp3),
    r4, HSTACK("2020-2030 (Est)", sp4),
    VSTACK(headers, r1, r2, r3, r4)
)

Sensitivity Testing and Model Verification

Ensure that all transition matrix rows sum to 100%. Dynamic array updates allow instant recalculation across stacked time series tables whenever underlying population survey frequencies change.