How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Asymmetric Decile Downside Fields-Ok Mobility Models in Microsoft Excel

In economic welfare and longitudinal income studies, measuring directional mobility requires capturing the absolute magnitude of economic descent. The Fields-Ok mobility index quantifies the sum of individual shifts across income deciles. By customising the metric to isolate downside mobility and authoring it in modern Microsoft Excel with VSTACK and LAMBDA, analysts can build real-time, macro-free econometric models.

Mathematical Foundations of the Fields-Ok Downside Measure

The directional Fields-Ok measure decomposes total mobility into upward gains and downward losses. For a decile transition matrix $P$, the downside component aggregates the product of transition probabilities and step distances $|i – j|$ strictly where destination $j < i$:

$$M_{down} = sum_{i > j} P_{ij} cdot (i – j)$$

Constructing the Dynamic LAMBDA Formula

Define a reusable named formula FIELDS_OK_DOWNSIDE inside Excel’s Name Manager:

=LAMBDA(transition_matrix,
    LET(
        n, ROWS(transition_matrix),
        rows_seq, SEQUENCE(n, 1, 1, 1),
        cols_seq, SEQUENCE(1, n, 1, 1),
        is_downside, rows_seq > cols_seq,
        step_distance, IF(is_downside, rows_seq - cols_seq, 0),
        weighted_loss, transition_matrix * step_distance,
        downside_index, SUM(weighted_loss) / (n - 1),
        downside_index
    )
)

Stacking Multi-Decade Analyses with VSTACK

Consolidate results across multiple census intervals dynamically using VSTACK:

=LET(
    cohort90, Table1990[#Data],
    cohort00, Table2000[#Data],
    cohort10, Table2010[#Data],
    cohort20, Table2020[#Data],
    f90, FIELDS_OK_DOWNSIDE(cohort90),
    f00, FIELDS_OK_DOWNSIDE(cohort00),
    f10, FIELDS_OK_DOWNSIDE(cohort10),
    f20, FIELDS_OK_DOWNSIDE(cohort20),
    header, HSTACK("Census Decade", "Fields-Ok Downside Score"),
    r1, HSTACK("1990-2000", f90),
    r2, HSTACK("2000-2010", f00),
    r3, HSTACK("2010-2020", f10),
    r4, HSTACK("2020-2030 (Est)", f20),
    VSTACK(header, r1, r2, r3, r4)
)

Model Diagnostics and Interpretation

A rising Fields-Ok downside score signals increasing structural income vulnerability across cohorts. Modern Excel dynamic arrays recalculate the stacked output instantly whenever source decile frequency counts update, eliminating the need for external statistical scripting.