How to Use VSTACK and LAMBDA to Author Dynamic Foster-Greer-Thorbecke (FGT) Poverty Indexes in Microsoft Excel

In developmental economics, public policy evaluation, and corporate living-wage auditing, measuring economic hardship requires metrics that go beyond simple headcount ratios. The Foster-Greer-Thorbecke (FGT) family of poverty indices is the global standard (utilized by the World Bank and UN) for quantifying poverty. By introducing a sensitivity parameter ((alpha)), the FGT class measures not only how many people live below a designated poverty line (headcount), but also the depth of their poverty (poverty gap) and the severity of inequality among the impoverished (poverty severity). In Microsoft Excel, leveraging modern dynamic arrays with LAMBDA and VSTACK provides an automated modeling engine without macros.

Understanding the FGT Poverty Metric Formulation

For a population of (N) individuals with incomes (y_i) and a poverty threshold (z), the FGT index ((P_alpha)) is defined mathematically as:

P_alpha = (1 / N) * SUM( ((z - y_i) / z)^alpha )   [for all y_i < z]

• (alpha = 0) (Headcount Ratio, (P_0)): The proportion of the population living below the poverty threshold.
• (alpha = 1) (Poverty Gap Index, (P_1)): Measures the average shortfall in income required to bring all poor individuals up to the poverty line, expressed as a percentage of the poverty line.
• (alpha = 2) (Poverty Severity Index, (P_2)): Squares the normalized poverty gaps, placing greater mathematical weight on individuals who are furthest below the poverty line.

Building the Dynamic LAMBDA FGT Calculation Engine

Assuming household or worker income observations reside in A2:A50 and the poverty threshold is defined as (z), author a reusable formula using LET:

=LAMBDA(incomes, z, alpha,
    LET(
        clean_y, FILTER(incomes, ISNUMBER(incomes) * (incomes > 0)),
        n, ROWS(clean_y),
        poor_y, FILTER(clean_y, clean_y < z, 0),
        IF(
            poor_y = 0,
            0,
            LET(
                gaps, (z - poor_y) / z,
                fgt_val, (1 / n) * SUM(gaps^alpha),
                fgt_val
            )
        )
    )
)

Constructing the Executive Summary Dashboard with VSTACK

Assume sample income observations reside in B2:B40 and your reference poverty threshold is set in cell E2 (e.g. $25,000). Construct a comprehensive spilled reporting matrix:

=LET(
    data, B2:B40,
    z_line, E2,
    clean_d, FILTER(data, ISNUMBER(data) * (data > 0)),
    n_count, ROWS(clean_d),
    poor_vals, FILTER(clean_d, clean_d < z_line, 0),
    
    p0, IF(poor_vals = 0, 0, ROWS(poor_vals) / n_count),
    p1, IF(poor_vals = 0, 0, (1 / n_count) * SUM((z_line - poor_vals) / z_line)),
    p2, IF(poor_vals = 0, 0, (1 / n_count) * SUM(((z_line - poor_vals) / z_line)^2)),
    
    headers, HSTACK("FGT Poverty Index", "Calculated Metric", "Socio-Economic Interpretation"),
    row1, HSTACK("P0: Headcount Ratio", TEXT(p0, "0.0%"), "Proportion of Population Below Poverty Line"),
    row2, HSTACK("P1: Poverty Gap Index", TEXT(p1, "0.0%"), "Mean Deficit Relative to Threshold"),
    row3, HSTACK("P2: Poverty Severity Index", TEXT(p2, "0.0000"), "Inequality-Weighted Severity Among Impoverished"),
    row4, HSTACK("Total Poor Count", TEXT(IF(poor_vals = 0, 0, ROWS(poor_vals)), "#,##0"), "Total Identified Households"),
    
    VSTACK(headers, row1, row2, row3, row4)
)

Key Advantages of the Implementation

1. Instant Threshold Adjustments: Modifying cell E2 instantly recalculates all three FGT metrics simultaneously across the spilled table.
2. Division-by-Zero Safety: Handles scenarios with zero impoverished individuals gracefully without throwing #CALC! errors.
3. Enterprise Scalability: Define CALC_FGT_POVERTY in Excel’s Name Manager for corporate social responsibility and economic development models.