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

Evaluating economic inequality, income distribution dynamics, and actuarial downside risks demands robust parametric modeling techniques. The Jenkins-Cowell Generalized Log-Logistic Type XCI (Type 91) distribution offers an asymmetric three-parameter framework tailored for analyzing tail risks and downside income mobility under the Shorrocks inequality decomposition. With modern Excel dynamic array formulas—specifically LAMBDA, VSTACK, MAP, and LET—analysts can formulate fully vectorised quantile tables and Shorrocks downside indices without requiring external Python scripts or legacy VBA macros. This tutorial demonstrates how to construct, compute, and present a dynamic Jenkins-Cowell GLL Type XCI model in Microsoft Excel.

Theoretical Model: Jenkins-Cowell GLL Type XCI

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

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

where `alpha > 0` represents the scale (median benchmark) parameter, `gamma > 0` controls the tail thickness, and `beta > 0` governs downside tail asymmetry. Inverting the CDF yields the quantile function `Q(p)` for decile probability `p`:

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

Under the Shorrocks downward-mobility inequality framework, analysts evaluate lower-decile vulnerability by comparing quantile income thresholds against the median (`p = 0.50`) and measuring the relative decile downside shortfall ratio across deciles 1 through 5.

Constructing the Reusable LAMBDA in Excel

Open the Name Manager in Excel (Ctrl + F3) and define a custom function named GLL91_QUANTILE:

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

This dynamic LAMBDA function accepts single probability values or spilled sequence vectors and computes the corresponding inverse quantile values instantaneously.

Spilling Decile Downside Tables with VSTACK and LET

In an empty worksheet cell, enter the following vectorised formula to generate a complete 10-decile distribution summary table with dynamic headers and summary metrics:

=LET(
    alpha, B2,
    beta, B3,
    gamma, B4,
    p_seq, SEQUENCE(10, 1, 0.1, 0.1),
    quantiles, MAP(p_seq, LAMBDA(p, GLL91_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)
)

Where cell B2 contains `alpha` (e.g., 54000), B3 contains `beta` (e.g., 0.90), and B4 contains `gamma` (e.g., 1.50). The formula immediately spills 11 rows and 3 columns displaying exact decile cut-offs and downside mobility metrics.

Visualising Shorrocks Downside Curves in Excel

To visualize the asymmetric decile curve:

1. Select the dynamic spilled range using the pound operator (e.g., =D2#).
2. Navigate to the Insert ribbon tab and choose Scatter with Smooth Lines.
3. Set the horizontal axis to decile values (0.1 through 1.0) and the vertical axis to income values.
4. Insert a median benchmark reference line to visualize the steep downward curvature characteristic of the Type XCI distribution.

Parameter Sensitivity Reference

Parameter	Economic Role	Typical Range	Effect on Shorrocks Downside Ratio
alpha	Scale / median reference	10,000 to 120,000	Shifts the absolute income curve proportionally
beta	Downside asymmetry parameter	0.20 to 2.00	Higher values accentuate lower-decile shortfall gaps
gamma	Tail shape and curvature	1.00 to 4.00	Controls the rate of asymptotic decay in the upper tails

By combining VSTACK, MAP, and LAMBDA, quantitative analysts can deploy interactive, macro-free statistical distribution models in Microsoft Excel.