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

Econometric assessment of social and income mobility necessitates quantitative models that penalise downward decile transitions more severely than upward progress. The Jenkins-Cowell power mobility framework introduces an asymmetric penalty parameter into the classical Shorrocks mobility matrix index, capturing downside risk across distribution quintiles and deciles. By utilising Microsoft Excel’s dynamic array functions—notably LAMBDA, MAP, REDUCE, and VSTACK—analysts can construct scalable, real-time recalculating mobility engines without relying on legacy VBA macros.

Theoretical Foundation of the Asymmetric Jenkins-Cowell Power Index

A standard Shorrocks mobility index derived from an n x n transition matrix ( P ) evaluates trace properties: ( M(P) = rac{n – ext{Tr}(P)}{n – 1} ). Under the Jenkins-Cowell power framework, transitions below the diagonal (indicating downward mobility from decile ( i ) to decile ( j ) where ( i > j )) are weighted by an exponential distance parameter ( alpha ):

[ w_{ij} = egin{cases} |i – j|^alpha & ext{if } i > j \ |i – j| & ext{if } i le j end{cases} ]

The asymmetric downside index evaluates total weighted mobility normalised across total initial distribution mass.

Authoring the Dynamic LAMBDA Evaluation Formula

Enter the Excel Name Manager (Ctrl+F3) and create a reusable custom LAMBDA function titled JENKINS_COWELL_POWER_INDEX:

=LAMBDA(transition_matrix, alpha_param,
    LET(
        n, ROWS(transition_matrix),
        row_idx, SEQUENCE(n, 1, 1, 1),
        col_idx, SEQUENCE(1, n, 1, 1),
        weight_matrix, MAKEARRAY(n, n, LAMBDA(r, c,
            IF(r > c,
                (r - c) ^ alpha_param,
                (c - r)
            )
        )),
        weighted_transitions, transition_matrix * weight_matrix,
        total_weighted_sum, SUM(weighted_transitions),
        max_possible_sum, SUM(MAKEARRAY(n, n, LAMBDA(r, c, (n - 1) ^ alpha_param))),
        normalized_index, total_weighted_sum / max_possible_sum,
        normalized_index
    )
)

Assembling Multi-Cohort Decile Reports with VSTACK

When tracking multiple longitudinal cohorts across varying demographic segments, VSTACK allows instantaneous tabular consolidation of computed power metrics:

=LET(
    cohort_headers, HSTACK("Cohort Name", "Asymmetry Alpha", "Downside Power Metric", "Status"),
    cohort_A, HSTACK("2010-2015", 1.75, JENKINS_COWELL_POWER_INDEX(MatrixA!$B$2:$K$11, 1.75), "Severe Downward Exposure"),
    cohort_B, HSTACK("2015-2020", 1.75, JENKINS_COWELL_POWER_INDEX(MatrixB!$B$2:$K$11, 1.75), "Moderate Downward Exposure"),
    cohort_C, HSTACK("2020-2025", 1.75, JENKINS_COWELL_POWER_INDEX(MatrixC!$B$2:$K$11, 1.75), "Stabilised Mobility"),
    VSTACK(cohort_headers, cohort_A, cohort_B, cohort_C)
)

Verification and Error-Handling Strategies

To ensure mathematical accuracy and numerical stability across large empirical transition datasets:

• Row-Stochastic Validation: Validate that each row of the input decile matrix sums precisely to 1.00 using SUM(INDEX(transition_matrix, r, 0)).
• Alpha Sensitivity Auditing: Ensure the asymmetry exponent ( alpha ge 1.0 ). When ( alpha = 1.0 ), the formula gracefully converges to symmetric Prais mobility.
• Array Dimension Boundaries: Employ IFERROR() wrapping around matrix cross-products to prevent calculation breaks when processing non-square decile input slices.