Excel: Market Risk VBA Toolkit

Runs entirely in your browser — nothing is uploaded.

Parametric and historical VaR/CVaR, Sharpe ratio, drawdown, Black-Scholes pricing and Greeks, and bond duration — as native Excel formulas, plus a macro that builds a full VaR report from a return series.

Install

Download the .bas file below.
In Excel, press Alt+F11 to open the VBA editor.
File → Import File… and pick the downloaded file.
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

ParametricVaR(Value, Vol%, Conf%, Days)Variance-covariance VaR.

HistoricalVaR(Returns, Value, Conf%)Empirical VaR from a return series.

HistoricalCVaR(Returns, Value, Conf%)Expected shortfall beyond the VaR cutoff.

SharpeRatio(Returns, RiskFree%, Periods)Annualized Sharpe ratio.

MaxDrawdown(Prices)Largest peak-to-trough decline in a price series.

BlackScholes(...)European call/put price.

BlackScholesDelta / Gamma / Vega(...)Option Greeks.

ModifiedDuration(...)Modified duration of an annual-coupon bond.

Sub BuildVaRReport()Prompts for a return series, writes a historical + parametric VaR/CVaR summary.

Source

Attribute VB_Name = "MarketRiskToolkit"
Option Explicit

' ============================================================
' Market Risk Analysis Toolkit — Rajat Mani Karn
' VaR (parametric + historical), option Greeks, bond duration.
'
' Install: Alt+F11 -> File -> Import File... and pick this .bas.
' ============================================================

' Parametric (variance-covariance) Value at Risk.
Public Function ParametricVaR(ByVal PortfolioValue As Double, ByVal AnnualVolPct As Double, _
                               ByVal ConfidencePct As Double, ByVal HorizonDays As Double) As Double
    Dim z As Double, dailyVol As Double
    z = Application.WorksheetFunction.NormSInv(ConfidencePct / 100)
    dailyVol = (AnnualVolPct / 100) / Sqr(252)
    ParametricVaR = Abs(z) * dailyVol * Sqr(HorizonDays) * PortfolioValue
End Function

' Historical simulation VaR from a range of periodic returns (decimals, e.g. -0.012 for -1.2%).
Public Function HistoricalVaR(ByVal ReturnsRange As Range, ByVal PortfolioValue As Double, ByVal ConfidencePct As Double) As Double
    HistoricalVaR = -Application.WorksheetFunction.Percentile(ReturnsRange, 1 - ConfidencePct / 100) * PortfolioValue
End Function

' Historical Expected Shortfall (CVaR): average loss among returns at/beyond the VaR cutoff.
Public Function HistoricalCVaR(ByVal ReturnsRange As Range, ByVal PortfolioValue As Double, ByVal ConfidencePct As Double) As Double
    Dim cutoff As Double, total As Double, cnt As Long, c As Range
    cutoff = Application.WorksheetFunction.Percentile(ReturnsRange, 1 - ConfidencePct / 100)
    total = 0: cnt = 0
    For Each c In ReturnsRange
        If IsNumeric(c.Value) Then
            If c.Value <= cutoff Then
                total = total + c.Value
                cnt = cnt + 1
            End If
        End If
    Next c
    If cnt = 0 Then
        HistoricalCVaR = 0
    Else
        HistoricalCVaR = -(total / cnt) * PortfolioValue
    End If
End Function

' Annualized Sharpe ratio from a range of periodic returns.
Public Function SharpeRatio(ByVal ReturnsRange As Range, ByVal AnnualRiskFreeRatePct As Double, ByVal PeriodsPerYear As Double) As Double
    Dim meanRet As Double, sdRet As Double, rf As Double
    meanRet = Application.WorksheetFunction.Average(ReturnsRange) * PeriodsPerYear
    sdRet = Application.WorksheetFunction.StDev(ReturnsRange) * Sqr(PeriodsPerYear)
    rf = AnnualRiskFreeRatePct / 100
    SharpeRatio = (meanRet - rf) / sdRet
End Function

' Maximum drawdown (positive decimal, e.g. 0.24 = -24%) over a chronological range of price levels.
Public Function MaxDrawdown(ByVal PricesRange As Range) As Double
    Dim peak As Double, dd As Double, maxDD As Double, c As Range
    peak = -1
    maxDD = 0
    For Each c In PricesRange
        If IsNumeric(c.Value) Then
            If c.Value > peak Or peak = -1 Then peak = c.Value
            dd = (peak - c.Value) / peak
            If dd > maxDD Then maxDD = dd
        End If
    Next c
    MaxDrawdown = maxDD
End Function

' Black-Scholes European option price. OptionType: "C" (call) or "P" (put).
Public Function BlackScholes(ByVal Spot As Double, ByVal Strike As Double, ByVal RiskFreeRate As Double, _
                              ByVal Volatility As Double, ByVal TimeToExpiry As Double, ByVal OptionType As String) As Double
    Dim d1 As Double, d2 As Double
    d1 = (Log(Spot / Strike) + (RiskFreeRate + Volatility ^ 2 / 2) * TimeToExpiry) / (Volatility * Sqr(TimeToExpiry))
    d2 = d1 - Volatility * Sqr(TimeToExpiry)

    If UCase(Left(OptionType, 1)) = "C" Then
        BlackScholes = Spot * Application.WorksheetFunction.NormSDist(d1) - _
                       Strike * Exp(-RiskFreeRate * TimeToExpiry) * Application.WorksheetFunction.NormSDist(d2)
    Else
        BlackScholes = Strike * Exp(-RiskFreeRate * TimeToExpiry) * Application.WorksheetFunction.NormSDist(-d2) - _
                       Spot * Application.WorksheetFunction.NormSDist(-d1)
    End If
End Function

Public Function BlackScholesDelta(ByVal Spot As Double, ByVal Strike As Double, ByVal RiskFreeRate As Double, _
                                   ByVal Volatility As Double, ByVal TimeToExpiry As Double, ByVal OptionType As String) As Double
    Dim d1 As Double
    d1 = (Log(Spot / Strike) + (RiskFreeRate + Volatility ^ 2 / 2) * TimeToExpiry) / (Volatility * Sqr(TimeToExpiry))
    If UCase(Left(OptionType, 1)) = "C" Then
        BlackScholesDelta = Application.WorksheetFunction.NormSDist(d1)
    Else
        BlackScholesDelta = Application.WorksheetFunction.NormSDist(d1) - 1
    End If
End Function

Public Function BlackScholesGamma(ByVal Spot As Double, ByVal Strike As Double, ByVal RiskFreeRate As Double, _
                                   ByVal Volatility As Double, ByVal TimeToExpiry As Double) As Double
    Dim d1 As Double, nprime As Double
    d1 = (Log(Spot / Strike) + (RiskFreeRate + Volatility ^ 2 / 2) * TimeToExpiry) / (Volatility * Sqr(TimeToExpiry))
    nprime = Exp(-d1 ^ 2 / 2) / Sqr(2 * Application.WorksheetFunction.Pi())
    BlackScholesGamma = nprime / (Spot * Volatility * Sqr(TimeToExpiry))
End Function

' Vega per 1 percentage-point move in volatility.
Public Function BlackScholesVega(ByVal Spot As Double, ByVal Strike As Double, ByVal RiskFreeRate As Double, _
                                  ByVal Volatility As Double, ByVal TimeToExpiry As Double) As Double
    Dim d1 As Double, nprime As Double
    d1 = (Log(Spot / Strike) + (RiskFreeRate + Volatility ^ 2 / 2) * TimeToExpiry) / (Volatility * Sqr(TimeToExpiry))
    nprime = Exp(-d1 ^ 2 / 2) / Sqr(2 * Application.WorksheetFunction.Pi())
    BlackScholesVega = Spot * nprime * Sqr(TimeToExpiry) / 100
End Function

' Modified duration of an annual-coupon fixed-rate bond.
Public Function ModifiedDuration(ByVal FaceValue As Double, ByVal CouponRatePct As Double, _
                                  ByVal YieldPct As Double, ByVal YearsToMaturity As Long) As Double
    Dim coupon As Double, y As Double, t As Long, pv As Double, weightedPV As Double, price As Double, cf As Double
    coupon = FaceValue * CouponRatePct / 100
    y = YieldPct / 100
    price = 0: weightedPV = 0
    For t = 1 To YearsToMaturity
        cf = coupon
        If t = YearsToMaturity Then cf = cf + FaceValue
        pv = cf / (1 + y) ^ t
        price = price + pv
        weightedPV = weightedPV + t * pv
    Next t
    ModifiedDuration = (weightedPV / price) / (1 + y)
End Function

' Prompts for a range of periodic returns and a portfolio value, then writes a
' historical + parametric VaR/CVaR summary block starting at the chosen cell.
Public Sub BuildVaRReport()
    Dim rng As Range, out As Range, pv As Double, annualVol As Double

    On Error Resume Next
    Set rng = Application.InputBox("Select the range of periodic returns (decimals):", "VaR Report", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub

    pv = Application.InputBox("Portfolio value:", "VaR Report", 1000000, Type:=1)
    Set out = Application.InputBox("Select the top-left cell to write the report to:", "VaR Report", Type:=8)
    If out Is Nothing Then Exit Sub

    annualVol = Application.WorksheetFunction.StDev(rng) * Sqr(252) * 100

    out.Value = "Metric"
    out.Offset(0, 1).Value = "Value"
    out.Offset(1, 0).Value = "Historical VaR 95% (1d)": out.Offset(1, 1).Value = HistoricalVaR(rng, pv, 95)
    out.Offset(2, 0).Value = "Historical VaR 99% (1d)": out.Offset(2, 1).Value = HistoricalVaR(rng, pv, 99)
    out.Offset(3, 0).Value = "Historical CVaR 95% (1d)": out.Offset(3, 1).Value = HistoricalCVaR(rng, pv, 95)
    out.Offset(4, 0).Value = "Parametric VaR 95% (1d)": out.Offset(4, 1).Value = ParametricVaR(pv, annualVol, 95, 1)
    out.Offset(5, 0).Value = "Parametric VaR 99% (1d)": out.Offset(5, 1).Value = ParametricVaR(pv, annualVol, 99, 1)
    out.Offset(6, 0).Value = "Annualized volatility %": out.Offset(6, 1).Value = annualVol

    out.Resize(1, 2).Font.Bold = True
    MsgBox "VaR report written.", vbInformation
End Sub