How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Asymmetric Decile Downside Jenkins-Cowell Rational 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 rational mobility framework introduces a rational weighting kernel that applies asymptotic penalty bounds to extreme downward decile transitions while preserving sensitivity to intermediate drops. 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 Rational 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 rational weighting kernel is expressed as:

[ w_{ij} = egin{cases} rac{ heta cdot (i – j)}{1 + lambda cdot (i – j)} & ext{if } i > j \ |i – j| & ext{if } i le j end{cases} ]

Where ( heta > 0 ) and ( lambda > 0 ) are calibration parameters governing the scale and asymptotic plateau of the downward penalty. The normalized rational index measures societal vulnerability 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_RATIONAL_INDEX:

=LAMBDA(transition_matrix, theta_param, lambda_param,
    LET(
        n, ROWS(transition_matrix),
        rational_weights, MAKEARRAY(n, n, LAMBDA(r, c,
            IF(r > c,
                (theta_param * (r - c)) / (1 + lambda_param * (r - c)),
                c - r
            )
        )),
        weighted_transitions, transition_matrix * rational_weights,
        sum_weighted, SUM(weighted_transitions),
        max_theoretical_rational, SUM(MAKEARRAY(n, n, LAMBDA(r, c,
            (theta_param * (n - 1)) / (1 + lambda_param * (n - 1))
        ))),
        normalised_metric, sum_weighted / max_theoretical_rational,
        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 Theta", "Param Lambda", "Downside Mobility Index", "Vulnerability Level"),
    cohort_rural, HSTACK("Rural Workforce", 2.0, 0.5, JENKINS_COWELL_RATIONAL_INDEX(RuralData!$B$2:$K$11, 2.0, 0.5), "High Vulnerability"),
    cohort_suburban, HSTACK("Suburban Workforce", 2.0, 0.5, JENKINS_COWELL_RATIONAL_INDEX(SuburbanData!$B$2:$K$11, 2.0, 0.5), "Moderate Vulnerability"),
    cohort_urban, HSTACK("Urban Workforce", 2.0, 0.5, JENKINS_COWELL_RATIONAL_INDEX(UrbanData!$B$2:$K$11, 2.0, 0.5), "Low Vulnerability"),
    VSTACK(table_headers, cohort_rural, cohort_suburban, cohort_urban)
)

Auditing and Practical Deployment Best Practices

When applying rational 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 ( heta > 0 ) and ( lambda > 0 ). Setting ( lambda o 0 ) reduces the equation to a linear asymmetry model, while larger ( lambda ) values constrain extreme penalties asymptotically.
• 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.