Measuring socio-economic mobility and financial credit rating migrations requires mathematical models that accurately reflect the societal or institutional impact of transition distances. In classical Markov transition matrix analysis, standard mobility measures—such as the Prais-Bibby trace index—treat transitions between states linearly or count only diagonal persistence. However, in credit portfolio risk analysis and wealth distribution studies, large leaps across multiple classes (such as an investment-grade bond deteriorating directly to default, or an individual moving from the lowest to the highest wealth decile) carry non-linear, super-proportional economic weight. To capture this dynamic, econometricians employ convex distance mobility models where transition penalties accelerate non-linearly with distance.
With modern dynamic array functions in Microsoft Excel—including VSTACK, LAMBDA, MAKEARRAY, and LET—analysts can formulate sophisticated convex distance mobility engines directly in spreadsheet workbooks without legacy VBA macros.
Mathematical Theory of Convex Distance Weighting
Consider an $n imes n$ stochastic transition matrix $P$ where each cell $p_{i,j}$ represents the transition probability of an economic agent moving from stratum $i$ to stratum $j$ over a given period. A convex distance mobility index $M_C(P)$ assigns a distance weighting function $w(i, j) = |i – j|^gamma$, where the convexity parameter $gamma > 1$ penalises multi-state leaps:
Convex Mobility Index Formula:
M_C(P) = (1 / W_max) * ∑_{i=1}^{n} ∑_{j=1}^{n} p_{i,j} * |i - j|^γ
Where W_max normalises the index:
W_max = (n - 1)^γWhen $gamma = 1$, the index reduces to a standard linear distance mobility measure. When $gamma = 2$ (quadratic convexity), a 4-step jump carries 16 times the penalty weight of a 1-step transition, accurately reflecting the catastrophic volatility of extreme credit migrations.
Organising the Transition Matrix in Excel
Place your $5 imes 5$ transition probability grid in cells B4:F8, where rows represent origin quintiles (Q1 to Q5) and columns represent destination quintiles (Q1 to Q5). Ensure that each row sums to $1.00$ ($100%$). Set your convexity parameter $gamma$ in cell H4 (e.g., 2.0 for quadratic weighting).
Authoring the Dynamic LAMBDA Formula
In the Name Manager (Formulas > Name Manager) or using the Advanced Formula Environment, define a custom function named SHORROCKS_CONVEX_MODEL with the following structured formula:
=LAMBDA(matrix, gamma,
LET(
n, ROWS(matrix),
w_max, (n - 1)^gamma,
distance_weights, MAKEARRAY(n, n, LAMBDA(r, c, ABS(r - c)^gamma)),
weighted_matrix, matrix * distance_weights,
raw_leap_score, SUM(weighted_matrix),
normalised_index, raw_leap_score / (n * w_max),
diagonal_persistence, SUM(MAKEARRAY(n, n, LAMBDA(r, c, IF(r = c, INDEX(matrix, r, c), 0)))) / n,
header, HSTACK("Convex Metric", "Calculated Value"),
r1, HSTACK("Convexity Exponent (γ)", gamma),
r2, HSTACK("Raw Weighted Jump Score", raw_leap_score),
r3, HSTACK("Normalised Convex Mobility Index", normalised_index),
r4, HSTACK("Average Diagonal Persistence", diagonal_persistence),
VSTACK(header, r1, r2, r3, r4)
)
)Interpreting Convex Mobility Metrics
When entered into cell B11 as =SHORROCKS_CONVEX_MODEL(B4:F8, H4), the formula dynamically spills the four calculated metrics:
- Raw Weighted Jump Score: Summarises the aggregate non-linear displacement across all non-diagonal cells in the transition matrix.
- Normalised Convex Mobility Index: Yields a standardised score bounded between $0.00$ (complete immobility / identity matrix) and $1.00$ (complete anti-diagonal inversion). Higher values indicate aggressive class churning across extremes.
- Average Diagonal Persistence: Represents the mean probability of remaining in the origin quintile across all categories.
By leveraging Excel’s functional LAMBDA environment, this econometric model remains fully reactive to scenario adjustments, recalculating instantly when input probabilities or convexity parameters change.