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

Assessing multi-period income vulnerability requires directional transition measures capable of capturing origin-dependent social risk. The Jenkins-Shorrocks mobility framework adapts rank-dependent social evaluation functions to transition dynamics, weighting downward shifts according to the cumulative mass of individuals occupying lower strata. By implementing dynamic LAMBDA and VSTACK algorithms in Microsoft Excel, quantitative researchers can construct real-time Jenkins-Shorrocks downside mobility models without external statistical packages.

Mathematical Foundations of Jenkins-Shorrocks Downside Mobility

The Jenkins-Shorrocks downside framework incorporates a rank-weighting distribution parameter $mu > 1$. For an $n imes n$ decile transition matrix $P$ where $P_{ij}$ represents the probability of moving from decile $i$ to decile $j$:

$$JS_{down} = sum_{i > j} P_{ij} cdot left(rac{i – j}{i} ight) cdot left[1 – left(rac{j}{n} ight)^mu ight]$$

When $mu > 1$, downward transitions landing in the lowest income tiers exert an increasingly heavy penalty on the aggregate mobility index, highlighting societal vulnerability.

Authoring the Dynamic LAMBDA Formula

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

=LAMBDA(transition_matrix, mu_param,
    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,
        rel_drop, IF(is_downside, (row_seq - col_seq) / row_seq, 0),
        poverty_weight, IF(is_downside, 1 - ((col_seq / n) ^ mu_param), 0),
        weighted_loss, IF(is_downside, transition_matrix * rel_drop * poverty_weight, 0),
        js_score, SUM(weighted_loss),
        js_score
    )
)

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],
    mu, 2.5,
    js1, JS_DOWNSIDE(cohort1990, mu),
    js2, JS_DOWNSIDE(cohort2000, mu),
    js3, JS_DOWNSIDE(cohort2010, mu),
    js4, JS_DOWNSIDE(cohort2020, mu),
    headers, HSTACK("Observation Window", "Jenkins-Shorrocks Downside Vulnerability Index"),
    r1, HSTACK("1990-2000", js1),
    r2, HSTACK("2000-2010", js2),
    r3, HSTACK("2010-2020", js3),
    r4, HSTACK("2020-2030 (Est)", js4),
    VSTACK(headers, r1, r2, r3, r4)
)

Sensitivity Testing and Model Verification

Ensure that all transition matrix rows sum to 100%. Adjusting $mu$ in linked cells allows researchers to stress-test differing degrees of social risk aversion across all stacked cohort comparisons simultaneously.