Back to site

Excel: Credit Risk VBA Toolkit

Runs entirely in your browser — nothing is uploaded.

Expected/unexpected loss, Altman Z-Score, Merton distance-to-default, Basel risk weights and portfolio concentration — as native Excel formulas, plus a macro that builds a full expected-loss report from a table of exposures.

Install

  1. Download the .bas file below.
  2. In Excel, press Alt+F11 to open the VBA editor.
  3. File → Import File… and pick the downloaded file.
  4. Save the workbook as a macro-enabled file (.xlsm) to keep it.

Every Function below then works as a normal worksheet formula (e.g. =ExpectedLoss(B2,C2,D2)); every Sub runs from Developer → Macros.

What's inside

ExpectedLoss(PD, LGD, EAD)Standard EL = PD × LGD × EAD.
UnexpectedLoss(PD, LGD, EAD, Rho, Confidence)Basel single-factor (ASRF) unexpected loss.
AltmanZScore(...)Original 1968 Z-Score from 7 balance-sheet inputs.
AltmanZone(Z)Maps a Z-Score to Safe / Grey / Distress.
MertonDistanceToDefault(...)Iterative structural-model distance to default.
MertonImpliedPD(...)Market-implied probability of default from Merton.
BaselRiskWeight(Class, Rating)Basel standardized-approach risk weight.
RiskWeightedAssets(EAD, Class, Rating)EAD × the risk weight above.
HHIConcentration(Range)Herfindahl-Hirschman index over an exposure range.
Sub BuildExpectedLossReport()Prompts for an exposures table, writes a full EL breakdown.

Source

Attribute VB_Name = "CreditRiskToolkit"
Option Explicit

' ============================================================
' Credit Risk Analysis Toolkit — Rajat Mani Karn
'
' Install: In Excel, press Alt+F11 to open the VBA editor, then
' File -> Import File... and pick this .bas. Every Function below
' becomes a normal worksheet formula, e.g. =ExpectedLoss(B2,C2,D2).
' The Sub at the bottom runs from Developer -> Macros.
' ============================================================

' Expected Loss = PD x LGD x EAD
Public Function ExpectedLoss(ByVal PD As Double, ByVal LGD As Double, ByVal EAD As Double) As Double
    ExpectedLoss = PD * LGD * EAD
End Function

' Unexpected loss via the Basel single-factor (ASRF) conditional default rate.
' Rho is the asset correlation (Basel corporate default: roughly 0.12-0.24); Confidence as a decimal (0.999 for 99.9%).
Public Function UnexpectedLoss(ByVal PD As Double, ByVal LGD As Double, ByVal EAD As Double, _
                                ByVal Rho As Double, ByVal Confidence As Double) As Double
    Dim z As Double, wcdr As Double
    z = Application.WorksheetFunction.NormSInv(Confidence)
    wcdr = Application.WorksheetFunction.NormSDist( _
             (Application.WorksheetFunction.NormSInv(PD) + Sqr(Rho) * z) / Sqr(1 - Rho))
    UnexpectedLoss = (wcdr * LGD - PD * LGD) * EAD
End Function

' Altman Z-Score (original 1968 manufacturing model). All five inputs are absolute
' currency amounts except nothing needs pre-dividing — the ratios are built in here.
Public Function AltmanZScore(ByVal WorkingCapital As Double, ByVal TotalAssets As Double, _
                              ByVal RetainedEarnings As Double, ByVal EBIT As Double, _
                              ByVal MarketValueEquity As Double, ByVal TotalLiabilities As Double, _
                              ByVal Sales As Double) As Double
    Dim x1 As Double, x2 As Double, x3 As Double, x4 As Double, x5 As Double
    x1 = WorkingCapital / TotalAssets
    x2 = RetainedEarnings / TotalAssets
    x3 = EBIT / TotalAssets
    x4 = MarketValueEquity / TotalLiabilities
    x5 = Sales / TotalAssets
    AltmanZScore = 1.2 * x1 + 1.4 * x2 + 3.3 * x3 + 0.6 * x4 + 1# * x5
End Function

' Human-readable Altman zone for a Z-Score.
Public Function AltmanZone(ByVal Z As Double) As String
    If Z > 2.99 Then
        AltmanZone = "Safe"
    ElseIf Z >= 1.81 Then
        AltmanZone = "Grey"
    Else
        AltmanZone = "Distress"
    End If
End Function

' Merton / KMV-style distance-to-default, solved by the standard iterative fixed-point
' method: equity is a call option on firm assets, so today's equity value/volatility
' imply an (unobservable) asset value and asset volatility. EquityVol and RiskFreeRate
' are decimals (0.35 = 35%); Horizon in years.
Public Function MertonDistanceToDefault(ByVal Equity As Double, ByVal EquityVol As Double, _
                                         ByVal Debt As Double, ByVal RiskFreeRate As Double, _
                                         ByVal Horizon As Double) As Double
    Dim V As Double, sigV As Double, d1 As Double, d2 As Double
    Dim vNext As Double, sigVNext As Double, i As Integer

    V = Equity + Debt
    sigV = EquityVol
    For i = 1 To 100
        d1 = (Log(V / Debt) + (RiskFreeRate + sigV ^ 2 / 2) * Horizon) / (sigV * Sqr(Horizon))
        d2 = d1 - sigV * Sqr(Horizon)
        vNext = (Equity + Debt * Exp(-RiskFreeRate * Horizon) * Application.WorksheetFunction.NormSDist(d2)) _
                / Application.WorksheetFunction.NormSDist(d1)
        sigVNext = (EquityVol * Equity) / (Application.WorksheetFunction.NormSDist(d1) * vNext)
        If sigVNext <= 0 Then Exit For
        V = vNext
        sigV = sigVNext
    Next i

    MertonDistanceToDefault = (Log(V / Debt) + (RiskFreeRate - sigV ^ 2 / 2) * Horizon) / (sigV * Sqr(Horizon))
End Function

' Market-implied 1-period probability of default from the Merton distance-to-default above.
Public Function MertonImpliedPD(ByVal Equity As Double, ByVal EquityVol As Double, _
                                 ByVal Debt As Double, ByVal RiskFreeRate As Double, _
                                 ByVal Horizon As Double) As Double
    Dim dd As Double
    dd = MertonDistanceToDefault(Equity, EquityVol, Debt, RiskFreeRate, Horizon)
    MertonImpliedPD = Application.WorksheetFunction.NormSDist(-dd)
End Function

' Basel standardized-approach risk-weight lookup — a commonly-cited simplified reference
' table; check your jurisdiction's exact regulatory table for compliance use.
' ExposureClass: "Sovereign", "Bank", "Corporate". Rating: "AAA/AA","A","BBB","BB","B","Below B","Unrated".
Public Function BaselRiskWeight(ByVal ExposureClass As String, ByVal Rating As String) As Double
    Dim cls As String, rtg As String
    cls = LCase(ExposureClass)
    rtg = LCase(Rating)

    Select Case cls
        Case "sovereign"
            Select Case rtg
                Case "aaa/aa": BaselRiskWeight = 0
                Case "a": BaselRiskWeight = 0.2
                Case "bbb": BaselRiskWeight = 0.5
                Case "bb", "b": BaselRiskWeight = 1#
                Case "below b": BaselRiskWeight = 1.5
                Case Else: BaselRiskWeight = 1#
            End Select
        Case "bank"
            Select Case rtg
                Case "aaa/aa": BaselRiskWeight = 0.2
                Case "a": BaselRiskWeight = 0.3
                Case "bbb", "bb": BaselRiskWeight = 0.5
                Case "b": BaselRiskWeight = 1#
                Case "below b": BaselRiskWeight = 1.5
                Case Else: BaselRiskWeight = 0.5
            End Select
        Case Else ' corporate
            Select Case rtg
                Case "aaa/aa": BaselRiskWeight = 0.2
                Case "a": BaselRiskWeight = 0.5
                Case "bbb": BaselRiskWeight = 0.75
                Case "bb", "b": BaselRiskWeight = 1#
                Case "below b": BaselRiskWeight = 1.5
                Case Else: BaselRiskWeight = 1#
            End Select
    End Select
End Function

Public Function RiskWeightedAssets(ByVal EAD As Double, ByVal ExposureClass As String, ByVal Rating As String) As Double
    RiskWeightedAssets = EAD * BaselRiskWeight(ExposureClass, Rating)
End Function

' Herfindahl-Hirschman concentration index (0-10,000) over a range of exposure amounts.
Public Function HHIConcentration(ByVal ExposureRange As Range) As Double
    Dim total As Double, c As Range, hhi As Double, share As Double
    total = Application.WorksheetFunction.Sum(ExposureRange)
    If total = 0 Then
        HHIConcentration = 0
        Exit Function
    End If
    hhi = 0
    For Each c In ExposureRange
        If IsNumeric(c.Value) Then
            share = c.Value / total
            hhi = hhi + share ^ 2
        End If
    Next c
    HHIConcentration = hhi * 10000
End Function

' Prompts for an exposures table (Name, PD%, LGD%, EAD with headers) and a target cell,
' then writes a full per-exposure Expected Loss breakdown with a totals row.
Public Sub BuildExpectedLossReport()
    Dim src As Range, out As Range, r As Long, n As Long
    Dim totalEL As Double, totalEAD As Double
    Dim pd As Double, lgd As Double, ead As Double, el As Double

    On Error Resume Next
    Set src = Application.InputBox("Select the exposures table (Name, PD%, LGD%, EAD) INCLUDING headers:", _
                                    "Expected Loss Report", Type:=8)
    On Error GoTo 0
    If src Is Nothing Then Exit Sub

    Set out = Application.InputBox("Select the top-left cell to write the report to:", "Expected Loss Report", Type:=8)
    If out Is Nothing Then Exit Sub

    out.Value = "Exposure"
    out.Offset(0, 1).Value = "PD %"
    out.Offset(0, 2).Value = "LGD %"
    out.Offset(0, 3).Value = "EAD"
    out.Offset(0, 4).Value = "Expected Loss"
    out.Resize(1, 5).Font.Bold = True

    n = src.Rows.Count
    For r = 2 To n
        pd = src.Cells(r, 2).Value / 100
        lgd = src.Cells(r, 3).Value / 100
        ead = src.Cells(r, 4).Value
        el = ExpectedLoss(pd, lgd, ead)
        totalEL = totalEL + el
        totalEAD = totalEAD + ead

        out.Offset(r - 1, 0).Value = src.Cells(r, 1).Value
        out.Offset(r - 1, 1).Value = src.Cells(r, 2).Value
        out.Offset(r - 1, 2).Value = src.Cells(r, 3).Value
        out.Offset(r - 1, 3).Value = ead
        out.Offset(r - 1, 4).Value = el
    Next r

    out.Offset(n, 0).Value = "TOTAL"
    out.Offset(n, 0).Font.Bold = True
    out.Offset(n, 3).Value = totalEAD
    out.Offset(n, 4).Value = totalEL
    out.Offset(n, 4).Font.Bold = True

    MsgBox "Expected loss report written. Portfolio EL = " & Format(totalEL, "#,##0") & _
           " (" & Format(totalEL / totalEAD, "0.00%") & " of EAD)", vbInformation
End Sub