How to Use VSTACK and LAMBDA to Author Dynamic Hart Mobility Index Models in Microsoft Excel

In econometric mobility research, understanding how closely an individual current income depends on their past earnings is essential for assessing economic opportunity. Peter Hart formulated the Hart Mobility Index ($H$), which evaluates income mobility through the correlation of logarithmic incomes across successive time periods. If earnings across time periods are perfectly correlated, the index reflects zero mobility; as correlation weakens or reordering occurs, the index approaches unity. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic Hart mobility models cleanly without legacy macros.

Mathematical Foundations of the Hart Index

Given a longitudinal panel of $n$ individuals observed across initial period incomes $x = (x_1, dots, x_n)$ and final period incomes $y = (y_1, dots, y_n)$, let $u_i = ln(x_i)$ and $v_i = ln(y_i)$ represent the logarithmic transformations. The discrete Hart Mobility Index $H$ is defined as:

H = 1 - r(u, v)

Where:

• r(u, v) represents the Pearson product-moment correlation coefficient between the logarithmic income vectors $u$ and $v$.
• If incomes across both periods remain in identical lockstep ($r = 1$), $H = 0$, indicating complete immobility.
• If individual earnings decouple from past status ($r o 0$), $H o 1$, indicating high economic fluidness.
• If severe rank reversals occur ($r < 0$), $H$ can exceed 1, highlighting dramatic structural economic reshuffling.

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can construct the Hart mobility calculation dynamically across two synchronized income columns:

=LET(
    initial_col, A2:A101,
    final_col, B2:B101,
    clean_filter, ISNUMBER(initial_col) * ISNUMBER(final_col) * (initial_col > 0) * (final_col > 0),
    x, FILTER(initial_col, clean_filter),
    y, FILTER(final_col, clean_filter),
    u, LN(x),
    v, LN(y),
    r_val, CORREL(u, v),
    hart_index, 1 - r_val,
    hart_index
)

This dynamic formula removes non-positive or invalid rows, computes natural logarithms, evaluates the Pearson correlation coefficient using CORREL, and outputs the exact Hart mobility index instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

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

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

   =LAMBDA(initial_range, final_range,
       LET(
           filter_valid, ISNUMBER(initial_range) * ISNUMBER(final_range) * (initial_range > 0) * (final_range > 0),
           x, FILTER(initial_range, filter_valid),
           y, FILTER(final_range, filter_valid),
           1 - CORREL(LN(x), LN(y))
       )
   )

4. Click OK and close the dialogue.

Building Comparative Multi-Decade Dashboards with VSTACK

We can combine multiple longitudinal decade cohorts into a side-by-side comparative dashboard using VSTACK:

=LET(
    cohort_90s_p1, A2:A100, cohort_90s_p2, B2:B100,
    cohort_00s_p1, C2:C100, cohort_00s_p2, D2:D100,
    header, HSTACK("Decade Cohort", "Hart Mobility Index", "Correlation r(ln x, ln y)", "Sample Units"),
    row_1, HSTACK("1990-2000 Panel", HART.MOBILITY(cohort_90s_p1, cohort_90s_p2), 1 - HART.MOBILITY(cohort_90s_p1, cohort_90s_p2), COUNT(cohort_90s_p1)),
    row_2, HSTACK("2000-2010 Panel", HART.MOBILITY(cohort_00s_p1, cohort_00s_p2), 1 - HART.MOBILITY(cohort_00s_p1, cohort_00s_p2), COUNT(cohort_00s_p1)),
    VSTACK(header, row_1, row_2)
)

This dynamic array structure automatically updates whenever longitudinal tracking data expands, delivering instantaneous insight into generational earnings mobility and economic fluidity across successive decades.