How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Asymmetric Decile Downside Sharma-Mittal Entropy Models in Microsoft Excel

Modern econometric evaluation of social and income mobility necessitates robust indices capable of isolating downside transition volatility. While traditional Shorrocks mobility measures assess aggregate matrix turnover, incorporating generalized dual-parameter entropy formulations—such as the Sharma-Mittal entropy framework—permits precise penalisation of downward decile trajectories. Modern Microsoft Excel dynamic array engines empower analysts to author fully responsive models using VSTACK, MAP, and custom LAMBDA functions.

Mathematical Foundations of Downside Sharma-Mittal Entropy

The Sharma-Mittal entropy generalises both Rényi and Tsallis formulations via two distinct structural parameters: degree parameter $r$ and order parameter $q$. When applied to a transition probability matrix $P$, the downside matrix components $P_{ij}$ (where target decile $j < i$) measure downward societal churn. Authoring this in dynamic arrays allows real-time sensitivity tuning across longitudinal cohort studies.

Structuring the Dynamic LAMBDA Formula

To compute the downside entropy dynamically without legacy VBA macros, define a reusable named formula SHORROCKS_SM_DOWNSIDE within the Excel Name Manager:

=LAMBDA(transition_matrix, r_param, q_param,
    LET(
        n, ROWS(transition_matrix),
        row_indices, SEQUENCE(n, 1, 1, 1),
        col_indices, SEQUENCE(1, n, 1, 1),
        downside_mask, row_indices > col_indices,
        filtered_p, IF(downside_mask, transition_matrix, 0),
        sum_p, SUM(filtered_p),
        norm_p, IF(sum_p > 0, filtered_p / sum_p, 0),
        pow_p, IF(norm_p > 0, norm_p ^ r_param, 0),
        sum_pow, SUM(pow_p),
        sharma_mittal, IF(
            q_param = 1,
            (1 / (1 - r_param)) * (sum_pow ^ ((1 - q_param) / (1 - r_param)) - 1),
            (1 / (1 - q_param)) * ((sum_pow) ^ ((1 - q_param) / (1 - r_param)) - 1)
        ),
        sharma_mittal
    )
)

Iterating Across Cohorts with VSTACK and SCAN

To stack multi-period longitudinal decile transitions across sequential census datasets, employ VSTACK to assemble empirical time series outputs into a unified visual summary table:

=LET(
    cohort_1, Table2010[#Data],
    cohort_2, Table2015[#Data],
    cohort_3, Table2020[#Data],
    r_val, 1.4,
    q_val, 0.8,
    e1, SHORROCKS_SM_DOWNSIDE(cohort_1, r_val, q_val),
    e2, SHORROCKS_SM_DOWNSIDE(cohort_2, r_val, q_val),
    e3, SHORROCKS_SM_DOWNSIDE(cohort_3, r_val, q_val),
    headers, HSTACK("Period", "Downside Sharma-Mittal Index"),
    row1, HSTACK("2010-2015", e1),
    row2, HSTACK("2015-2020", e2),
    row3, HSTACK("2020-2025", e3),
    VSTACK(headers, row1, row2, row3)
)

Model Validation and Numerical Diagnostics

When implementing non-linear parametric entropy measures, incorporate rigorous sanity checks across edge conditions:

• Boundary Checks: Ensure $r eq 1$ to prevent direct division-by-zero singularities within native evaluation steps.
• Normalisation Verification: Confirm that the conditional downside transition probabilities properly sum to 1 before power transformation.
• Dynamic Recalculation: Adjust parameter inputs in linked cells to observe instant recalculation across stacked cohorts without workbook lag.

By synthesising higher-order entropy formulations within Microsoft Excel’s dynamic array paradigm, quantitative researchers obtain unparalleled analytical flexibility without reliance on third-party computational packages.