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

Quantitative wealth and risk modeling often encounters empirical distributions characterized by kurtosis profiles that violate Gaussian normality assumptions. To address both fat-tailed and thin-tailed deviations, econometricians synthesize the Generalized Error Distribution (GED, also known as the Subbotin distribution) with Anthony Shorrocks’ inequality decomposition framework and Stephen Jenkins and Frank Cowell’s asymmetric sensitivity indexes. Microsoft Excel’s dynamic array engine—specifically VSTACK, LAMBDA, LET, and MAKEARRAY—allows analysts to construct these complex econometric models dynamically without resorting to external programming languages.

Econometric Mechanics of the Composite Model

The Generalized Error Distribution is parameterized by location parameter (mu), scale parameter (sigma), and shape parameter (beta or p). When p = 2, the GED simplifies to a standard normal distribution; when p < 2, it models heavy-tailed distributions with elevated peak kurtosis. Its probability density function provides smooth, flexible tail fitting across economic microdata:

f(x) = [p / (2 * sigma * Gamma(1 / p))] * exp(-(|x – mu| / sigma)^p)

By decomposing the distribution into ten asymmetric decile cohorts, the Shorrocks downside method computes dispersion and inequality penalties weighted according to lower-tail vulnerability. Dynamic array functions enable the generation of dynamic cohort statistics and recursive risk evaluations in a single spreadsheet formula.

Defining the Dynamic LAMBDA Model

To implement this model, author a custom named formula in Microsoft Excel. Open the Name Manager (press Ctrl + F3) and define CALC_GED_SHORROCKS:

=LAMBDA(dataset, param_mu, param_sigma, shape_p,
    LET(
        clean_vals, FILTER(dataset, ISNUMBER(dataset)),
        n, ROWS(clean_vals),
        sorted_vals, SORT(clean_vals),
        decile_ranks, SEQUENCE(10, 1, 1, 1),
        quantiles, PERCENTILE.INC(sorted_vals, decile_ranks / 10),
        starts, VSTACK(MIN(sorted_vals), DROP(quantiles, -1)),
        cohort_matrix, MAKEARRAY(10, 5, LAMBDA(r, c,
            LET(
                d_low, INDEX(starts, r),
                d_high, INDEX(quantiles, r),
                subset, FILTER(sorted_vals, (sorted_vals >= d_low) * (sorted_vals <= d_high)),
                cohort_mean, AVERAGE(subset),
                z_score, ABS(cohort_mean - param_mu) / param_sigma,
                ged_kernel, EXP(-(z_score^shape_p)),
                cohort_downside, SQRT(AVERAGE((subset - cohort_mean)^2)),
                decile_weight, (11 - r) / 10,
                CHOOSE(c, r, cohort_mean, ged_kernel, cohort_downside, decile_weight * cohort_downside)
            )
        )),
        header_row, HSTACK("Decile", "Mean", "GED Kernel", "Downside Dev", "Weighted Shorrocks Risk"),
        VSTACK(header_row, cohort_matrix)
    )
)

Constructing the Dynamic Spreadsheet Interface

Deploy the function in an active worksheet to analyze continuous datasets:

1. Input your raw wealth or income observations in column A2:A5000.
2. Enter the distribution location parameter mu in cell C2 (e.g. 42000).
3. Enter the scale parameter sigma in cell C3 (e.g. 16500).
4. Enter the GED shape parameter p in cell C4 (e.g. 1.45 for heavy tails).
5. In cell E2, execute the formula:

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

The custom formula immediately spills a comprehensive 11-row by 5-column analysis table detailing decile means, GED kernel weights, downside deviations, and weighted Shorrocks risk indices.

Computing the Aggregate Jenkins-Cowell Tail Score

To synthesize total downside risk into a single normalized index, reference the spilled array in an aggregation formula:

=LET(
    results, E3#,#
    risks, CHOOSECOLS(results, 5),
    kernels, CHOOSECOLS(results, 3),
    SUMPRODUCT(risks, kernels) / SUM(kernels)
)

This dynamic formulation gives financial analysts an automated, transparent metric of downside tail risk across complex economic distributions in Microsoft Excel.