How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Asymmetric Decile Downside Jenkins-Cowell Logistic 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 logistic mobility framework introduces a generalized logistic weighting kernel that applies configurable growth parameters to 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 Logistic 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 logistic weighting kernel is expressed as:

[ w_{ij} = egin{cases} rac{L}{1 + e^{-s cdot (i – j)}} & ext{if } i > j \ |i – j| & ext{if } i le j end{cases} ]

Where ( L ) represents the upper carrying capacity and ( s ) governs the steepness of the penalty curve. The normalized logistic 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_LOGISTIC_INDEX:

=LAMBDA(transition_matrix, param_L, param_s,
    LET(
        n, ROWS(transition_matrix),
        logistic_weights, MAKEARRAY(n, n, LAMBDA(r, c,
            IF(r > c,
                param_L / (1 + EXP(-param_s * (r - c))),
                c - r
            )
        )),
        weighted_transitions, transition_matrix * logistic_weights,
        sum_weighted, SUM(weighted_transitions),
        max_theoretical_logistic, SUM(MAKEARRAY(n, n, LAMBDA(r, c,
            param_L / (1 + EXP(-param_s * (n - 1)))
        ))),
        normalised_metric, sum_weighted / max_theoretical_logistic,
        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 L", "Param s", "Downside Mobility Index", "Vulnerability Level"),
    cohort_rural, HSTACK("Rural Workforce", 3.0, 1.2, JENKINS_COWELL_LOGISTIC_INDEX(RuralData!$B$2:$K$11, 3.0, 1.2), "High Vulnerability"),
    cohort_suburban, HSTACK("Suburban Workforce", 3.0, 1.2, JENKINS_COWELL_LOGISTIC_INDEX(SuburbanData!$B$2:$K$11, 3.0, 1.2), "Moderate Vulnerability"),
    cohort_urban, HSTACK("Urban Workforce", 3.0, 1.2, JENKINS_COWELL_LOGISTIC_INDEX(UrbanData!$B$2:$K$11, 3.0, 1.2), "Low Vulnerability"),
    VSTACK(table_headers, cohort_rural, cohort_suburban, cohort_urban)
)

Auditing and Practical Deployment Best Practices

When applying logistic 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 ( L > 0 ) and ( s > 0 ). Setting ( s approx 1.0 ) provides smooth, gradual penalty transitions across decile boundaries.
• 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.