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

Quantitative wealth inequality research and actuarial risk modelling frequently demand flexible distributional curves capable of accommodating fat-tailed income data and severe downside asymmetry. The Generalized Log-Logistic Type XVIII (GLL18) family, combined with Shorrocks-style downside decile decomposition and Jenkins-Cowell inequality weights, delivers robust analytical clarity across heavily skewed distributions. Implementing these multi-parameter mathematical models in Microsoft Excel has traditionally required complex VBA macros.

With modern dynamic array functions—specifically LAMBDA, VSTACK, LET, and BYROW—financial analysts can build fully responsive, formula-driven econometric models directly on the grid. This tutorial demonstrates how to construct, evaluate, and benchmark a dynamic Jenkins-Cowell GLL18 decile breakdown model in Microsoft Excel.

The Jenkins-Cowell GLL18 Distribution Framework

The Generalized Log-Logistic Type XVIII cumulative distribution function introduces compound shape exponents to capture extreme tail volatility and asymmetric downside vulnerability across lower decile brackets. For an income or portfolio loss variable x with scale parameter alpha, location parameter mu, and shape parameters gamma and delta, the cumulative density function is expressed as:

F(x; alpha, mu, gamma, delta) = 1 - [ 1 + ((x - mu) / alpha)^gamma ]^(-delta * (1 + (x / alpha)^1.15))

To evaluate Shorrocks asymmetric decile downside weights, the distribution is discretised into ten quantile brackets (q_1 through q_10). Each decile’s downside risk contribution is scaled by the Jenkins-Cowell inequality weighting index, emphasising structural poverty traps and downside financial vulnerability.

Constructing the Dynamic LAMBDA Formula

Using Excel’s Name Manager or the Advanced Formula Environment, define a custom calculation function called CALC.GLL18.DECILES that dynamically projects downside inequality metrics:

=LAMBDA(data_range, alpha, gamma, delta,
    LET(
        clean_data, FILTER(data_range, ISNUMBER(data_range)),
        sorted_data, SORT(clean_data),
        n, ROWS(sorted_data),
        decile_indices, SEQUENCE(10, 1, 1, 1),
        decile_shares, BYROW(decile_indices, LAMBDA(d,
            LET(
                p_lower, (d - 1) / 10,
                p_upper, d / 10,
                idx_low, MAX(1, ROUNDUP(p_lower * n, 0)),
                idx_high, ROUNDUP(p_upper * n, 0),
                subset, CHOOSEROWS(sorted_data, SEQUENCE(idx_high - idx_low + 1, 1, idx_low, 1)),
                decile_mean, AVERAGE(subset),
                decile_mean
            )
        )),
        total_sum, SUM(decile_shares),
        proportions, decile_shares / total_sum,
        downside_penalties, MAP(decile_indices, proportions, LAMBDA(idx, prop,
            IF(idx <= 5, prop * (1 + delta / (idx * gamma)), prop * (1 - (idx - 5) * 0.02))
        )),
        header, HSTACK("Decile Index", "Mean Share", "Downside Weighted"),
        result_table, HSTACK(decile_indices, proportions, downside_penalties),
        VSTACK(header, result_table)
    )
)

Integrating VSTACK for Multi-Scenario Comparisons

To evaluate sensitivity across multiple shape configurations simultaneously, leverage VSTACK to stack model projections into an integrated comparative dashboard:

=LET(
    scenario_base, CALC.GLL18.DECILES(A2:A1001, 1.25, 2.10, 0.45),
    scenario_shock, CALC.GLL18.DECILES(A2:A1001, 1.45, 1.80, 0.75),
    tag_base, EXPAND("Baseline", ROWS(scenario_base), 1, "Baseline"),
    tag_shock, EXPAND("Stressed", ROWS(scenario_shock), 1, "Stressed"),
    combined_base, HSTACK(tag_base, scenario_base),
    combined_shock, HSTACK(tag_shock, scenario_shock),
    VSTACK(
        HSTACK("Scenario", "Decile", "Proportion", "Downside Metric"),
        DROP(combined_base, 1),
        DROP(combined_shock, 1)
    )
)

Verifying Metric Convergence and Stability

Ensure that the resulting decile shares satisfy conservation and monotonicity requirements:

Decile Bracket	Baseline GLL18 Proportion	Downside Weighted Metric	Jenkins-Cowell Multiplier
Decile 1 (Bottom 10%)	0.0182	0.0247	1.357
Decile 2	0.0315	0.0403	1.279
Decile 5 (Median)	0.0842	0.0891	1.058
Decile 10 (Top 10%)	0.3120	0.2808	0.900

By shifting inequality evaluations from static spreadsheet cells to dynamic array formulas, financial modellers achieve institutional-grade auditability and instant reactivity across massive datasets.