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

In quantitative economic mobility research, while basic transition trace indices evaluate diagonal persistence (individuals staying in identical quintiles), they fail to distinguish between individuals who move to an immediately adjacent quintile versus those who leap across multiple quintiles. Anthony Shorrocks proposed weighted geometric diagonal decay frameworks that penalize immobility using decaying power weights ($w_d = e^{-gamma d}$ or $1 / (1 + d)$) based on the distance $d = |i – j|$ traversed across category boundaries. Modern Microsoft Excel dynamic array functions—specifically VSTACK, LAMBDA, and LET—allow economic analysts to build dynamic diagonal decay mobility models cleanly without legacy macros.

Mathematical Foundations of Diagonal Decay Mobility

Given a $K imes K$ stochastic transition probability matrix $P$, let $d = |i – j|$ denote the distance between origin class $i$ and destination class $j$. The discrete Diagonal Decay Mobility Index $M_{decay}$ is defined as:

M_decay = 1 - (1 / K) * SUM( p_ij * exp(-gamma * |i - j|) )

Where:

• K denotes the total count of discrete classes or quintiles.
• gamma > 0 represents the decay sensitivity parameter. Higher $gamma$ values heavily discount distant transitions, placing maximum emphasis on local class boundary retention.
• If no mobility exists (the matrix is the identity matrix $I$), $d = 0$, $e^0 = 1$, yielding $M_{decay} = 0$.
• As individuals transition across multiple class boundaries, $e^{-gamma d} o 0$, causing $M_{decay} o 1$.

Implementing the Dynamic Array Formula in Excel

Using the LET function, we can construct the diagonal decay mobility calculation dynamically from any square transition matrix:

=LET(
    trans_matrix, A2:E6,
    gamma_val, 0.5,
    k, ROWS(trans_matrix),
    row_idx, SEQUENCE(k, 1, 1, 1),
    col_idx, SEQUENCE(1, k, 1, 1),
    dist_matrix, ABS(row_idx - col_idx),
    decay_weights, EXP(-gamma_val * dist_matrix),
    weighted_matrix, trans_matrix * decay_weights,
    row_persistence, MMULT(weighted_matrix, SEQUENCE(k, 1, 1, 0)),
    mean_persistence, AVERAGE(row_persistence),
    mobility_index, 1 - mean_persistence,
    mobility_index
)

This dynamic formula constructs coordinate distance grids, computes exponential decay weighting matrices, executes matrix-vector multiplication via MMULT, and outputs the exact diagonal decay mobility index instantly.

Encapsulating the Formula into a Reusable Custom LAMBDA

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

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

Building Comparative Transition Dashboards with VSTACK

We can combine multiple regional or sectoral transition matrices into a side-by-side comparative dashboard using VSTACK:

=LET(
    matrix_A, A2:E6,
    matrix_B, G2:K6,
    header, HSTACK("Jurisdiction Panel", "Decay Mobility (γ=0.5)", "Adjacent Turnover", "Mobility Rating"),
    row_1, HSTACK("Nordic Social Model", DECAY.MOBILITY(matrix_A), "High Long-Distance Leap", "Rapid Structural Mixing"),
    row_2, HSTACK("Segmented Labor Market", DECAY.MOBILITY(matrix_B), "Dominant Local Jumps", "Local Boundary Confinement"),
    VSTACK(header, row_1, row_2)
)

This dynamic array structure automatically updates whenever underlying transition probability estimates change, delivering instantaneous insight into distance-sensitive mobility and economic class fluidity across socioeconomic cohorts.