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

Assessing multi-period household income vulnerability requires directional transition measures capable of capturing origin-dependent social risk alongside multi-tier destination deprivation penalties. The Jenkins-Cowell multi-tier mobility framework combines origin status sensitivity with Cowell-style destination penalties and tiered population decile brackets, penalising downward transitions that trap families in bottom-tier economic brackets. By implementing dynamic LAMBDA and VSTACK algorithms in Microsoft Excel, quantitative researchers can construct real-time Jenkins-Cowell multi-tier downside mobility models without external statistical packages.

Mathematical Foundations of Jenkins-Cowell Multi-Tier Downside Mobility

The Jenkins-Cowell multi-tier downside framework incorporates an origin-sensitivity parameter $alpha > 0$ and a multi-tier 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$:

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

When all parameters are applied, 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 JCMT_COWELL_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),
        tier_weight, IF(is_downside, 1 + ((n - col_seq) / n), 0),
        weighted_loss, IF(is_downside, transition_matrix * rel_drop * poverty_penalty * tier_weight, 0),
        jcmt_score, SUM(weighted_loss),
        jcmt_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.4,
    beta, 2.0,
    jcmt1, JCMT_COWELL_DOWNSIDE(cohort1990, alpha, beta),
    jcmt2, JCMT_COWELL_DOWNSIDE(cohort2000, alpha, beta),
    jcmt3, JCMT_COWELL_DOWNSIDE(cohort2010, alpha, beta),
    jcmt4, JCMT_COWELL_DOWNSIDE(cohort2020, alpha, beta),
    headers, HSTACK("Observation Window", "Jenkins-Cowell Multi-Tier Downside Index"),
    r1, HSTACK("1990-2000", jcmt1),
    r2, HSTACK("2000-2010", jcmt2),
    r3, HSTACK("2010-2020", jcmt3),
    r4, HSTACK("2020-2030 (Est)", jcmt4),
    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.