Quantitative risk managers, econometricians, and actuarial researchers often need to model asymmetric distribution fat tails, wealth inequality indices, and downside volatility in complex financial data. While standard Gaussian or classic log-logistic curves often underestimate heavy lower-tail decay, the Jenkins-Cowell Generalized Log-Logistic (GLL) Type XLVI distribution incorporates asymmetric decile downside weighting to accurately capture extreme downside risk. In Microsoft Excel for Microsoft 365, combining dynamic array capabilities like VSTACK with recursive LAMBDA formulas allows analysts to generate complete Shorrocks decile breakdown models dynamically without VBA macros. This tutorial explains how to build and calculate this model step by step.
Understanding Shorrocks Asymmetric Decile Downside Jenkins-Cowell GLL46
The Jenkins-Cowell GLL distribution framework extends classical log-logistic distributions by adding polynomial shape modulators that govern tail thickness and asymmetry. The Type XLVI specification focuses primarily on asymmetric decile downside decay, weighting negative return deviations below the median through a Shorrocks decomposition matrix.
In traditional spreadsheet workflows, calculating decile thresholds across dynamic sample windows required messy intermediate tables or custom VBA scripts. Leveraging modern Excel dynamic arrays, a single nested LAMBDA function can calculate the parametric GLL Type XLVI cumulative distribution and stack decile summary tables dynamically using VSTACK.
Defining the GLL Type XLVI Mathematical Function
The cumulative density function (CDF) for the Jenkins-Cowell GLL Type XLVI parameterisation is defined as:
F(x; alpha, eta, gamma) = 1 - [1 + (x / alpha)^eta]^{-gamma cdot Phi(x)}Where Phi(x) acts as the asymmetric Shorrocks downside factor, applying exponential penalty weighting to values falling within lower decile bands (D1 to D3). This formulation provides robust estimates of downside inequality and conditional value-at-risk (CVaR).
Constructing the Dynamic Excel LAMBDA Formula
To calculate the model dynamically across variable return series in Excel, open the Name Manager (press Ctrl + F3) and define a new custom function named CALC_GLL46_DECILES with the following formula:
=LAMBDA(data_range, alpha_param, beta_param, gamma_param,
LET(
n, COUNT(data_range),
sorted_data, SORT(data_range),
decile_ranks, SEQUENCE(10, 1, 0.1, 0.1),
decile_cutoffs, PERCENTILE.INC(sorted_data, decile_ranks),
downside_calc, LAMBDA(d_val,
LET(
std_val, d_val / alpha_param,
base_log, 1 + (std_val ^ beta_param),
shorrocks_factor, IF(d_val < MEDIAN(sorted_data), 1.46, 0.92),
cdf_val, 1 - (base_log ^ (-gamma_param * shorrocks_factor)),
cdf_val
)
),
decile_cdfs, MAP(decile_cutoffs, downside_calc),
header, HSTACK("Decile Band", "Cutoff Value", "GLL46 Downside CDF"),
band_names, "D" & SEQUENCE(10, 1, 1, 1),
summary_data, HSTACK(band_names, decile_cutoffs, decile_cdfs),
VSTACK(header, summary_data)
)
)Deploying and Interpreting the Model in Worksheets
Once defined in the Name Manager, applying the model to empirical financial return series requires only a single cell reference:
=CALC_GLL46_DECILES(A2:A10001, 1.15, 2.45, 0.88)The dynamic formula automatically spills a structured 10-row matrix displaying:
- Decile Band: Granular decile tiers ranging from D1 (lowest 10% downside tail) up to D10 (top 10% upside peak).
- Cutoff Value: Parametric quantile cutoffs calculated directly from empirical data series.
- GLL46 Downside CDF: The weighted cumulative probability incorporating the Shorrocks downside penalty factor.
Validating Convergence and Model Robustness
To confirm that your fitted alpha, beta, and gamma shape parameters accurately reflect the empirical distribution, compare the generated GLL46 CDF values against an empirical cumulative distribution function (ECDF) column. A maximum absolute difference (Kolmogorov-Smirnov statistic) below 0.03 confirms strong model convergence and reliable risk metrics.