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

Evaluating economic and income mobility requires robust mathematical indices capable of isolating downward socio-economic transitions. The Jenkins-Cowell logarithmic mobility index introduces an asymmetric weighting function that applies logarithmic scaling to downward transitions, reflecting diminishing marginal concern over extreme transitions while retaining heightened sensitivity to downward movement across intermediate deciles. By leveraging Microsoft Excel’s dynamic array functions—specifically LAMBDA, MAKEARRAY, and VSTACK—econometricians can build interactive, formula-driven mobility engines without writing VBA macros.

Mathematical Foundations of the Logarithmic Weighting Kernel

Let ( P ) represent an ( n imes n ) transition probability matrix where ( p_{ij} ) denotes the probability of moving from initial decile ( i ) to final decile ( j ). Downside mobility corresponds to transitions below the main diagonal where ( i > j ). The logarithmic weighting kernel is expressed as:

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

Here, ( eta > 0 ) is a sensitivity parameter controlling the curvature of the downward mobility penalty. When aggregated across all deciles, the normalised logarithmic index measures structural downward vulnerability across different survey periods.

Formulating the Custom LAMBDA Engine

Open the Excel Name Manager (Ctrl+F3) and create the named formula JENKINS_COWELL_LOG_INDEX:

=LAMBDA(transition_mat, beta_param,
    LET(
        n, ROWS(transition_mat),
        log_weights, MAKEARRAY(n, n, LAMBDA(r, c,
            IF(r > c,
                LN(1 + beta_param * (r - c)),
                c - r
            )
        )),
        weighted_transitions, transition_mat * log_weights,
        sum_weighted, SUM(weighted_transitions),
        max_theoretical_weight, SUM(MAKEARRAY(n, n, LAMBDA(r, c, LN(1 + beta_param * (n - 1))))),
        normalised_metric, sum_weighted / max_theoretical_weight,
        normalised_metric
    )
)

Constructing Comparative Reporting Layouts with VSTACK

To summarize and compare logarithmic mobility across multiple regional survey datasets, dynamically stack the results using VSTACK:

=LET(
    headers, HSTACK("Survey Region", "Logarithmic Beta", "Downside Risk Index", "Risk Classification"),
    region_north, HSTACK("Northern Region", 2.0, JENKINS_COWELL_LOG_INDEX(RegionNorth!$B$2:$K$11, 2.0), "Elevated Risk"),
    region_central, HSTACK("Central Region", 2.0, JENKINS_COWELL_LOG_INDEX(RegionCentral!$B$2:$K$11, 2.0), "Moderate Risk"),
    region_south, HSTACK("Southern Region", 2.0, JENKINS_COWELL_LOG_INDEX(RegionSouth!$B$2:$K$11, 2.0), "Low Risk"),
    VSTACK(headers, region_north, region_central, region_south)
)

Validation and Auditing Considerations

When implementing logarithmic transition models in empirical research:

• Row Stochasticity: Confirm that each row of the input transition matrix sums to exactly 1.000 using SUM(INDEX(transition_mat, row_idx, 0)).
• Beta Range Testing: Ensure ( eta > 0 ). Lower values (( 0.5 le eta le 1.5 )) linearise penalties, while higher values (( eta > 3.0 )) flatten penalties for distant decile movements.
• Dynamic Recalculation: Unlike VBA user-defined functions, native dynamic array formulas recalculate asynchronously within Excel’s native multi-core execution engine, ensuring smooth performance on large datasets.