How to Use VSTACK and LAMBDA to Author Dynamic Pietra Ratio Inequality Models in Microsoft Excel

In public finance, tax policy evaluation, and socioeconomic redistribution modeling, economists frequently seek an intuitive answer to a fundamental question: What percentage of total national income would need to be redistributed from individuals earning above the mean to those earning below the mean to achieve perfect equality? While the Gini coefficient measures average pairwise dispersion, the Pietra Ratio (also known as the Ricci-Schutz Index or the Robin Hood Index) provides this exact answer directly. By combining modern Excel dynamic array functions—specifically VSTACK, HSTACK, LAMBDA, LET, FILTER, and SUM—analysts can construct dynamic Pietra Ratio models without legacy VBA macros.

Mathematical Foundations of the Pietra Ratio

For an income distribution of N individuals with incomes y₁, y₂, …, y_N, mean income μ, and total income Y = N * μ, the Pietra Ratio (P) is defined as:

P = (1 / (2 * Y)) * ∑_{i=1}^N |y_i - μ| = (1 / (2 * N * μ)) * ∑_{i=1}^N |y_i - μ|

Equivalently, it can be calculated solely over individuals earning above the mean:

P = (1 / Y) * ∑_{y_i > μ} (y_i - μ)

Geometrically, the Pietra Ratio represents the maximum vertical distance between the Lorenz curve and the 45-degree line of perfect equality. If an economy has a Pietra Ratio of 0.22, transferring exactly 22% of total national income from above-average earners to below-average earners would eliminate all income inequality.

Creating the Reusable LAMBDA Function in Name Manager

In Microsoft Excel (Microsoft 365 or Excel 2024), navigate to Formulas > Name Manager. Click New, enter the name PIETRA_RATIO, and define the formula as follows:

=LAMBDA(incomes,
    LET(
        clean, FILTER(incomes, (incomes >= 0) * ISNUMBER(incomes)),
        tot, SUM(clean),
        mu, AVERAGE(clean),
        surplus_incomes, FILTER(clean, clean > mu),
        surplus_sum, SUM(surplus_incomes - mu),
        pietra, surplus_sum / tot,
        pietra
    )
)

This formula dynamically filters out invalid entries, calculates the mean, isolates individuals earning above the mean, and computes the exact redistribution proportion.

Constructing the Full Dynamic Scorecard with VSTACK

Suppose household survey incomes are listed in cells A2:A20. In cell C2, insert this self-contained reporting formula:

=LET(
    inc, A2:A20,
    p_val, PIETRA_RATIO(inc),
    clean_inc, FILTER(inc, (inc >= 0) * ISNUMBER(inc)),
    n, ROWS(clean_inc),
    tot_inc, SUM(clean_inc),
    mu, AVERAGE(clean_inc),
    redist_amount, tot_inc * p_val,
    
    above_count, COUNTIF(clean_inc, ">" & mu),
    below_count, COUNTIF(clean_inc, "<=" & mu),
    
    header, HSTACK("Redistribution Metric", "Computed Value", "Analytical Assessment"),
    r1, HSTACK("Pietra Ratio (Robin Hood Index)", TEXT(p_val, "0.0%"), IF(p_val > 0.25, "High Redistribution Needed", "Moderate Redistribution Needed")),
    r2, HSTACK("Total Income Pool", TEXT(tot_inc, "£#,##0"), "Total aggregate earnings"),
    r3, HSTACK("Required Equalising Transfer", TEXT(redist_amount, "£#,##0"), "Total transfer from above-mean earners to below-mean earners"),
    r4, HSTACK("Cohorts Above / Below Mean", above_count & " / " & below_count, "Net donors vs. net recipients"),
    r5, HSTACK("Mean Income Benchmark (μ)", TEXT(mu, "£#,##0"), "Threshold separating donors from recipients"),
    
    VSTACK(header, r1, r2, r3, r4, r5)
)

Analytical Advantages in Tax and Fiscal Policy

1. Intuitive Policy Communication: Explaining that “20% of national income must be transferred to eliminate poverty” is far more actionable for policymakers than abstract Gini index numbers.
2. Instant Dynamic Recalculation: Adjusting tax brackets or simulation salaries immediately re-spills across the dashboard with zero manual recalculation.
3. Zero-Macro Portability: Completely macro-free formula architecture that functions flawlessly across desktop, mobile, and web-based Excel environments.