How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Asymmetric Decile Downside Jenkins-Cowell Gompertz 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 Gompertz mobility framework introduces an asymmetric Gompertz weighting kernel that models double-exponential penalty acceleration for early downward transitions before smoothly saturating across extreme decile declines. 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 Gompertz 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 Gompertz weighting kernel is expressed as:

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

Where ( a ) represents upper asymptotic saturation, ( b ) determines displacement along the decile displacement axis, and ( c ) dictates exponential growth rate. The normalized Gompertz 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_GOMPERTZ_INDEX:

=LAMBDA(transition_matrix, param_a, param_b, param_c,
    LET(
        n, ROWS(transition_matrix),
        gompertz_weights, MAKEARRAY(n, n, LAMBDA(r, c,
            IF(r > c,
                param_a * EXP(-param_b * EXP(-param_c * (r - c))),
                c - r
            )
        )),
        weighted_transitions, transition_matrix * gompertz_weights,
        sum_weighted, SUM(weighted_transitions),
        max_theoretical_gompertz, SUM(MAKEARRAY(n, n, LAMBDA(r, c,
            param_a * EXP(-param_b * EXP(-param_c * (n - 1)))
        ))),
        normalised_metric, sum_weighted / max_theoretical_gompertz,
        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", "Param c", "Downside Mobility Index", "Vulnerability Level"),
    cohort_rural, HSTACK("Rural Workforce", 4.0, 2.0, 0.8, JENKINS_COWELL_GOMPERTZ_INDEX(RuralData!$B$2:$K$11, 4.0, 2.0, 0.8), "High Vulnerability"),
    cohort_suburban, HSTACK("Suburban Workforce", 4.0, 2.0, 0.8, JENKINS_COWELL_GOMPERTZ_INDEX(SuburbanData!$B$2:$K$11, 4.0, 2.0, 0.8), "Moderate Vulnerability"),
    cohort_urban, HSTACK("Urban Workforce", 4.0, 2.0, 0.8, JENKINS_COWELL_GOMPERTZ_INDEX(UrbanData!$B$2:$K$11, 4.0, 2.0, 0.8), "Low Vulnerability"),
    VSTACK(table_headers, cohort_rural, cohort_suburban, cohort_urban)
)

Auditing and Practical Deployment Best Practices

When applying Gompertz 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 > 0 ), ( b > 0 ), and ( c > 0 ). Setting ( c approx 0.5 ext{ to } 1.0 ) provides realistic asymmetry without premature saturation.
• 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.