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

In quantitative economics and policy analysis, assessing upward versus downward mobility is essential for understanding socio-economic equity. Classical mobility indices treat upward and downward shifts symmetrically, obscuring asymmetric downward degradation. The Jenkins-Cowell polynomial mobility framework introduces a polynomial weighting function that escalates the penalty for multi-step downward transitions while maintaining linear sensitivity across single-step changes. Using Microsoft Excel’s dynamic array functions—notably LAMBDA, MAKEARRAY, and VSTACK—analysts can implement real-time, scalable mobility models without writing legacy VBA macros.

Theoretical Framework of the Polynomial Weighting Kernel

Given an ( n imes n ) transition probability matrix ( P ), downside mobility occurs when initial decile ( i ) exceeds destination decile ( j ) (( i > j )). The Jenkins-Cowell polynomial weighting kernel is expressed as:

[ w_{ij} = egin{cases} a cdot (i – j) + b cdot (i – j)^2 & ext{if } i > j \ |i – j| & ext{if } i le j end{cases} ]

Where ( a ) and ( b ) are calibration parameters governing linear and quadratic sensitivities to downward transitions. The normalized polynomial index captures societal vulnerability by aggregating these weighted transition probabilities across all deciles.

Formulating the Custom LAMBDA Formula in Excel

Open the Name Manager by pressing Ctrl+F3 and create the custom formula JENKINS_COWELL_POLY_INDEX:

=LAMBDA(transition_matrix, param_a, param_b,
    LET(
        n, ROWS(transition_matrix),
        poly_weights, MAKEARRAY(n, n, LAMBDA(r, c,
            IF(r > c,
                param_a * (r - c) + param_b * ((r - c) ^ 2),
                c - r
            )
        )),
        weighted_transitions, transition_matrix * poly_weights,
        sum_weighted, SUM(weighted_transitions),
        max_theoretical_poly, SUM(MAKEARRAY(n, n, LAMBDA(r, c,
            param_a * (n - 1) + param_b * ((n - 1) ^ 2)
        ))),
        normalised_metric, sum_weighted / max_theoretical_poly,
        normalised_metric
    )
)

Consolidating Multi-Period Demographic Audits with VSTACK

To compile multiple longitudinal demographic cohorts into a single dynamic summary table, use VSTACK:

=LET(
    table_headers, HSTACK("Demographic Cohort", "Param a", "Param b", "Downside Mobility Index", "Vulnerability Level"),
    cohort_rural, HSTACK("Rural Workforce", 1.2, 0.4, JENKINS_COWELL_POLY_INDEX(RuralData!$B$2:$K$11, 1.2, 0.4), "High Vulnerability"),
    cohort_suburban, HSTACK("Suburban Workforce", 1.2, 0.4, JENKINS_COWELL_POLY_INDEX(SuburbanData!$B$2:$K$11, 1.2, 0.4), "Moderate Vulnerability"),
    cohort_urban, HSTACK("Urban Workforce", 1.2, 0.4, JENKINS_COWELL_POLY_INDEX(UrbanData!$B$2:$K$11, 1.2, 0.4), "Low Vulnerability"),
    VSTACK(table_headers, cohort_rural, cohort_suburban, cohort_urban)
)

Auditing and Practical Deployment Best Practices

When applying polynomial mobility models in practical econometric reporting:

• Row Stochastic Integrity: Verify that each row of the input decile matrix sums to exactly 1.000 using SUM(INDEX(transition_matrix, r, 0)).
• Parameter Bounds: Ensure ( a ge 0 ) and ( b ge 0 ). Setting ( b = 0 ) reduces the equation to a standard linear asymmetry model, while ( b > 0 ) introduces convex sensitivity to extreme drops.
• Formula Performance: Because native dynamic array functions execute in Excel’s multi-threaded calculation engine, large multi-decade datasets update instantaneously upon survey weight adjustments.