How to Use VSTACK and LAMBDA to Author Dynamic Suits Index Tax Progressivity Models in Microsoft Excel

The Suits Index is a widely utilised metric in public finance and macroeconomic policy designed to assess the progressivity of tax structures. Derived from the geometry of the Suits curve—which plots cumulative percentage of tax liability on the vertical axis against cumulative percentage of income on the horizontal axis—the Suits Index is analogous to the Gini coefficient. While the Kakwani index calculates progressivity by subtracting the pre-tax income Gini from the tax concentration index, the Suits Index calculates progressivity directly using relative Lorenz geometry. In Microsoft Excel, combining LAMBDA, SORT, and VSTACK provides an elegant, single-formula modeling engine.

Understanding the Suits Progressivity Index Range and Formula

The Suits Index ((S)) ranges bounded between (-1) and (+1):

• (S = +1) (Extreme Progressivity): The highest-earning individual pays 100% of the entire tax burden.
• (S > 0) (Progressive Tax): Higher-income individuals pay a proportionately larger share of taxes relative to their income share.
• (S = 0) (Proportional Tax): A perfectly flat tax structure where tax shares mirror income shares identically.
• (S < 0) (Regressive Tax): Lower-income individuals shoulder a disproportionate tax burden relative to their income (common in sales and excise taxes).
• (S = -1) (Extreme Regressivity): The lowest-earning individual pays 100% of the tax burden.

Mathematical Formulation Using Trapezoidal Integration

When ranked by income deciles or individual returns with cumulative income shares (y_i) and cumulative tax shares (t_i), the Suits Index is calculated using trapezoidal integration:

S = 1 - (1 / 5000) * SUM( (1/2) * (t_i + t_{i-1}) * (y_i - y_{i-1}) )   [when using percentages 0-100]
S = 1 - 2 * Area_under_Suits_curve                                        [when using fractions 0-1]

Building the Dynamic Suits Engine with LAMBDA

Assuming pre-tax income is in A2:A40 and taxes paid are in B2:B40, create a custom formula using LET:

=LAMBDA(income, taxes,
    LET(
        valid, FILTER(HSTACK(income, taxes), ISNUMBER(income) * (income > 0)),
        sorted, SORT(valid, 1, 1),
        inc, CHOOSECOLS(sorted, 1),
        tax, CHOOSECOLS(sorted, 2),
        
        cum_inc, SCAN(0, inc / SUM(inc), LAMBDA(acc, x, acc + x)),
        cum_tax, SCAN(0, tax / SUM(tax), LAMBDA(acc, x, acc + x)),
        
        y_prev, VSTACK(0, DROP(cum_inc, -1)),
        t_prev, VSTACK(0, DROP(cum_tax, -1)),
        
        trapezoids, 0.5 * (cum_tax + t_prev) * (cum_inc - y_prev),
        area_k, SUM(trapezoids),
        suits, 1 - 2 * area_k,
        suits
    )
)

Assembling the Executive Dashboard with VSTACK

Combine your calculations into a spilled dashboard table complete with descriptive metrics and antitrust/fiscal evaluations:

=LET(
    incomes, A2:A40,
    tax_paid, B2:B40,
    
    valid, FILTER(HSTACK(incomes, tax_paid), ISNUMBER(incomes) * (incomes > 0)),
    s, SORT(valid, 1, 1),
    y, CHOOSECOLS(s, 1),
    t, CHOOSECOLS(s, 2),
    
    cy, SCAN(0, y / SUM(y), LAMBDA(a, v, a + v)),
    ct, SCAN(0, t / SUM(t), LAMBDA(a, v, a + v)),
    
    y0, VSTACK(0, DROP(cy, -1)),
    t0, VSTACK(0, DROP(ct, -1)),
    
    suits_val, 1 - 2 * SUM(0.5 * (ct + t0) * (cy - y0)),
    
    headers, HSTACK("Progressivity Measure", "Index Value", "Fiscal Evaluation"),
    row1, HSTACK("Suits Progressivity Index (S)", TEXT(suits_val, "0.0000"), IF(suits_val > 0.15, "Strongly Progressive", IF(suits_val > 0, "Mildly Progressive", IF(suits_val = 0, "Proportional", "Regressive")))),
    row2, HSTACK("Suits Curve Trapezoid Area", TEXT(0.5 * (1 - suits_val), "0.0000"), "Area Under Lorenz Transform"),
    
    VSTACK(headers, row1, row2)
)

Why This Method Outperforms Legacy Spreadsheets

1. Continuous Dynamic Integration: By utilizing SCAN and DROP, the formula computes discrete trapezoidal Riemann sums instantaneously without requiring auxiliary helper columns.
2. Safe Sorting Automation: Automatically re-sorts all observations by income rank, guaranteeing mathematical accuracy regardless of raw data ordering.
3. Enterprise Portability: Encapsulate the calculation in Excel’s Name Manager as CALC_SUITS_INDEX for seamless re-use across tax and fiscal budget models.