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

The Generalized Log-Logistic Type XI distribution provides an exceptionally versatile parametric framework for modeling skewed and fat-tailed economic phenomena, generalizing the log-logistic distribution by introducing asymmetric compound exponent parameters that accommodate severe tail disparities. By synthesizing the Generalized Log-Logistic Type XI distribution with Anthony Shorrocks’ inequality decomposition methodology and Stephen Jenkins and Frank Cowell’s asymmetric sensitivity indexes, quantitative analysts can model downside economic risk with extraordinary precision. Microsoft Excel’s dynamic array functions—specifically VSTACK, LAMBDA, LET, and MAKEARRAY—enable the implementation of these complex econometric models natively in spreadsheets without VBA macros.

Theoretical Framework of the Composite Model

The Generalized Log-Logistic Type XI distribution is parameterized by scale (alpha) and shape parameters (beta, gamma, lambda, zeta). For x > 0, its cumulative distribution function is expressed as:

F(x) = (1 – (1 + zeta * (x / alpha)^beta)^(-gamma / zeta))^(lambda / zeta)

When zeta = 1, gamma = 1, and lambda = 1, the model reduces to the standard two-parameter log-logistic distribution. By segmenting data into ten decile cohorts, the Shorrocks downside method computes dispersion and downside risk metrics weighted by lower-tail sensitivity. Modern dynamic arrays consolidate this recursive calculation into a single modular formula.

Authoring the Reusable LAMBDA Function

Define a custom modular function in Microsoft Excel. Open the Name Manager (press Ctrl + F3) and create a new name called CALC_GLL11_SHORROCKS:

=LAMBDA(dataset, param_alpha, param_beta, param_gamma, param_lambda, param_zeta,
    LET(
        clean_data, FILTER(dataset, ISNUMBER(dataset) * (dataset > 0)),
        n, ROWS(clean_data),
        sorted, SORT(clean_data),
        decile_ranks, SEQUENCE(10, 1, 1, 1),
        quantiles, PERCENTILE.INC(sorted, decile_ranks / 10),
        starts, VSTACK(MIN(sorted), 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, (sorted >= d_low) * (sorted <= d_high)),
                cohort_mean, AVERAGE(subset),
                z_ratio, (cohort_mean / param_alpha)^param_beta,
                base_prob, 1 - (1 + param_zeta * z_ratio)^(-param_gamma / param_zeta),
                gll11_kernel, base_prob^(param_lambda / param_zeta),
                downside_dev, SQRT(AVERAGE((subset - cohort_mean)^2)),
                decile_weight, (11 - r) / 10,
                CHOOSE(c, r, cohort_mean, gll11_kernel, downside_dev, decile_weight * cohort_downside)
            )
        )),
        header_row, HSTACK("Decile", "Mean", "GLL11 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 scale parameter alpha in cell C2 (e.g. 58000).
3. Enter the shape parameter beta in cell C3 (e.g. 3.15).
4. Enter the shape parameter gamma in cell C4 (e.g. 1.95).
5. Enter the exponent parameter lambda in cell C5 (e.g. 1.45).
6. Enter the parameter zeta in cell C6 (e.g. 1.05).
7. In cell E2, execute the formula:

   =CALC_GLL11_SHORROCKS(A2:A5000, C2, C3, C4, C5, C6)

The custom formula immediately spills a comprehensive 11-row by 5-column analysis table detailing decile means, GLL11 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 provides quantitative analysts with an automated, transparent metric of downside tail risk across complex economic distributions in Microsoft Excel.