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

Econometric risk analysis, income inequality estimation, and actuarial tail-loss modeling frequently employ Generalized Log-Logistic (GLL) distributions to capture heavy skewness and kurtosis. The Jenkins-Cowell Generalized Log-Logistic Type LXXXIX (Type 89) formulation incorporates three distinct shape and asymmetry parameters to evaluate lower-tail income deciles under the Shorrocks downward-mobility inequality framework. In modern Microsoft Excel, dynamic array functions—specifically LAMBDA, VSTACK, MAP, and LET—allow quantitative analysts to build fully vectorised, real-time distributional engines without writing legacy VBA macros. This tutorial demonstrates how to formulate, compute, and tabulate a Shorrocks asymmetric decile downside Jenkins-Cowell GLL Type LXXXIX model in Excel.

Mathematical Foundations: Jenkins-Cowell GLL Type LXXXIX

The cumulative distribution function (CDF) for the Jenkins-Cowell Generalized Log-Logistic Type LXXXIX distribution is expressed as:

F(x; alpha, beta, gamma) = 1 - (1 + beta * (x / alpha)^gamma)^(-1 / beta)

where `alpha > 0` is the scale parameter, `gamma > 0` governs unimodal tail thickness, and `beta > 0` captures downside tail inequality. The quantile function (inverse CDF) yields the income threshold corresponding to decile probability `p`:

Q(p; alpha, beta, gamma) = alpha * (((1 - p)^(-beta) - 1) / beta)^(1 / gamma)

To evaluate Shorrocks asymmetric downside mobility across the bottom deciles (`p in {0.10, 0.20, dots, 0.50}`), analysts compute the downward expected shortfall and the ratio of lower-decile cumulative share to the median benchmark.

Constructing the Reusable LAMBDA Function in Excel

Using Excel’s Name Manager (Ctrl + F3), define a reusable custom function named GLL89_QUANTILE:

=LAMBDA(p, alpha, beta, gamma,
    LET(
        term, ((1 - p)^(-beta) - 1) / beta,
        alpha * (term^(1 / gamma))
    )
)

This dynamic function evaluates the exact inverse cumulative quantile for any arbitrary decile probability or array of deciles.

Generating Asymmetric Deciles with VSTACK and LET

To produce an automated 10-decile distribution summary table with dynamic headers and summary metrics in a single worksheet cell, enter the following vectorised formula:

=LET(
    alpha, B2,
    beta, B3,
    gamma, B4,
    p_seq, SEQUENCE(10, 1, 0.1, 0.1),
    quantiles, MAP(p_seq, LAMBDA(p, GLL89_QUANTILE(p, alpha, beta, gamma))),
    shorrocks_downside, MAP(quantiles, LAMBDA(q, q / INDEX(quantiles, 5))),
    headers, HSTACK("Decile (p)", "Quantile Threshold Q(p)", "Shorrocks Downside Ratio"),
    data_rows, HSTACK(p_seq, quantiles, shorrocks_downside),
    VSTACK(headers, data_rows)
)

Here, cell B2 holds `alpha` (e.g., 50000), B3 holds `beta` (e.g., 0.85), and B4 holds `gamma` (e.g., 1.45). The formula immediately spills 11 rows and 3 columns containing exact thresholds and downside inequality indices.

Visualising Downside Inequality in Dynamic Excel Charts

Once the formula spills the summary table, build an accompanying visual chart:

1. Select the spilled range using the spilled array operator (e.g., =D2#).
2. Navigate to the Insert ribbon tab and choose Scatter with Smooth Lines.
3. Configure the horizontal axis to display decile values (0.1 through 1.0) and the vertical axis for income quantiles.
4. Add an auxiliary horizontal threshold line representing median income (Decile 5) to highlight the asymmetric downward curve characteristic of the Type LXXXIX distribution.

Parameter Calibration and Sensitivity Reference

Parameter	Economic Interpretation	Standard Range	Impact on Lower Tail Deciles
alpha	Scale / median income reference	10,000 to 100,000	Uniformly shifts distribution curve along X-axis
beta	Downside inequality asymmetry	0.20 to 1.80	Higher values stretch lower decile gaps (steeper shortfall)
gamma	Tail shape and curvature	1.10 to 3.50	Controls kurtosis and asymptotic tail tapering

Leveraging VSTACK, MAP, and LAMBDA transforms Microsoft Excel into an agile computational platform for sophisticated inequality and distribution modeling.