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

In quantitative socio-economic analysis and financial risk modeling, aggregate mobility metrics provide a high-level summary of system churn. However, aggregate figures can mask deep asymmetries across individual strata. For example, a transition matrix might exhibit high upward mobility for middle tiers while simultaneously trapping lower tiers in chronic immobility, or exposing upper tiers to extreme downward risk. To uncover these stratum-level disparities, econometricians implement class-specific asymmetry models that dissect upward velocity, downward risk, and retention persistence individually for each tier.

Using Microsoft Excel’s dynamic array functions—including VSTACK, HSTACK, LAMBDA, MAKEARRAY, and BYROW—analysts can formulate a modular class-specific asymmetry engine that evaluates each tier dynamically without legacy VBA macros.

Mathematical Theory of Class-Specific Asymmetry

Consider an $n imes n$ stochastic transition matrix $P$ where $p_{i,j}$ denotes the probability of migrating from initial class $i$ to destination class $j$. For any given stratum $i$, we define class-specific directional metrics:

Upward Mobility Probability (U_i):
U_i = ∑_{j=i+1}^{n} p_{i,j}

Downward Mobility Probability (D_i):
D_i = ∑_{j=1}^{i-1} p_{i,j}

Diagonal Persistence (S_i):
S_i = p_{i,i}

Net Class Trajectory Balance (B_i):
B_i = U_i - D_i

Note that for the bottom class ($i=1$), $D_1 = 0$ (no lower class exists), and for the top class ($i=n$), $U_n = 0$. For intermediate classes, $B_i > 0$ indicates net upward propulsion, whereas $B_i < 0$ signals dominant downward vulnerability.

Structuring the Transition Matrix in Excel

Place your $5 imes 5$ transition probability grid in cells B4:F8, representing quintiles Q1 through Q5 on rows (origin) and columns (destination). Confirm that each row sums to $1.00$ ($100%$).

Authoring the Dynamic LAMBDA Formula

Open Excel’s Name Manager (Formulas > Name Manager) and create a new custom formula named CLASS_ASYMMETRY_MODEL:

=LAMBDA(matrix,
    LET(
        n, ROWS(matrix),
        class_rows, MAKEARRAY(n, 5, LAMBDA(r, c,
            LET(
                p_stay, INDEX(matrix, r, r),
                p_up, IF(r < n, SUM(MAKEARRAY(1, n - r, LAMBDA(x, y, INDEX(matrix, r, r + y)))), 0),
                p_down, IF(r > 1, SUM(MAKEARRAY(1, r - 1, LAMBDA(x, y, INDEX(matrix, r, y)))), 0),
                net_bal, p_up - p_down,
                label, "Class " & r,
                CHOOSE(c, label, p_stay, p_up, p_down, net_bal)
            )
        )),
        header, HSTACK("Stratum", "Stay (p_ii)", "Upward (U_i)", "Downward (D_i)", "Net Trajectory"),
        VSTACK(header, class_rows)
    )
)

Interpreting Stratum-Specific Results

Entering =CLASS_ASYMMETRY_MODEL(B4:F8) into cell B11 outputs a five-column dynamic report breakdown across all quintiles:

• Stratum Retention (Stay): Indicates what proportion of agents remain trapped or stabilized in their origin class.
• Upward Propulsion (U_i): Reveals the exact likelihood of climbing to higher strata.
• Downward Risk (D_i): Highlights vulnerability to economic demotion or credit downgrade.
• Net Trajectory: Quickly indicates whether a particular stratum enjoys favorable net mobility or faces systematic downward erosion.

By implementing class-specific asymmetry analysis with native Excel dynamic arrays, analysts gain granular visibility into inequality dynamics and credit migration risks with complete spreadsheet reactivity.