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

Evaluating economic mobility requires quantifying the asymmetry between upward progress and downward vulnerability. The Arndt downside mobility measure weights negative socio-economic transitions based on the distance fallen across decile brackets. Utilizing Microsoft Excel’s dynamic array calculation engine, analysts can build flexible Arndt mobility models via custom LAMBDA and VSTACK structures without legacy visual basic scripts.

Mathematical Concept of Arndt Downside Mobility

The Arndt formulation assigns quadratic or exponential weightings to transition matrix probabilities $P_{ij}$ where the destination decile $j$ lies below initial decile $i$. The metric scales with the square of transition distance $(i – j)^2$, ensuring severe downward descents contribute disproportionately to the total risk score.

Dynamic LAMBDA Formula for Arndt Downside Mobility

Configure a named formula SHORROCKS_ARNDT_DOWNSIDE in Excel’s Name Manager:

=LAMBDA(transition_matrix, alpha_weight,
    LET(
        n, ROWS(transition_matrix),
        row_idx, SEQUENCE(n, 1, 1, 1),
        col_idx, SEQUENCE(1, n, 1, 1),
        downside_condition, row_idx > col_idx,
        distance, IF(downside_condition, (row_idx - col_idx) ^ alpha_weight, 0),
        weighted_transitions, transition_matrix * distance,
        total_downside_score, SUM(weighted_transitions) / (n - 1),
        total_downside_score
    )
)

Stacking Multi-Period Longitudinal Results

Aggregate historical decile evaluations using VSTACK to generate clear, automated multi-period summaries:

=LET(
    cohort2005, Matrix2005[#Data],
    cohort2015, Matrix2015[#Data],
    cohort2025, Matrix2025[#Data],
    alpha, 2.0,
    m1, SHORROCKS_ARNDT_DOWNSIDE(cohort2005, alpha),
    m2, SHORROCKS_ARNDT_DOWNSIDE(cohort2015, alpha),
    m3, SHORROCKS_ARNDT_DOWNSIDE(cohort2025, alpha),
    headers, HSTACK("Decade Cohort", "Arndt Downside Index"),
    row1, HSTACK("2005-2015", m1),
    row2, HSTACK("2015-2025", m2),
    row3, HSTACK("2025-2035 (Proj)", m3),
    VSTACK(headers, row1, row2, row3)
)

Validation and Sensitivity Diagnostics

Verify that matrix rows strictly sum to 100% or 1.0 before calculation. Varying the $alpha$ exponent permits comparative sensitivity analysis between moderate downward drift and catastrophic decile collapse.