In quantitative optimal transport and financial risk analytics, measuring the distance between transition probability distributions using information-theoretic divergence (such as KL or Jensen-Shannon) ignores the underlying metric geometry of the strata: moving from Decile 1 to Decile 2 is penalized the same as moving from Decile 1 to Decile 10 if probabilities match. The Wasserstein metric (also known as the Earth Mover’s Distance) accounts directly for ground distance geometry, measuring the minimum work required to transform one probability distribution into another. When applied to $10 imes 10$ decile migration matrices, decomposing the Wasserstein metric directionally reveals the exact transport cost of downside degradation versus upward promotion.
Using Microsoft Excel’s dynamic array functions—specifically VSTACK, HSTACK, LAMBDA, MAKEARRAY, and LET—analysts can formulate a fully reactive decile downside Wasserstein metric engine that evaluates portfolio risk dynamics without legacy VBA macros.
Mathematical Theory of the 1-Wasserstein Distance
For one-dimensional discrete distributions on ordered deciles, the 1-Wasserstein distance ($W_1$) between cumulative distribution functions $F_P$ and $F_Q$ simplifies to the absolute difference of their cumulative distribution functions:
1-Wasserstein Distance Formula:
W_1(P, Q) = ∑_{k=1}^{n-1} | F_P(k) - F_Q(k) |To evaluate directional asymmetry across an empirical transition matrix $P$ against an identity benchmark $I$, we calculate the expected transport work required for upward transitions versus downward transitions:
Upward Transport Work (W_up):
W_up = (1 / n) * ∑_{i=1}^{n} ∑_{j=i+1}^{n} p_{i,j} * (j - i)
Downward Transport Work (W_down):
W_down = (1 / n) * ∑_{i=1}^{n} ∑_{j=1}^{i-1} p_{i,j} * (i - j)
Net Wasserstein Asymmetry Ratio (WAR):
WAR = (W_down - W_up) / (W_down + W_up)A positive $WAR > 0$ indicates that the mass transport work of downward economic displacement exceeds upward velocity, signaling net structural degradation across the deciles.
Organising the Transition Matrix in Excel
Structure your $10 imes 10$ decile transition probability grid across cells B4:K13, representing origin deciles D1 through D10 on the rows and destination deciles D1 through D10 on the columns. Verify that each row sums to $1.00$ ($100%$).
Authoring the Dynamic LAMBDA Formula
In Excel’s Name Manager (Formulas > Name Manager), create a new formula named DOWNSIDE_WASSERSTEIN_MODEL:
=LAMBDA(matrix,
LET(
n, ROWS(matrix),
up_weights, MAKEARRAY(n, n, LAMBDA(r, c, IF(c > r, c - r, 0))),
down_weights, MAKEARRAY(n, n, LAMBDA(r, c, IF(c < r, r - c, 0))),
w_up, SUM(matrix * up_weights) / n,
w_down, SUM(matrix * down_weights) / n,
total_w, w_up + w_down,
war_ratio, IF(total_w = 0, 0, (w_down - w_up) / total_w),
immobility, SUM(MAKEARRAY(n, n, LAMBDA(r, c, IF(r = c, INDEX(matrix, r, c), 0)))) / n,
header, HSTACK("Wasserstein Metric", "Transport Work (Deciles)"),
r1, HSTACK("Upward Transport Work (W_up)", w_up),
r2, HSTACK("Downward Transport Work (W_down)", w_down),
r3, HSTACK("Total Optimal Transport Work (W_1)", total_w),
r4, HSTACK("Net Wasserstein Asymmetry Ratio (WAR)", war_ratio),
r5, HSTACK("Mean Decile Retention Rate", immobility),
VSTACK(header, r1, r2, r3, r4, r5)
)
)Interpreting Wasserstein Metrics in Risk Governance
Entering =DOWNSIDE_WASSERSTEIN_MODEL(B4:K13) into cell B16 outputs a structured analytical report:
- Upward Transport Work: Measures the expected decile distance traveled during positive promotions.
- Downward Transport Work: Captures the expected decile displacement during downgrades, directly quantifying capital attrition risk.
- Net Wasserstein Asymmetry Ratio: Bounded between $-1.00$ and $+1.00$. Positive values alert risk committees that downward displacement work outweighs upward progression.
- Total Optimal Transport Work: Summarizes total system churn in intuitive decile step units.
By leveraging Excel’s functional dynamic array formulas, this risk engine provides instantaneous recalculation across stress test scenarios without requiring external code or macros.