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

Evaluating multi-generational economic vulnerability requires quantitative mobility measures sensitive to unequal transition penalties across different social strata. The Bourguignon mobility index introduces a parameterized convex transformation that penalises downward mobility severely for lower-income deciles while dampening downward movement from upper wealth tiers. By implementing dynamic LAMBDA and VSTACK algorithms in Microsoft Excel, quantitative researchers can construct real-time Bourguignon downside mobility models without external statistical packages.

Mathematical Foundations of Bourguignon Downside Mobility

The Bourguignon framework applies a convex weighting parameter $ heta > 1$ to origin decile status. For a decile transition matrix $P$ where $P_{ij}$ represents the probability of moving from decile $i$ to decile $j$:

$$B_{down} = sum_{i > j} P_{ij} cdot left(rac{i – j}{i} ight)^ heta cdot left(rac{n – i + 1}{n} ight)$$

The secondary term assigns higher social weight to lower initial deciles, ensuring downward slips from near-poverty ranks receive the highest policy urgency.

Authoring the Dynamic LAMBDA Formula

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

=LAMBDA(transition_matrix, theta_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),
        origin_weight, (n - row_seq + 1) / n,
        weighted_loss, IF(is_downside, transition_matrix * (rel_drop ^ theta_param) * origin_weight, 0),
        bourguignon_score, SUM(weighted_loss),
        bourguignon_score
    )
)

Stacking Multi-Period Longitudinal Results with VSTACK

Aggregate 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],
    theta, 2.2,
    b1, BOURGUIGNON_DOWNSIDE(cohort1990, theta),
    b2, BOURGUIGNON_DOWNSIDE(cohort2000, theta),
    b3, BOURGUIGNON_DOWNSIDE(cohort2010, theta),
    b4, BOURGUIGNON_DOWNSIDE(cohort2020, theta),
    headers, HSTACK("Observation Window", "Bourguignon Downside Vulnerability Index"),
    r1, HSTACK("1990-2000", b1),
    r2, HSTACK("2000-2010", b2),
    r3, HSTACK("2010-2020", b3),
    r4, HSTACK("2020-2030 (Est)", b4),
    VSTACK(headers, r1, r2, r3, r4)
)

Sensitivity Testing and Model Verification

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