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

Modeling economic distributions with severe skewness and heavy kurtosis often challenges standard parametric distributions. The sinh-arcsinh (SHASH) distribution, introduced by Chris Jones and Arthur Pewsey, provides extraordinary flexibility by controlling skewness and tail-weight via independent parameters without altering the location and scale. By combining the SHASH framework with Anthony Shorrocks’ inequality decomposition methodology and Stephen Jenkins and Frank Cowell’s asymmetric sensitivity indexes, analysts can construct highly responsive downside tail risk models. Microsoft Excel’s dynamic array functions—specifically VSTACK, LAMBDA, LET, and MAKEARRAY—enable the implementation of these complex econometric models natively in spreadsheets without VBA macros.

Theoretical Underpinnings of the SHASH Composite Model

The SHASH distribution transforms a standard normal variate Z into random variable X via the inverse transformation:

X = xi + omega * sinh((1 / delta) * (asinh(Z) – epsilon))

where xi is location, omega > 0 is scale, epsilon controls skewness, and delta > 0 governs tail thickness. When delta < 1, the distribution exhibits heavier tails than the normal distribution. By segmenting data into ten decile cohorts, the Shorrocks downside method computes 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_SHASH_SHORROCKS:

=LAMBDA(dataset, param_xi, param_omega, param_epsilon, 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),
                z_raw, (cohort_mean - param_xi) / param_omega,
                z_shash, SINH(param_delta * ASINH(z_raw) + param_epsilon),
                shash_density, NORM.S.DIST(z_shash, FALSE),
                downside_dev, SQRT(AVERAGE((subset - cohort_mean)^2)),
                decile_weight, (11 - r) / 10,
                CHOOSE(c, r, cohort_mean, shash_density, downside_dev, decile_weight * downside_dev)
            )
        )),
        header_row, HSTACK("Decile", "Mean", "SHASH 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 continuous datasets:

1. Input your raw wealth or income observations in column A2:A5000.
2. Enter the location parameter xi in cell C2 (e.g. 35000).
3. Enter the scale parameter omega in cell C3 (e.g. 14000).
4. Enter the skewness parameter epsilon in cell C4 (e.g. 0.35).
5. Enter the tail-weight parameter delta in cell C5 (e.g. 0.85).
6. In cell E2, execute the formula:

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

The custom formula immediately spills a comprehensive 11-row by 5-column analysis table detailing decile means, SHASH 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 complex economic distributions in Microsoft Excel.