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

Quantifying socio-economic mobility requires econometric instruments that distinguish between upward socio-economic progress and downward degradation. While classical transition matrices treat upward and downward shifts symmetrically, the Jenkins-Cowell harmonic mobility model introduces an asymmetric harmonic weighting parameter. This model heavily penalises multi-step downward decile descents while dampening marginal step differences at distant decile tails. Using modern Microsoft Excel dynamic arrays—specifically LAMBDA, MAKEARRAY, and VSTACK—analysts can formulate reusable harmonic evaluation engines directly in native spreadsheets.

Mathematical Structure of the Jenkins-Cowell Harmonic Weighting

Given an ( n imes n ) transition probability matrix ( P ), where element ( p_{ij} ) denotes the probability of migrating from initial decile ( i ) to final decile ( j ), downside mobility occurs when ( i > j ). The harmonic weighting kernel assigns weights based on reciprocal step distances:

[ w_{ij} = egin{cases} sum_{k=1}^{i-j} rac{1}{k^gamma} & ext{if } i > j \ |i – j| & ext{if } i le j end{cases} ]

Here, ( gamma ) is a positive tuning exponent representing societal risk aversion to downward mobility. When aggregated over all deciles, the normalised harmonic index quantifies structural vulnerability across multi-period cohorts.

Authoring the Dynamic LAMBDA Formula

Press Ctrl+F3 to launch the Excel Name Manager and define the formula JENKINS_COWELL_HARMONIC_INDEX:

=LAMBDA(trans_matrix, gamma_val,
    LET(
        n, ROWS(trans_matrix),
        harmonic_weight, MAKEARRAY(n, n, LAMBDA(r, c,
            IF(r > c,
                LET(
                    steps, r - c,
                    seq, SEQUENCE(steps, 1, 1, 1),
                    SUM(1 / (seq ^ gamma_val))
                ),
                c - r
            )
        )),
        weighted_matrix, trans_matrix * harmonic_weight,
        aggregate_mobility, SUM(weighted_matrix),
        max_harmonic_weight, SUM(MAKEARRAY(n, n, LAMBDA(r, c,
            LET(
                max_steps, n - 1,
                max_seq, SEQUENCE(max_steps, 1, 1, 1),
                SUM(1 / (max_seq ^ gamma_val))
            )
        ))),
        normalised_metric, aggregate_mobility / max_harmonic_weight,
        normalised_metric
    )
)

Constructing Comparative Demographic Reports with VSTACK

To consolidate multiple empirical decile cohorts into a unified analytical summary table, use VSTACK to stack rows dynamically:

=LET(
    headers, HSTACK("Demographic Segment", "Harmonic Gamma", "Downside Risk Index", "Mobility Tier"),
    row_youth, HSTACK("Youth Cohort (18-29)", 1.5, JENKINS_COWELL_HARMONIC_INDEX(Data!$B$2:$K$11, 1.5), "High Volatility"),
    row_mid, HSTACK("Mid-Career (30-49)", 1.5, JENKINS_COWELL_HARMONIC_INDEX(Data!$M$2:$V$11, 1.5), "Moderate Stability"),
    row_senior, HSTACK("Senior Cohort (50-64)", 1.5, JENKINS_COWELL_HARMONIC_INDEX(Data!$X$2:$AG$11, 1.5), "Strong Capital Retention"),
    VSTACK(headers, row_youth, row_mid, row_senior)
)

Verification and Practical Deployment Guidelines

When applying harmonic decile mobility models in production econometric auditing:

• Gamma Parameter Range: Validate that ( gamma ge 0.5 ). Values near ( 1.0 ) produce classical harmonic dampening, while values above ( 2.0 ) restrict significant penalties to adjacent decile drops.
• Stochastic Matrix Integrity: Confirm that each row of the input decile matrix sums to exactly 1.000 to prevent artificial inflation of the aggregate index.
• Real-Time Recalculation: Because native dynamic array functions run directly in the multithreaded calculation engine, updating source survey weights updates all downstream VSTACK tables instantly without VBA performance bottlenecks.