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

Measuring downward economic transitions across population deciles requires quantitative frameworks that balance distance-based turnover with distributional inequality weighting. The Cowell mobility framework incorporates an inequality aversion parameter $alpha$ that penalises transitions based on destination decile poverty depth. By building modern dynamic array formulas with VSTACK and LAMBDA in Microsoft Excel, researchers can author responsive Cowell downside mobility models without legacy VBA macros.

Mathematical Concept of Cowell Downside Mobility

The Cowell downside mobility index applies a parametric weighting function to downward transitions $i > j$. For an $n imes n$ decile transition matrix $P$:

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

When the destination decile $j$ represents a severe plunge relative to origin $i$, the ratio $j/i$ approaches zero, yielding maximal penalty weighting.

Constructing the Dynamic LAMBDA Formula

Define the named formula COWELL_DOWNSIDE inside Excel’s Name Manager:

=LAMBDA(transition_matrix, alpha_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,
        dest_ratio, IF(is_downside, col_idx / row_idx, 1),
        penalty, IF(is_downside, 1 - (dest_ratio ^ alpha_param), 0),
        weighted_loss, IF(is_downside, transition_matrix * penalty, 0),
        cowell_score, SUM(weighted_loss),
        cowell_score
    )
)

Stacking Multi-Period Longitudinal Results with VSTACK

Consolidate results across multiple census intervals dynamically using VSTACK:

=LET(
    cohortA, PanelA[#Data],
    cohortB, PanelB[#Data],
    cohortC, PanelC[#Data],
    alpha, 1.8,
    w1, COWELL_DOWNSIDE(cohortA, alpha),
    w2, COWELL_DOWNSIDE(cohortB, alpha),
    w3, COWELL_DOWNSIDE(cohortC, alpha),
    headers, HSTACK("Survey Decade", "Cowell Downside Vulnerability Index"),
    r1, HSTACK("1995-2005", w1),
    r2, HSTACK("2005-2015", w2),
    r3, HSTACK("2015-2025", w3),
    VSTACK(headers, r1, r2, r3)
)

Model Diagnostics and Parameter Tuning

Ensure that all input matrix rows sum to unity. Varying $alpha$ within linked cells allows economists to simulate varying degrees of societal risk aversion instantly across all stacked output tables.