How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Asymmetric Decile Downside Jenkins-Cowell Johnson SB Distribution Models in Microsoft Excel

Empirical wealth and asset distributions frequently display bounded support, high positive skewness, and fat-tailed characteristics that cannot be modeled accurately by simple parametric distributions. To model bounded economic phenomena, econometricians combine the Johnson SB (Special Bounded) distribution system with Anthony Shorrocks’ inequality decomposition framework and Stephen Jenkins and Frank Cowell’s asymmetric sensitivity indexes. Microsoft Excel’s dynamic array functions—including VSTACK, LAMBDA, LET, and MAKEARRAY—enable analysts to construct fully reactive econometrics models directly in spreadsheet workbooks without VBA macros.

Econometric Mechanics of the Composite Model

The Johnson SB distribution maps a random variable bounded within an interval [xi, xi + lambda] to a standard normal distribution via the transformation:

z = gamma + delta * ln((x – xi) / (xi + lambda – x))

where gamma is the shape parameter governing skewness, delta is the shape parameter governing kurtosis, xi is the lower bound, and lambda is the range. By segmenting data into ten decile cohorts, the Shorrocks downside decomposition calculates dispersion and downside risk metrics weighted by lower-tail sensitivity. Modern dynamic arrays consolidate this recursive calculation into a single modular formula.

Authoring the Reusable LAMBDA Function

Define a custom modular function in Microsoft Excel. Open the Name Manager (press Ctrl + F3) and create a new name called CALC_JOHNSON_SB_SHORROCKS:

=LAMBDA(dataset, param_xi, param_lambda, param_gamma, param_delta,
    LET(
        clean_data, FILTER(dataset, ISNUMBER(dataset)),
        n, ROWS(clean_data),
        sorted, SORT(clean_data),
        decile_ranks, SEQUENCE(10, 1, 1, 1),
        quantiles, PERCENTILE.INC(sorted, decile_ranks / 10),
        starts, VSTACK(MIN(sorted), DROP(quantiles, -1)),
        cohort_matrix, MAKEARRAY(10, 5, LAMBDA(r, c,
            LET(
                d_low, INDEX(starts, r),
                d_high, INDEX(quantiles, r),
                subset, FILTER(sorted, (sorted >= d_low) * (sorted <= d_high)),
                cohort_mean, AVERAGE(subset),
                norm_ratio, (cohort_mean - param_xi) / (param_xi + param_lambda - cohort_mean),
                z_transform, param_gamma + param_delta * LN(norm_ratio),
                johnson_density, NORM.S.DIST(z_transform, FALSE),
                downside_dev, SQRT(AVERAGE((subset - cohort_mean)^2)),
                asym_weight, (11 - r) / 10,
                CHOOSE(c, r, cohort_mean, johnson_density, downside_dev, asym_weight * downside_dev)
            )
        )),
        header_row, HSTACK("Decile", "Mean", "Johnson Density", "Downside Dev", "Weighted Shorrocks Risk"),
        VSTACK(header_row, cohort_matrix)
    )
)

Constructing the Dynamic Spreadsheet Interface

Deploy the function in an active worksheet to analyze bounded distributions:

1. Input your raw wealth or income observations in column A2:A5000.
2. Enter the lower bound parameter xi in cell C2 (e.g. 10000).
3. Enter the range parameter lambda in cell C3 (e.g. 90000).
4. Enter the shape parameter gamma in cell C4 (e.g. 0.45).
5. Enter the shape parameter delta in cell C5 (e.g. 1.25).
6. In cell E2, execute the formula:

   =CALC_JOHNSON_SB_SHORROCKS(A2:A5000, C2, C3, C4, C5)

The custom formula immediately spills an 11-row by 5-column table displaying decile means, Johnson SB density values, downside deviations, and weighted Shorrocks risk indices.

Computing the Aggregate Jenkins-Cowell Tail Score

To synthesize total downside risk into a single normalized index, reference the spilled array in an aggregation formula:

=LET(
    results, E3#,#
    risks, CHOOSECOLS(results, 5),
    densities, CHOOSECOLS(results, 3),
    SUMPRODUCT(risks, densities) / SUM(densities)
)

This dynamic formulation provides quantitative analysts with an automated, transparent metric of downside tail risk across bounded economic distributions in Microsoft Excel.