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

The Foster-Greer-Thorbecke (FGT) metric family represents the international gold standard for multidimensional poverty measurement, widely adopted by the World Bank, the United Nations, and governmental statistical agencies. By adjusting a single sensitivity parameter ($alpha$), the FGT formulation unifies the headcount poverty ratio ($FGT_0$), the poverty gap index ($FGT_1$), and the poverty severity index ($FGT_2$) into a single continuous mathematical framework. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic FGT poverty models cleanly without legacy VBA macros.

Mathematical Foundations of the FGT Metric Family

Given an observed income distribution vector $y_1, y_2, dots, y_n$ and an established poverty threshold $z$, the discrete Foster-Greer-Thorbecke index $P_alpha$ is defined as:

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

Where:

• n denotes the total population count (including both non-poor and poor individuals).
• z represents the poverty line threshold.
• alpha = 0 yields the Headcount Ratio ($P_0$): the proportion of the population living below the poverty line.
• alpha = 1 yields the Poverty Gap Index ($P_1$): the average shortfall of the poor relative to the poverty line, measuring poverty depth.
• alpha = 2 yields the Poverty Severity Index ($P_2$): squaring the normalised shortfalls to place greater weight on the poorest individuals, satisfying Sen’s transfer axiom.

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can structure the calculation into readable intermediate steps that handle uncurated columns dynamically:

=LET(
    raw_data, A2:A101,
    poverty_line, 1500,
    alpha_val, 2,
    clean_data, FILTER(raw_data, ISNUMBER(raw_data) * (raw_data > 0)),
    n, COUNT(clean_data),
    poor_data, FILTER(clean_data, clean_data < poverty_line),
    q, COUNT(poor_data),
    IF(q = 0, 0,
        LET(
            shortfalls, (poverty_line - poor_data) / poverty_line,
            fgt_index, (1 / n) * SUM(shortfalls^alpha_val),
            fgt_index
        )
    )
)

This dynamic formula automatically filters observations below the poverty line, computes normalised shortfalls, raises them to power $alpha$, and derives the composite FGT index instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

To deploy this metric across multiple economic workbooks, define a reusable custom function named FGT.INDEX in the Excel Name Manager:

1. Navigate to the Formulas tab and click Name Manager.
2. Click New, enter FGT.INDEX in the Name field, and set Scope to Workbook.
3. In the Refers to box, input the following formula:

   =LAMBDA(values, poverty_line, alpha_param,
       LET(
           clean, FILTER(values, ISNUMBER(values) * (values > 0)),
           n, COUNT(clean),
           poor, FILTER(clean, clean < poverty_line),
           q, COUNT(poor),
           IF(q = 0, 0,
               (1 / n) * SUM(((poverty_line - poor) / poverty_line)^alpha_param)
           )
       )
   )

4. Click OK and close the dialogue.

Building Comparative Multi-Tier Dashboards with VSTACK

With the custom function established, we can compute all three FGT tiers across regional cohorts simultaneously using VSTACK:

=LET(
    cohort, B2:B100,
    line, 1500,
    header, HSTACK("Poverty Metric", "Parameter Alpha", "Index Value", "Interpretation"),
    row_0, HSTACK("Headcount Ratio (P0)", 0, FGT.INDEX(cohort, line, 0), "Poverty Incidence"),
    row_1, HSTACK("Poverty Gap (P1)", 1, FGT.INDEX(cohort, line, 1), "Poverty Depth"),
    row_2, HSTACK("Poverty Severity (P2)", 2, FGT.INDEX(cohort, line, 2), "Poverty Severity"),
    VSTACK(header, row_0, row_1, row_2)
)

This dynamic array structure automatically updates whenever underlying survey data changes, delivering instantaneous insight into poverty incidence, depth, and severity across different socioeconomic cohorts.