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

In quantitative economics and policy analysis, distinguishing between upward mobility and downward socio-economic vulnerability is critical. Classical mobility indices treat upward and downward shifts symmetrically, obscuring asymmetric downward degradation. The Jenkins-Cowell Champernowne mobility framework introduces a classical three-parameter Champernowne cumulative weighting kernel that models heavy-tailed penalty escalation for downward decile transitions across diverse socio-economic classes. Using Microsoft Excel’s dynamic array functions—specifically LAMBDA, MAKEARRAY, and VSTACK—analysts can implement real-time, scalable mobility models without writing VBA macros.

Theoretical Framework of the Champernowne Weighting Kernel

Given an n by n transition probability matrix P, downside mobility occurs when initial decile i exceeds destination decile j (i > j). The Jenkins-Cowell Champernowne weighting kernel scales downward step distances through parameters alpha, x_0, and c:

w_ij = (1 / 3.14159) * ATAN(((i – j) / x_0) ^ alpha + c) for i > j, and |i – j| for i <= j.

Where alpha > 0, x_0 > 0, and c represent the shape, scale, and shift parameters of the downward penalty distribution. The normalized Champernowne index measures societal vulnerability to downward socio-economic mobility across longitudinal cohorts.

Formulating the Custom LAMBDA Formula in Excel

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

=LAMBDA(transition_matrix, param_alpha, param_x0, param_c,
    LET(
        n, ROWS(transition_matrix),
        champ_weights, MAKEARRAY(n, n, LAMBDA(r, c_idx,
            IF(r > c_idx,
                (1 / PI()) * ATAN((((r - c_idx) / param_x0) ^ param_alpha) + param_c) + 0.5,
                c_idx - r
            )
        )),
        weighted_transitions, transition_matrix * champ_weights,
        sum_weighted, SUM(weighted_transitions),
        max_theoretical_champ, SUM(MAKEARRAY(n, n, LAMBDA(r, c_idx,
            (1 / PI()) * ATAN((((n - 1) / param_x0) ^ param_alpha) + param_c) + 0.5
        ))),
        normalised_metric, sum_weighted / max_theoretical_champ,
        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 Alpha", "Param X0", "Param C", "Downside Mobility Index", "Vulnerability Level"),
    cohort_rural, HSTACK("Rural Workforce", 1.8, 2.0, 0.0, JENKINS_COWELL_CHAMP_INDEX(RuralData!$B$2:$K$11, 1.8, 2.0, 0.0), "High Vulnerability"),
    cohort_suburban, HSTACK("Suburban Workforce", 1.8, 2.0, 0.0, JENKINS_COWELL_CHAMP_INDEX(SuburbanData!$B$2:$K$11, 1.8, 2.0, 0.0), "Moderate Vulnerability"),
    cohort_urban, HSTACK("Urban Workforce", 1.8, 2.0, 0.0, JENKINS_COWELL_CHAMP_INDEX(UrbanData!$B$2:$K$11, 1.8, 2.0, 0.0), "Low Vulnerability"),
    VSTACK(table_headers, cohort_rural, cohort_suburban, cohort_urban)
)

Auditing and Practical Deployment Best Practices

When applying Champernowne 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 param_alpha > 0 and param_x0 > 0 to preserve mathematical validity across all evaluated decile steps.
• 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.