How to Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation Introduction to Value at Risk Performance evaluation measures such as the Sharpe ratio, Sortino ratio focused on lower moments assume normality of the distribution of asset returns. Value at Risk (VaR) is an attempt to characterise the fatness of the tail of the asset returns, or the kurtosis. Essentially, VaR is the V in the statement: "We are x percent certain that we will not lose more than V dollars in the next N days." In other words, VaR would be the response to the question "What's the loss at this level of probability?" . The Multivariate VaR Monte Carlo Simulation There are several methods of calculating VaR: historical simulation, model-building and Monte Carlo simulation. Here we focus on the latter. The calculation steps are as follows. 1. Calculate the current portolio value 2. Sample once from the multivariate normal distribution for each of the assets in the portfolio using the Cholesky Decomposition of the correlation matrix to create correlated normal random variables 3. Calculate the asset prices at the end of the time period 4. Calculate the portfolio value at the end of the time period 5. Subtract step 1 result from step 4 result to get the change in value of the portfolio, (the 'portfolio delta') over the time period 6. Repeat steps 1 to 5 to build up a distribution of the change in value of the portfolio 1 Nyasha Madavo, VBA Developer.net 7. Sort the portfolio deltas from lowest to highest using the Quicksort algorithm 8. Calculate the percentile required (1 - confidence level) 9. VaR is given by portfolio delta(i) where all the portfolio deltas from N simulations are in a vector, and i = percentile * N 10. VaRs can be extracted as an array for difference confidence intervals if required In the following simplified example, we assume a portfolio of assets with prices that follow the Black Scholes model assumptions. Multivariate VaR VBA Code Function VaR_Sim(Runs As Long, S_t_range As Object, K_range As Object, r_scalar As Double, _ q_range As Object, sigma_range As Object, t_scalar As Double, Matrix_range As Object, Confidence As Double) As Double Application.Calculation = xlCalculationManual Application.Volatile (False) Application.ScreenUpdating = False Dim S_Count As Long, i As Long Dim S() As Double, k() As Double, r() As Double, q() As Double, sigma() As Double, t() As Double, a() As Double Dim Vector_Limits As Long Dim L_Lower As Variant, epsilon() As Variant Dim j As Long Dim Sum_S As Double, Val_at_Risk(i) As Double Dim RandVars How To Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation How to Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation '------------ ASSUME ALL VECTORS HAVE SAME NUMBER OF CELLS AS # OF ASSET PRICES, DEFINE LIMITS OF ALL VECTORS THEN REDECLARE -----Vector_Limits = S_t_range.Rows.Count * S_t_range.Columns.Count ReDim Preserve S(1 To Vector_Limits) ReDim Preserve k(1 To Vector_Limits) ReDim Preserve r(1 To Vector_Limits) ReDim Preserve q(1 To Vector_Limits) ReDim Preserve sigma(1 To Vector_Limits) ReDim Preserve t(1 To Vector_Limits) ReDim Preserve a(1 To Vector_Limits) Dim N As Integer Dim Sum_S0 As Double, Delta() As Double, Percentile As Double RandVars = WorksheetFunction.Transpose(RandVars) epsilon = Application.WorksheetFunction.MMult(L_Lower, RandVars) For i = 1 To Vector_Limits S(i) = S_t_range(i) SumS0 = SumS0 + S(i) q(i) = q_range(i) k(i) = K_range(i) r(i) = r_scalar t(i) = t_scalar sigma(i) = sigma_range(i, j) a(i) = r(i) - q(i) - 0.5 * sigma(i) S(i) = S(i) * Exp(a(i) + sigma(i) * Sqr(t(i)) * epsilon(i, 1)) Sum_S = Sum_S + S(i) '----------- DECOMPOSE MATRIX -------------------N = Matrix_range.Columns.Count ReDim L_Lower(1 To N, 1 To N) For i = 1 To N For j = 1 To N L_Lower(i, j) = Matrix_range(i, j).Value Next j Next i Next i Delta(j) = Sum_S - Sum_S0 L_Lower = cholesky(Matrix_range) Next j ReDim Preserve Val_at_Risk(1 To Runs) ReDim Preserve Delta(1 To Runs) 'Sort the Deltas and select the Delta corresponding to the percentile Delta = quicksort(Delta) Percentile = 1 - Confidence Val_at_Risk = Delta(Round(Percentile * Runs, 0)) VaR_Sim = Val_at_Risk '----------- GET PARAMETERS AND FILL VECTORS -------------------For j = 1 To Runs Sum_S = 0 RandVars = Fill_epsilon(Vector_Limits) 2 Nyasha Madavo, VBA Developer.net Application.Calculation = xlCalculationAutomatic How To Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation How to Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation End Function Application.Volatile (True) Application.ScreenUpdating = True Function Fill_epsilon(ByVal Vector_Limit As Long) As Variant Next Steps Dim i As Long Dim epsilon() As Double ReDim Preserve epsilon(1 To Vector_Limit) For i = 1 To Vector_Limit epsilon(i) = WorksheetFunction.NormSInv(Rnd) Next i Fill_epsilon = epsilon End Function Here we've essentially assumed homogeneity of the assets and assumed that stock prices follow the assumptions of the Black Scholes model. We've assumed lognormality assumptions as well as the remaining Black Scholes assumptions such as interest rate and volatility remaining constant, which would need to be re-appraised. How VBA Developer.net Can Save You Time and money You can get complete Excel apps from VBA Developer.net containing the code in this document, customisation, VBA development of any Excel, Access and Outlook apps, as well as C# and C++ add-ins and technical documentation. Visit VBA Developer.net Examples of VBA documents from VBA Developer.net How to build a Black Scholes VBA Option Pricer How to build a Black Scholes C# Option Pricer How to Code a Multivariate Value at Risk (VaR) VBA Monte 3 Nyasha Madavo, VBA Developer.net How To Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation How to Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation Carlo Simulation How to build a Black Scholes VBA Option Pricer for FX Options How to build a Black Scholes VBA Option Pricer for Equity Options How To Code the Newton-Raphson Method in Excel VBA How to Model Volatility Smiles, Volatility Term Structure and the Volatility Surface in Excel VBA How To Write Use Cases for a Portfolio Reporting VBA Tool How to build a Black Scholes VBA Option Pricer using Monte Carlo Simulation How to build a Black Scholes VBA Option Pricer for Binary Options How To Write a User Interface Model For a Portfolio Reporting VBA Tool How To Create a Semantic Object Model For a Portfolio Reporting VBA Tool How to build a Black Scholes VBA Option Pricer for Equity Barrier Options How To Normalise a Database For VBA Apps How to build a Black Scholes VBA Option Pricer for Exotic Asian Options How To Create a Database using SQL Scripts for a Portfolio Reporting VBA App How to build a Black Scholes VBA Option Pricer for Exotic Lookback Options How to Write Stored Procedures in SQL/Access/VBA for a Portfolio Reporting VBA App How to build an Equity Option Pricer using the Binomial Tree in Excel VBA How to Use Cursors in SQL for a Portfolio Reporting VBA Tool How to code a Choleskey Decomposition in VBA (Numerical Methods for Excel) How to Move Data from Access to Excel with SQL for a Portfolio Reporting VBA App 3 ways to sort in VBA 4 Nyasha Madavo, VBA Developer.net How To Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation How to Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation Portfolio Reporting VBA Tool: Inserting Data into SQL/Access Databases from Excel Portfolio Reporting VBA Tool: Connecting Excel with SQL & Access Databases How To Design Classes For an Option Pricer in VBA: UML Concepts How To Design Classes for Object Orientated VBA Programming 5 Nyasha Madavo, VBA Developer.net How To Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation