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

Quantitative wealth analysis and inequality research frequently demand the modeling of fat-tailed economic distributions where downside tail risk deviates significantly from symmetrical assumptions. By synthesizing Anthony Shorrocks’ decomposition methodology, Stephen Jenkins and Frank Cowell’s distributional sensitivity indexes, and Generalized Pareto distributions (GPD), financial analysts can construct robust asymmetric decile risk models. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, MAP, and SCAN—enable the implementation of fully recursive econometric models directly in spreadsheet workbooks without requiring VBA macros.

Theoretical Underpinnings of the Composite Model

The Jenkins-Cowell inequality index assesses distributional disparities by evaluating parametric sensitivities across segmented economic strata. When paired with a Generalized Pareto parameterization defined by threshold location parameter (mu), scale parameter (sigma), and shape parameter (xi), the cumulative distribution function handles extreme tail dynamics with high precision:

F(x) = 1 – (1 + xi * ((x – mu) / sigma))^(-1 / xi)

To measure downside vulnerability, the Shorrocks asymmetric decile framework breaks down total inequality into constituent decile cohorts, calculating downside downside risk measures relative to the cohort median. Implementing this dynamic calculation in classic Excel required multi-column lookup matrices, but modern dynamic arrays consolidate the entire calculation into a single modular formula.

Authoring the Reusable LAMBDA Function

To implement this model, define a custom modular function using Excel’s LAMBDA syntax. Open the Name Manager (press Ctrl + F3) and define a new named formula called CALC_GENPARETO_SHORROCKS:

=LAMBDA(data_array, threshold_mu, scale_sigma, shape_xi,
    LET(
        clean_data, FILTER(data_array, ISNUMBER(data_array)),
        n, ROWS(clean_data),
        sorted, SORT(clean_data),
        decile_idx, SEQUENCE(10, 1, 1, 1),
        decile_breaks, PERCENTILE.INC(sorted, decile_idx / 10),
        decile_starts, VSTACK(MIN(sorted), DROP(decile_breaks, -1)),
        decile_summary, MAKEARRAY(10, 5, LAMBDA(r, c,
            LET(
                d_low, INDEX(decile_starts, r),
                d_high, INDEX(decile_breaks, r),
                cohort, FILTER(sorted, (sorted >= d_low) * (sorted <= d_high)),
                mu_cohort, AVERAGE(cohort),
                pareto_gpd, 1 - (1 + shape_xi * ((mu_cohort - threshold_mu) / scale_sigma))^(-1 / shape_xi),
                downside_dev, SQRT(AVERAGE((cohort - mu_cohort)^2)),
                asym_weight, (r / 10)^2,
                CHOOSE(c, r, mu_cohort, pareto_gpd, downside_dev, asym_weight * downside_dev)
            )
        )),
        headers, HSTACK("Decile", "Mean", "GPD Prob", "Downside Dev", "Weighted Shorrocks Risk"),
        VSTACK(headers, decile_summary)
    )
)

Constructing the Dynamic Workbook Pipeline

Once the custom function is established in the Name Manager, deploy it in your analysis worksheet alongside dynamic inputs:

1. Populate your raw observed distribution data in column A2:A5000.
2. In cell C2, enter the threshold location parameter mu (e.g. 25000).
3. In cell C3, enter the scale parameter sigma (e.g. 12500).
4. In cell C4, enter the shape parameter xi (e.g. 0.185).
5. In cell E2, call the dynamic function:

   =CALC_GENPARETO_SHORROCKS(A2:A5000, C2, C3, C4)

The formula instantly spills an 11-row by 5-column table containing header labels and comprehensive decile metrics, recalculating instantly when input parameters or source data records change.

Synthesising Downside Tail Sensitivity

To extract the composite Jenkins-Cowell tail sensitivity score across all deciles, apply an aggregating formula directly to the spilled array:

=LET(
    spill_result, E3#,#
    weighted_risks, CHOOSECOLS(spill_result, 5),
    gpd_probs, CHOOSECOLS(spill_result, 3),
    SUMPRODUCT(weighted_risks, gpd_probs) / SUM(weighted_risks)
)

This single cell expression computes the normalised downside tail inequality index, providing financial econometricians with a transparent, audit-ready measure of downside economic exposure in Microsoft Excel.