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

Assessing multi-period household income vulnerability requires directional transition measures capable of capturing origin-dependent social risk alongside destination poverty severity. The Jenkins-Cowell mobility framework combines origin status sensitivity with destination deprivation weighting, penalising downward transitions that lead to persistent deprivation. By implementing dynamic LAMBDA and VSTACK algorithms in Microsoft Excel, quantitative researchers can construct real-time Jenkins-Cowell downside mobility models without external statistical packages.

Mathematical Foundations of Jenkins-Cowell Downside Mobility

The Jenkins-Cowell downside framework incorporates an origin-sensitivity parameter $alpha > 0$ and a destination-aversion parameter $eta > 0$. For an $n imes n$ decile transition matrix $P$ where $P_{ij}$ represents the probability of moving from decile $i$ to decile $j$:

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

When both parameters exceed 1.0, downward transitions from middle-income deciles into chronic poverty receive compounding ethical penalties on the aggregate mobility index.

Authoring the Dynamic LAMBDA Formula

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

=LAMBDA(transition_matrix, alpha_param, beta_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) ^ alpha_param, 0),
        poverty_penalty, IF(is_downside, 1 - ((col_seq / n) ^ beta_param), 0),
        weighted_loss, IF(is_downside, transition_matrix * rel_drop * poverty_penalty, 0),
        jc_score, SUM(weighted_loss),
        jc_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],
    alpha, 1.5,
    beta, 2.0,
    jc1, JC_DOWNSIDE(cohort1990, alpha, beta),
    jc2, JC_DOWNSIDE(cohort2000, alpha, beta),
    jc3, JC_DOWNSIDE(cohort2010, alpha, beta),
    jc4, JC_DOWNSIDE(cohort2020, alpha, beta),
    headers, HSTACK("Observation Window", "Jenkins-Cowell Downside Vulnerability Index"),
    r1, HSTACK("1990-2000", jc1),
    r2, HSTACK("2000-2010", jc2),
    r3, HSTACK("2010-2020", jc3),
    r4, HSTACK("2020-2030 (Est)", jc4),
    VSTACK(headers, r1, r2, r3, r4)
)

Sensitivity Testing and Model Verification

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