How to Use VSTACK and LAMBDA to Author Dynamic Watts Poverty Index Models in Microsoft Excel

While the Foster-Greer-Thorbecke (FGT) family of poverty indices is widely utilized, standard poverty metrics often fail to satisfy the strict "transfer axiom"—the principle that a transfer of income from a poor person to a richer poor person should strictly increase measured poverty. Economists regard the Watts Poverty Index as the premier distribution-sensitive poverty measure that rigorously satisfies both the transfer axiom and subgroup decomposability. By taking the logarithm of the ratio between the poverty threshold and individual incomes, the Watts Index places increasing weight on those furthest below the poverty line. In modern Microsoft Excel, leveraging LAMBDA and VSTACK allows analysts to author automated Watts poverty models without macros.

Understanding the Watts Poverty Index Formulation

For a population of (N) individuals with incomes (y_i) and a poverty threshold (z), the Watts Poverty Index ((W)) is defined mathematically as:

W = (1 / N) * SUM( LN(z / y_i) )   [for all y_i < z]

• Distribution Sensitivity: Because the natural logarithm function has an increasingly steep slope as income approaches zero, a transfer of income away from an extremely poor individual causes a much larger spike in the index than a transfer from someone near the poverty line.
• Time-to-Exit Interpretation: Under the assumption of constant percentage economic growth ((g)), the Watts Index directly represents the expected average time required for the poor to exit poverty ((T = W / g)).

Building the Dynamic LAMBDA Watts Calculation Engine

Assuming household income observations reside in A2:A50 and cell E2 holds the poverty threshold (z), author a reusable formula using LET:

=LAMBDA(incomes, z_line,
    LET(
        clean_y, FILTER(incomes, ISNUMBER(incomes) * (incomes > 0)),
        n, ROWS(clean_y),
        poor_y, FILTER(clean_y, clean_y < z_line, 0),
        IF(
            poor_y = 0,
            0,
            LET(
                log_ratios, LN(z_line / poor_y),
                watts_val, (1 / n) * SUM(log_ratios),
                watts_val
            )
        )
    )
)

Constructing the Executive Summary Matrix with VSTACK

Assemble a comprehensive summary table comparing the Watts Index against poverty headcounts and projected time-to-exit metrics using VSTACK and HSTACK:

=LET(
    incomes, B2:B40,
    z, E2,
    growth_rate, 0.03,
    
    clean_d, FILTER(incomes, ISNUMBER(incomes) * (incomes > 0)),
    n_count, ROWS(clean_d),
    poor, FILTER(clean_d, clean_d < z, 0),
    
    poor_count, IF(poor = 0, 0, ROWS(poor)),
    headcount_pct, poor_count / n_count,
    watts, IF(poor = 0, 0, (1 / n_count) * SUM(LN(z / poor))),
    exit_years, IF(watts = 0, 0, watts / growth_rate),
    
    headers, HSTACK("Poverty Analytical Metric", "Calculated Value", "Policy Interpretation"),
    row1, HSTACK("Poverty Threshold (z)", TEXT(z, "$#,##0.00"), "Baseline Standard of Living"),
    row2, HSTACK("Headcount Proportion (P0)", TEXT(headcount_pct, "0.0%"), "Proportion of Impoverished Population"),
    row3, HSTACK("Watts Poverty Index (W)", TEXT(watts, "0.0000"), "Distribution-Sensitive Hardship Depth"),
    row4, HSTACK("Expected Exit Horizon (3% Growth)", TEXT(exit_years, "0.0") & " Years", "Projected Time Required to Eliminate Poverty"),
    
    VSTACK(headers, row1, row2, row3, row4)
)

Key Advantages of This Implementation

1. Instant Scenario Re-evaluation: Updating the poverty line in cell E2 instantly updates all poverty metrics and time-to-exit projections simultaneously.
2. Mathematical Protection: The FILTER logic eliminates non-positive values, safeguarding against invalid natural logarithm calculations (#NUM!).
3. Zero Code Deployment: Encapsulate the formula inside Excel’s Name Manager as CALC_WATTS_INDEX for seamless re-use across developmental and economic models.