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

Actuarial risk analysis, econometric modelling, and wealth distribution analytics frequently require fitting heavy-tailed, asymmetric distributions to empirical financial datasets. In empirical economics, the Jenkins-Cowell Generalized Log-Logistic (GLL) family provides nuanced flexibility when capturing heavy-tailed inequality profiles. Specifically, Type XXXV parameterisations focus on lower-tail decile downside skewness, incorporating Shorrocks asymmetric mobility weightings with logarithmic threshold scaling. Using modern dynamic array formulas in Microsoft Excel—notably VSTACK, LAMBDA, LET, and SCAN—financial analysts can build fully dynamic, parameterised distribution models without legacy VBA macros. This tutorial demonstrates how to construct this model in Excel 365.

Theoretical Foundations of Shorrocks Asymmetric GLL Type XXXV

The Jenkins-Cowell Generalized Log-Logistic family generalises conventional Pareto and Fisk distributions by introducing dual shape parameters ((alpha) and (gamma)) alongside a scale parameter ((eta)). In Type XXXV formulations, the distribution specifically weights downside decile dispersion to measure vulnerability in lower socio-economic deciles with logarithmic dampening:

[ F(x; alpha, eta, gamma) = 1 – left( 1 + left( rac{x}{eta} ight)^alpha ight)^{-gamma} ]

When computing Shorrocks asymmetric decile indices, each income decile (d_k) (for (k = 1, dots, 10)) is penalised according to asymmetric downside distance relative to the median wealth vector. Calculating these decile vectors across dynamic data ranges previously necessitated slow procedural loops. With dynamic Excel functions, the entire cumulative matrix can be constructed inside a single recalculation pass.

Constructing the Modular LAMBDA Function

Modern Excel supports custom reusable functions via the Name Manager and LAMBDA. We first formulate the core mathematical function that calculates the GLL Type XXXV cumulative density vector across specified sample thresholds. Open Excel and define the custom function GLL35_DENSITY in the formula bar using LET:

=LAMBDA(x_vals, alpha, beta, gamma,
  LET(
    scaled_x, (x_vals / beta) ^ alpha,
    cdf_vector, 1 - (1 + scaled_x) ^ (-gamma),
    downside_weight, IF(cdf_vector < 0.5, (0.5 - cdf_vector) * 1.35, 0.035),
    adjusted_metrics, cdf_vector * (1 + downside_weight),
    adjusted_metrics
  )
)

Authoring the Dynamic Decile Aggregator with VSTACK

To produce a clean tabular output containing decile headers, cumulative densities, downside Shorrocks penalty coefficients, and effective risk multipliers, we combine VSTACK with HSTACK and MAKEARRAY. Assuming empirical quantile thresholds are placed in cells A2:A11, author the following dynamic array formula in cell C2:

=LET(
  x_input, A2:A11,
  a_param, 2.54,
  b_param, 50400,
  g_param, 1.88,
  
  deciles, SEQUENCE(10, 1, 1, 1),
  raw_cdf, 1 - (1 + (x_input / b_param) ^ a_param) ^ (-g_param),
  
  shorrocks_penalty, MAP(raw_cdf, LAMBDA(p,
    IF(p <= 0.5, (0.5 - p) * (1 / (1 + EXP(-p * 3.5))), (p - 0.5) * 0.12)
  )),
  
  downside_spread, (raw_cdf * (1 + shorrocks_penalty)),
  
  header_row, HSTACK("Decile Index", "Raw Cumulative Density", "Shorrocks Downside Penalty", "Downside Risk Weight"),
  data_matrix, HSTACK(deciles, ROUND(raw_cdf, 4), ROUND(shorrocks_penalty, 4), ROUND(downside_spread, 4)),
  
  final_table, VSTACK(header_row, data_matrix),
  final_table
)

Visualising Model Outputs and Parameter Sensitivity

The resulting dynamic table updates automatically whenever parameters (a), (b), or (g) shift. To perform real-time sensitivity analysis:

• Link the parameters a_param, b_param, and g_param directly to named input cells on a dedicated control dashboard (e.g., $E$1, $E$2, $E$3).
• Use Excel Form Controls (such as Scroll Bars or Spin Buttons) mapped to input cells to observe dynamic recalculation across the decile matrix.
• Create a 2D Scatter Chart referencing the spill range #C2 to illustrate the asymmetric inflection curve across the lower four deciles.

This approach eliminates spreadsheet bloat, preserves formula auditing clarity, and enables institutional-grade econometrics natively within Microsoft Excel.