How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Asymmetric Decile Downside Jensen-Shannon Divergence Models in Microsoft Excel

In quantitative information theory and financial risk analytics, measuring the informational distance between two transition probability distributions using Kullback-Leibler (KL) divergence has one notable mathematical limitation: KL divergence is asymmetric ($D_{KL}(P || Q) e D_{KL}(Q || P)$) and can reach infinity when probabilities approach zero. To achieve a symmetric, smooth, and bounded distance metric, econometricians utilize the Jensen-Shannon Divergence (JSD). JSD is always finite, bounded between $0$ and $1$ (using logarithm base 2), and whose square root satisfies the properties of a true mathematical metric.

Using Microsoft Excel’s dynamic array functions—specifically VSTACK, HSTACK, LAMBDA, MAKEARRAY, and LET—analysts can formulate a fully reactive decile downside Jensen-Shannon divergence engine that evaluates portfolio risk dynamics without legacy VBA macros.

Mathematical Theory of Downside Jensen-Shannon Divergence

Let $P$ represent a $10 imes 10$ empirical transition probability matrix and $Q$ represent a uniform benchmark transition matrix ($q_{i,j} = 1/n$). The mixture distribution $M$ is defined as:

M = 0.5 * (P + Q)

Total Jensen-Shannon Divergence:
JSD(P || Q) = 0.5 * D_KL(P || M) + 0.5 * D_KL(Q || M)

We partition JSD into upside transitions ($j > i$) and downside transitions ($j < i$):

Upside JSD (JSD_up):
JSD_up = 0.5 * ∑_{i=1}^{n} ∑_{j=i+1}^{n} ( p_{i,j} * LN(p_{i,j}/m_{i,j}) + q_{i,j} * LN(q_{i,j}/m_{i,j}) )

Downside JSD (JSD_down):
JSD_down = 0.5 * ∑_{i=1}^{n} ∑_{j=1}^{i-1} ( p_{i,j} * LN(p_{i,j}/m_{i,j}) + q_{i,j} * LN(q_{i,j}/m_{i,j}) )

Net JSD Asymmetry Ratio (JAR):
JAR = (JSD_down - JSD_up) / (JSD_down + JSD_up)

A positive $JAR > 0$ indicates that downside transitions diverge more sharply from uniform churn than upward paths, signaling downside volatility concentration.

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%$). Set your reference matrix probability $q = 0.10$ ($1/10$).

Authoring the Dynamic LAMBDA Formula

In Excel’s Name Manager (Formulas > Name Manager), create a new formula named DOWNSIDE_JSD_MODEL:

=LAMBDA(matrix,
    LET(
        n, ROWS(matrix),
        q_ref, 1 / n,
        jsd_matrix, MAKEARRAY(n, n, LAMBDA(r, c,
            LET(
                p, INDEX(matrix, r, c),
                q, q_ref,
                m, 0.5 * (p + q),
                t1, IF(p > 0, p * LN(p / m), 0),
                t2, IF(q > 0, q * LN(q / m), 0),
                0.5 * (t1 + t2)
            )
        )),
        jsd_up, SUM(MAKEARRAY(n, n, LAMBDA(r, c, IF(c > r, INDEX(jsd_matrix, r, c), 0)))),
        jsd_down, SUM(MAKEARRAY(n, n, LAMBDA(r, c, IF(c < r, INDEX(jsd_matrix, r, c), 0)))),
        jsd_diag, SUM(MAKEARRAY(n, n, LAMBDA(r, c, IF(r = c, INDEX(jsd_matrix, r, c), 0)))),
        total_jsd, jsd_up + jsd_down + jsd_diag,
        jar_ratio, IF((jsd_down + jsd_up) = 0, 0, (jsd_down - jsd_up) / (jsd_down + jsd_up)),
        header, HSTACK("Jensen-Shannon Metric", "JSD Value (Nats)"),
        r1, HSTACK("Upside JSD (JSD_up)", jsd_up),
        r2, HSTACK("Downside JSD (JSD_down)", jsd_down),
        r3, HSTACK("Diagonal Persistence JSD", jsd_diag),
        r4, HSTACK("Total Matrix JSD", total_jsd),
        r5, HSTACK("Net JSD Asymmetry Ratio (JAR)", jar_ratio),
        VSTACK(header, r1, r2, r3, r4, r5)
    )
)

Interpreting JSD in Risk Governance

Entering =DOWNSIDE_JSD_MODEL(B4:K13) into cell B16 outputs a structured analytical report:

• Upside JSD: Measures bounded informational divergence across upward migration channels.
• Downside JSD: Quantifies bounded informational distance along downward degradation pathways.
• Net JSD Asymmetry Ratio: Bounded strictly between $-1.00$ and $+1.00$. Positive values alert risk committees that downside transitions exhibit structured, non-uniform risk concentration.
• Total Matrix JSD: Summarizes the overall departure of the transition matrix from pure unconstrained churn.

By leveraging Excel’s functional dynamic array formulas, this risk engine provides instantaneous recalculation across stress test scenarios without requiring external code or macros.