How to Use VSTACK and LAMBDA to Author Dynamic Sen-Shorrocks-Blackorby Poverty Models in Microsoft Excel

Poverty measurement in modern econometric research requires metrics that move beyond basic headcount ratios. The Sen-Shorrocks-Blackorby (SST) index (often referred to as the Shorrocks modification of the Sen index) is widely regarded by the United Nations and the World Bank as one of the most rigorous poverty measures. It combines three key dimensions into a single statistic: poverty incidence (headcount ratio), poverty intensity (average poverty gap ratio among the poor), and poverty inequality (the Gini coefficient of poverty gaps across the entire population). Using Excel’s dynamic array engine with LAMBDA, LET, and VSTACK, analysts can build a self-contained, automated SST model without legacy VBA code.

Mathematical Foundations of the SST Poverty Index

The Sen-Shorrocks-Blackorby index (P_{SST}) decomposes cleanly into three intuitive economic factors:

[ P_{SST} = H imes I_{poor} imes (1 + G_{poor}) ]

Equivalently, Shorrocks formulated the index using normalized poverty gap ratios (X_i) across the entire population (N):

[ X_i = maxleft(0, rac{z – y_i}{z} ight) ]

where (z) represents the poverty threshold and (y_i) is individual household income. The full SST index is given by:

[ P_{SST} = mu_X imes (1 + G_X) ]

where (mu_X) is the mean poverty gap across all (N) observations, and (G_X) is the Gini coefficient calculated over the normalized poverty gaps (X).

Authoring the Dynamic SST Model Using LET and LAMBDA

Place your household income data in range B2:B50 and your poverty line (e.g., £24,000) in cell E2. In cell G2, enter this dynamic array formula:

=LET(
    incomes, B2:B50,
    z_line, E2,
    clean_inc, FILTER(incomes, ISNUMBER(incomes)),
    n_total, ROWS(clean_inc),
    
    /* Calculate normalized poverty gap ratios: max(0, (z - y)/z) */
    pov_gaps, MAP(clean_inc, LAMBDA(y, MAX(0, (z_line - y) / z_line))),
    
    /* Identify poor population */
    is_poor, pov_gaps > 0,
    q_poor, SUM(--is_poor),
    headcount_h, q_poor / n_total,
    
    /* Intensity: Average gap among the poor */
    mean_gap_poor, IF(q_poor > 0, AVERAGE(FILTER(pov_gaps, is_poor)), 0),
    
    /* Overall mean poverty gap */
    mu_x, AVERAGE(pov_gaps),
    
    /* Gini coefficient of poverty gaps across entire population */
    sorted_gaps, SORT(pov_gaps, 1, 1),
    ranks, SEQUENCE(n_total),
    gini_x, IF(mu_x = 0, 0, (2 / (n_total * mu_x)) * SUM(ranks * sorted_gaps) - (n_total + 1) / n_total),
    
    /* Sen-Shorrocks-Blackorby Index */
    sst_index, mu_x * (1 + gini_x),
    
    /* Construct Presentation Output */
    headers, HSTACK("Poverty Metric", "Calculated Value", "Interpretation"),
    r1, HSTACK("Headcount Ratio (H)", TEXT(headcount_h, "0.0%"), "Proportion of population below poverty line"),
    r2, HSTACK("Income Gap Ratio (I)", TEXT(mean_gap_poor, "0.0%"), "Average shortfall depth among the poor"),
    r3, HSTACK("Poverty Gap Gini (Gx)", TEXT(gini_x, "0.0000"), "Inequality of poverty gaps across population"),
    r4, HSTACK("Overall Mean Gap (mu_x)", TEXT(mu_x, "0.0000"), "Unconditional average normalized poverty shortfall"),
    r5, HSTACK("SST Poverty Index", TEXT(sst_index, "0.0000"), "Sen-Shorrocks-Blackorby composite poverty measure"),
    
    VSTACK(headers, r1, r2, r3, r4, r5)
)

Constructing a Multi-Threshold Sensitivity Matrix with VSTACK

Policy analysts often assess poverty resilience by evaluating multiple poverty line thresholds (such as 50%, 60%, and 70% of median income). Combine VSTACK and REDUCE to generate a comparative sensitivity table:

=LET(
    incomes, B2:B50,
    clean, FILTER(incomes, ISNUMBER(incomes)),
    med_inc, MEDIAN(clean),
    thresholds, HSTACK(0.5 * med_inc, 0.6 * med_inc, 0.7 * med_inc),
    
    calc_sst, LAMBDA(z,
        LET(
            n, ROWS(clean),
            gaps, MAP(clean, LAMBDA(y, MAX(0, (z - y) / z))),
            mu, AVERAGE(gaps),
            s_gaps, SORT(gaps, 1, 1),
            r, SEQUENCE(n),
            g, IF(mu = 0, 0, (2 / (n * mu)) * SUM(r * s_gaps) - (n + 1) / n),
            mu * (1 + g)
        )
    ),
    
    t_head, HSTACK("Poverty Threshold (% Median)", "Threshold (£)", "SST Index Score"),
    t1, HSTACK("50% Median (Extreme)", TEXT(0.5 * med_inc, "£#,##0"), TEXT(calc_sst(0.5 * med_inc), "0.0000")),
    t2, HSTACK("60% Median (Standard)", TEXT(0.6 * med_inc, "£#,##0"), TEXT(calc_sst(0.6 * med_inc), "0.0000")),
    t3, HSTACK("70% Median (At-Risk)", TEXT(0.7 * med_inc, "£#,##0"), TEXT(calc_sst(0.7 * med_inc), "0.0000")),
    
    VSTACK(t_head, t1, t2, t3)
)

Core Benefits of the Formulaic Implementation

1. Instant Dynamic Spilling: Updating income microdata recalculates the headcount, depth, gap inequality, and composite SST index simultaneously.
2. Auditability and Transparency: Every intermediate metric ((H), (I), (G_X), (mu_X)) is cleanly exposed and calculated according to academic standards.
3. Workbook Portability: Zero reliance on macros or external Python plugins ensures that spreadsheets remain fully compliant with cloud-based Excel for Web environments.