Attribute VB_Name = "VaR_MonteCarlo"
' ===========================================================================
'  VaR_MonteCarlo · Strategic Investment Portfolio · Saranne Ndamba
'  Monte Carlo and historical VaR / Expected Shortfall for the workbook
'  Saranne_Ndamba_VaR_Calculator.xlsx
'
'  How to install: Developer > Visual Basic > File > Import File > this .bas
'  then save the workbook as .xlsm (Excel Macro-Enabled Workbook).
'  Run: Developer > Macros > MonteCarloVaR > Run
' ===========================================================================
Option Explicit

' Draws N one-period portfolio returns from a normal distribution with the
' daily mean and volatility measured on the Inputs sheet, scales them to the
' chosen horizon, then reads VaR and Expected Shortfall off the sorted losses.
Public Sub MonteCarloVaR()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Inputs")
    Dim capital As Double, conf As Double, horizon As Double
    Dim mu As Double, sigma As Double, n As Long, i As Long, k As Long
    capital = ws.Range("C5").Value
    conf = ws.Range("C6").Value
    horizon = ws.Range("C7").Value
    mu = ws.Range("C13").Value
    sigma = ws.Range("C14").Value
    n = ws.Range("C29").Value
    If n < 1000 Then MsgBox "Use at least 1,000 simulations.": Exit Sub

    Dim pnl() As Double: ReDim pnl(1 To n)
    Rnd -1: Randomize 2026                          ' reset, then fixed seed: same results every run
    Dim u As Double
    For i = 1 To n
        Do: u = Rnd(): Loop While u <= 0 Or u >= 1 ' avoid the infinite tails of NORM.S.INV
        pnl(i) = capital * (mu * horizon + sigma * Sqr(horizon) * _
                 Application.WorksheetFunction.Norm_S_Inv(u))
    Next i

    QuickSort pnl, 1, n                             ' worst outcome first
    k = Application.WorksheetFunction.Max(1, Int((1 - conf) * n))
    Dim tailSum As Double
    For i = 1 To k: tailSum = tailSum + pnl(i): Next i

    ws.Range("C30").Value = -pnl(k)                 ' VaR: loss at the (1 - confidence) quantile
    ws.Range("C31").Value = -tailSum / k            ' ES: average loss in the tail beyond VaR
    ws.Range("C32").Value = Now
    MsgBox "Monte Carlo done: " & Format(n, "#,##0") & " paths" & vbCrLf & _
           "VaR " & Format(conf, "0%") & ": EUR " & Format(-pnl(k), "#,##0") & vbCrLf & _
           "Expected Shortfall: EUR " & Format(-tailSum / k, "#,##0"), vbInformation, "VaR_MonteCarlo"
End Sub

' Cross-check of the historical VaR formula: sorts the real daily portfolio
' returns in VBA and compares the result with the PERCENTILE.INC cell.
Public Sub HistoricalVaRCheck()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Inputs")
    Dim rs As Worksheet: Set rs = ThisWorkbook.Worksheets("Returns")
    Dim lastRow As Long, n As Long, i As Long, k As Long, portCol As Long
    portCol = rs.Rows(4).Find(What:="Portfolio", LookAt:=xlWhole).Column
    lastRow = rs.Cells(rs.Rows.Count, portCol).End(xlUp).Row
    n = lastRow - 4
    Dim r() As Double: ReDim r(1 To n)
    For i = 1 To n: r(i) = rs.Cells(4 + i, portCol).Value: Next i
    QuickSort r, 1, n
    Dim conf As Double: conf = ws.Range("C6").Value
    k = Application.WorksheetFunction.Max(1, Int((1 - conf) * n))
    MsgBox "Sorted-array VaR: EUR " & Format(-r(k) * ws.Range("C5").Value, "#,##0") & vbCrLf & _
           "Formula VaR (C16): EUR " & Format(ws.Range("C16").Value, "#,##0") & vbCrLf & _
           "Small differences come from interpolation in PERCENTILE.INC.", vbInformation, "Historical VaR check"
End Sub

Private Sub QuickSort(a() As Double, ByVal lo As Long, ByVal hi As Long)
    Dim i As Long, j As Long, p As Double, t As Double
    i = lo: j = hi: p = a((lo + hi) \ 2)
    Do While i <= j
        Do While a(i) < p: i = i + 1: Loop
        Do While a(j) > p: j = j - 1: Loop
        If i <= j Then t = a(i): a(i) = a(j): a(j) = t: i = i + 1: j = j - 1
    Loop
    If lo < j Then QuickSort a, lo, j
    If i < hi Then QuickSort a, i, hi
End Sub
