How to Use VSTACK and LAMBDA to Author Dynamic Theil L (Mean Log Deviation) Models in Microsoft Excel

In quantitative economic disparity modeling, the Theil index family provides two foundational metrics: the Theil T index ((GE_1), weighted by income shares) and the Theil L index ((GE_0), also known as Mean Log Deviation / MLD, weighted by population shares). Economists favor the Theil L index because it is strictly subgroup decomposable: total inequality decomposes perfectly into within-group inequality and between-group inequality without complex interaction terms. Because it computes deviations using natural logarithms, Theil L is uniquely sensitive to income shortfalls among the poorest deciles. In modern Microsoft Excel, leveraging dynamic array formulas with LAMBDA and VSTACK provides an automated modeling engine without macros.

Understanding the Theil L (Mean Log Deviation) Mathematical Formulation

For a population of (N) individuals with incomes (y_i) and arithmetic mean (mu), the Theil L Index ((L)) is defined mathematically as:

L = (1 / N) * SUM( LN(mean / y_i) )   [for all y_i > 0]
L = LN(mean) - (1 / N) * SUM( LN(y_i) ) = LN(Arithmetic_Mean) - LN(Geometric_Mean)

• Arithmetic vs Geometric Mean Divergence: The Theil L index is mathematically equivalent to the natural logarithm of the ratio between the arithmetic mean and the geometric mean of the distribution ((L = ln(mu / mu_g))).
• (L = 0) (Complete Equality): The arithmetic mean equals the geometric mean. Every individual holds an identical income share.
• Bottom-Tail Sensitivity: Because (ln(mu / y_i)) approaches infinity as income approaches zero, Theil L places substantial mathematical weight on poverty and extreme bottom-tier shortfalls.

Building the Dynamic LAMBDA Theil L Calculation Engine

Assuming raw observations reside in A2:A50 (e.g. Household Income or Regional Salaries), author a reusable formula using LET:

=LAMBDA(values,
    LET(
        clean_y, FILTER(values, ISNUMBER(values) * (values > 0)),
        n, ROWS(clean_y),
        mu_arithmetic, AVERAGE(clean_y),
        mu_geometric, EXP((1 / n) * SUM(LN(clean_y))),
        theil_l, LN(mu_arithmetic / mu_geometric),
        theil_l
    )
)

Constructing the Executive Summary Dashboard with VSTACK

Assemble a comprehensive spilled reporting dashboard comparing Theil L, Theil T, arithmetic mean, and geometric mean benchmarks using VSTACK and HSTACK:

=LET(
    incomes, B2:B40,
    clean_d, FILTER(incomes, ISNUMBER(incomes) * (incomes > 0)),
    n_count, ROWS(clean_d),
    
    mean_a, AVERAGE(clean_d),
    mean_g, EXP((1 / n_count) * SUM(LN(clean_d))),
    
    theil_l, LN(mean_a / mean_g),
    
    ratios, clean_d / mean_a,
    theil_t, (1 / n_count) * SUM(ratios * LN(ratios)),
    
    headers, HSTACK("Entropy Metric", "Calculated Value", "Statistical Interpretation"),
    row1, HSTACK("Theil L Index (MLD / GE0)", TEXT(theil_l, "0.0000"), IF(theil_l < 0.15, "Low Bottom-Tail Disparity", IF(theil_l < 0.4, "Moderate Bottom Inequality", "Severe Poverty Disparity"))),
    row2, HSTACK("Theil T Index (GE1)", TEXT(theil_t, "0.0000"), "Balanced Distribution Measure"),
    row3, HSTACK("Arithmetic Mean Income", TEXT(mean_a, "$#,##0.00"), "Unweighted Standard Average"),
    row4, HSTACK("Geometric Mean Income", TEXT(mean_g, "$#,##0.00"), "Inequality-Penalised Typical Income"),
    row5, HSTACK("Mean Ratio Penalty (mu_a / mu_g)", TEXT(mean_a / mean_g, "0.000"), "Relative Divergence Factor"),
    
    VSTACK(headers, row1, row2, row3, row4, row5)
)

Key Methodological Advantages

1. Elegant Mean Comparison: Expressing Theil L as LN(mean_a / mean_g) guarantees rapid computation and avoids per-row division operations.
2. Instant Dynamic Recalculation: Updating source compensation numbers immediately updates both the geometric mean and Theil L index across adjacent cells.
3. Macro-Free Portability: Encapsulate the formula inside Excel’s Name Manager as CALC_THEIL_L for seamless re-use across executive auditing and enterprise compensation models.