How to Use VSTACK and LAMBDA to Author Dynamic Shorrocks Asymmetric Decile Downside Relative Entropy Models in Microsoft Excel

In quantitative information theory and financial risk analytics, evaluating how far an empirical transition matrix deviates from a uniform or benchmark mobility distribution requires entropy measures. Relative entropy—specifically Kullback-Leibler (KL) divergence—quantifies the information lost when an observed transition distribution is approximated by a reference distribution. When evaluating $10 imes 10$ decile transition matrices, analysts partition relative entropy into upside information gain versus downside informational divergence to evaluate directional disequilibrium.

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

Mathematical Theory of Downside Relative Entropy

Let $P$ represent a $10 imes 10$ empirical transition matrix with row probabilities $p_{i,j}$, and let $Q$ represent a uniform benchmark transition matrix ($q_{i,j} = 1/n$). The relative entropy (KL divergence) of the transition matrix is defined as:

Total Relative Entropy:
D_KL(P || Q) = ∑_{i=1}^{n} ∑_{j=1}^{n} p_{i,j} * LN( p_{i,j} / q_{i,j} )

To evaluate directional asymmetry, we partition divergence into upside transitions ($j > i$) and downside transitions ($j < i$):

Upside Relative Entropy (RE_up):
RE_up = ∑_{i=1}^{n} ∑_{j=i+1}^{n} p_{i,j} * LN( p_{i,j} / q_{i,j} )

Downside Relative Entropy (RE_down):
RE_down = ∑_{i=1}^{n} ∑_{j=1}^{i-1} p_{i,j} * LN( p_{i,j} / q_{i,j} )

Net Entropic Asymmetry Ratio (EAR):
EAR = (RE_down - RE_up) / (RE_down + RE_up)

A positive $EAR > 0$ demonstrates that downside transition dynamics deviate more severely from uniform randomness than upside migrations, indicating concentrated structural risk in demotion pathways.

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 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_ENTROPY_MODEL:

=LAMBDA(matrix,
    LET(
        n, ROWS(matrix),
        q_ref, 1 / n,
        re_matrix, MAKEARRAY(n, n, LAMBDA(r, c,
            LET(
                p, INDEX(matrix, r, c),
                IF(p > 0, p * LN(p / q_ref), 0)
            )
        )),
        re_up, SUM(MAKEARRAY(n, n, LAMBDA(r, c, IF(c > r, INDEX(re_matrix, r, c), 0)))),
        re_down, SUM(MAKEARRAY(n, n, LAMBDA(r, c, IF(c < r, INDEX(re_matrix, r, c), 0)))),
        re_diag, SUM(MAKEARRAY(n, n, LAMBDA(r, c, IF(r = c, INDEX(re_matrix, r, c), 0)))),
        total_entropy, re_up + re_down + re_diag,
        ear_ratio, IF((re_down + re_up) = 0, 0, (re_down - re_up) / (re_down + re_up)),
        header, HSTACK("Relative Entropy Metric", "KL Divergence (Nats)"),
        r1, HSTACK("Upside Relative Entropy (RE_up)", re_up),
        r2, HSTACK("Downside Relative Entropy (RE_down)", re_down),
        r3, HSTACK("Diagonal Persistence Divergence", re_diag),
        r4, HSTACK("Total Matrix Relative Entropy", total_entropy),
        r5, HSTACK("Net Entropic Asymmetry Ratio (EAR)", ear_ratio),
        VSTACK(header, r1, r2, r3, r4, r5)
    )
)

Interpreting Relative Entropy in Risk Governance

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

• Upside Relative Entropy: Quantifies the informational divergence of upward promotion pathways from uniform distribution.
• Downside Relative Entropy: Measures informational concentration in downgrade and default migration paths.
• Net Entropic Asymmetry Ratio: Bounded between $-1.00$ and $+1.00$. Positive values reveal that downgrade channels exhibit strong non-random concentration.
• Total Matrix Relative Entropy: Measures 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.