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

In quantitative economics and public policy research, assessing societal resilience requires isolating downward financial trajectories across longitudinal cohorts. The Jenkins mobility framework evaluates origin-dependent transition penalties, assigning greater social cost to descent from lower-middle strata into absolute poverty. By authoring custom LAMBDA and VSTACK dynamic array algorithms in Microsoft Excel, analysts can execute real-time Jenkins downside modeling without VBA macros.

Mathematical Foundations of Jenkins Downside Mobility

The Jenkins index parameterises origin decile weightings using an origin-sensitivity factor $gamma$. For an $n imes n$ decile transition matrix $P$ where elements $P_{ij}$ represent transitions from origin $i$ to destination $j$:

$$J_{down} = sum_{i > j} P_{ij} cdot left(rac{i – j}{i} ight)^gamma$$

Normalising the distance fallen by the origin rank $i$ places greater proportional penalty on vulnerable cohorts sliding downward.

Authoring the Dynamic LAMBDA Formula

Create a named formula JENKINS_DOWNSIDE in Excel’s Name Manager:

=LAMBDA(transition_matrix, gamma_param,
    LET(
        n, ROWS(transition_matrix),
        row_idx, SEQUENCE(n, 1, 1, 1),
        col_idx, SEQUENCE(1, n, 1, 1),
        is_downside, row_idx > col_idx,
        origin_ratio, IF(is_downside, (row_idx - col_idx) / row_idx, 0),
        weighted_loss, IF(is_downside, transition_matrix * (origin_ratio ^ gamma_param), 0),
        total_jenkins, SUM(weighted_loss),
        total_jenkins
    )
)

Stacking Multi-Period Longitudinal Results with VSTACK

Assemble sequential transition datasets into a structured executive dashboard table using VSTACK:

=LET(
    cohort1, Panel2000[#Data],
    cohort2, Panel2010[#Data],
    cohort3, Panel2020[#Data],
    gamma, 1.5,
    j1, JENKINS_DOWNSIDE(cohort1, gamma),
    j2, JENKINS_DOWNSIDE(cohort2, gamma),
    j3, JENKINS_DOWNSIDE(cohort3, gamma),
    headers, HSTACK("Observation Window", "Jenkins Downside Vulnerability Index"),
    r1, HSTACK("2000-2010", j1),
    r2, HSTACK("2010-2020", j2),
    r3, HSTACK("2020-2030 (Proj)", j3),
    VSTACK(headers, r1, r2, r3)
)

Model Validation and Diagnostic Testing

Ensure that all input matrix rows sum to exactly 1.0. Increasing the $gamma$ exponent allows researchers to run immediate stress tests on relative poverty traps directly within Excel’s reactive recalculation engine.