How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Asymmetry Mobility Models in Microsoft Excel

Traditional economic mobility indices evaluate gross movement: whether an individual experiences a positive promotion or a devastating income loss, both contribute equally to gross mobility measures. However, welfare economists—building on Anthony Shorrocks foundational mobility axioms—demonstrate that directional asymmetry is vital: an economy where bottom-tier individuals experience rapid upward advancement while top-tier ranks remain stable presents fundamentally different welfare characteristics than an economy characterized by severe downward demotion. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic directional mobility asymmetry models cleanly without legacy macros.

Mathematical Foundations of Directional Mobility Asymmetry

Given an observed income distribution vector across initial period $x$ and final period $y$, the discrete directional mobility index decomposes movement into upward ($M^+$) and downward ($M^-$) trajectories:

M^+ = (1 / n) * SUM( max(0, y_i - x_i) )
M^- = (1 / n) * SUM( max(0, x_i - y_i) )

The Shorrocks Mobility Asymmetry Ratio $A_{mob}$ evaluates the relative prevalence of upward versus downward progression:

A_mob = (M^+ - M^-) / (M^+ + M^-)

Where:

• n denotes the total population count in the longitudinal cohort.
• M^+ represents the average per-capita upward income movement.
• M^- represents the average per-capita downward income movement.
• A_mob in [-1, 1]. If $A_{mob} > 0$, the economy is characterized by net upward mobility (structural expansion). If $A_{mob} < 0$, downward economic demotion dominates. If $A_{mob} = 0$, mobility is directionally balanced.

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can construct the directional asymmetry 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),
    n, COUNT(x),
    diffs, y - x,
    upward_moves, IF(diffs > 0, diffs, 0),
    downward_moves, IF(diffs < 0, -diffs, 0),
    m_plus, AVERAGE(upward_moves),
    m_minus, AVERAGE(downward_moves),
    asymmetry_ratio, (m_plus - m_minus) / (m_plus + m_minus),
    asymmetry_ratio
)

This dynamic formula separates positive gains from negative losses, computes average directional vectors, and derives the composite asymmetry ratio instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

To deploy this metric across multiple longitudinal models, define a reusable custom function named MOBILITY.ASYMMETRY in the Excel Name Manager:

1. Navigate to the Formulas tab and click Name Manager.
2. Click New, enter MOBILITY.ASYMMETRY in the Name field, and set Scope to Workbook.
3. In the Refers to box, input the formula to calculate directional asymmetry.
4. Click OK and close the dialogue.

Building Comparative Directional Dashboards with VSTACK

We can combine multiple regional or demographic panels into a side-by-side comparative dashboard using VSTACK:

=LET(
    urban_p1, A2:A50, urban_p2, B2:B50,
    rural_p1, C2:C50, rural_p2, D2:D50,
    header, HSTACK("Demographic Cohort", "Asymmetry Ratio", "Directional Bias", "Net Movement (£)"),
    row_1, HSTACK("Urban Technology Sector", MOBILITY.ASYMMETRY(urban_p1, urban_p2), "Net Upward Progression", AVERAGE(urban_p2 - urban_p1)),
    row_2, HSTACK("Deindustrialised Zone", MOBILITY.ASYMMETRY(rural_p1, rural_p2), "Net Downward Contraction", AVERAGE(rural_p2 - rural_p1)),
    VSTACK(header, row_1, row_2)
)

This dynamic array structure automatically updates whenever longitudinal tracking data expands, delivering instantaneous insight into economic opportunity, upward progression, and vulnerability across different demographic groups.