Description

This Excel spreadsheet calculates Value-at-Risk (VaR) for a portfolio of up to 12 tickers, using both observed and simulated prices.

Here is a description of the worksheet tabs (from right-most moving left until we get to the final report):

We start with using Observed Market Prices:

• [X] = Worksheet where user will copy-paste time-series data (in the same format as the sample data already there).
  • Col A = Date column (with latest date on the top)
  • Col B to M = Columns of price data for up to 12 tickers
• [r_X] = Worksheet showing time-series of asset returns, observed in the market
  • The values are simply log-returns of the data on sheet [X]
• [STATS(r_X)] = Worksheet with a summary of statistics for each ticker’s time-series of returns.
  • Mean of Daily Returns, Scaled for 1-day, 10-days, 260-days, etc (User can select period length)
  • Variance of Daily Returns, Scaled for 1-day, 10-days, 260-days, etc (User can select period length)
  • Standard Deviation of Daily Returns, Scaled for 1-day, 10-days, 260-days, etc (User can select period length)
  • Value-at-Risk (VaR) – Parametric – 1-day, 99% confidence interval (User can adjust VaR parameters)
  • Value-at-Risk(VaR) – Historical – 1-day, 99% confidence interval (User can adjust VaR parameters)
• [SD(r_X)] = Worksheet showing the matrix of standard deviations of of annualized returns (ie. annual volatility)
  • The volatilities are on the diagonals of the matrix
  • It is put in matrix form, for matrix multiplication later
• [CORREL(r_X)] = Worksheet showing the matrix of correlation of daily returns across the portfolio of tickers
  • The correlations are 1 on the diagonals (as expected)
  • The other values range from -1 to +1
• [COVAR(r_X)] = Worksheet showing the matrix of covariance of daily returns across the portfolio of tickers
  • The diagonals are the annualized variances of the respective ticker’s daily returns
  • The annualized variance is simply the volatility squared
  • The other off-diagonal values are the covariances between the respective pair of tickers
  • The Cholesky Decomposition of the covariance matrix is also included here for later use
• [VAR_Param_Hist(X)] = Worksheet showing the summary of Portfolio VaR and contributions by each ticker
  • VaR Parametric
  • VaR Historical
  • Incremental VaR for each ticker in the portfolio (without rebalancing)
    • This is the absolute VaR contribution of each ticker to the overall Portfolio VaR
    • Sum of all tickers’ incremental VaR’s will be equal to the Portfolio VaR
  • Marginal VaR for each ticker in the portfolio (with rebalancing)
    • This is the marginal change in Portfolio VaR when a ticker is removed, and the Portfolio is rebalanced
    • If each ticker is removed, and the Portfolio rebalanced, the Portfolio VaR changes

Next, we move on to simulated asset prices:

• [Zc] = Worksheet showing the correlated standard normals which will be used to simulate the assets in the portfolio
  • We begin with simulated independent standard normals, Z
  • Then we multiply the Z’s by the upper diagonal of the Cholesky Decomposition of the covariance matrix
    • The Cholesky Decomposition of the covariance matrix is found on the worksheet [COVAR(r_X)] from earlier
    • This uses the built-in VBA function “Matrix_Decomposition_Cholesky()”
  • The results are simulated correlated standard normals, Zc
• [X(Zc)] = Worksheet where we simulate the asset prices
  • We assume a log-normal distribution of returns (used by the standard Black-Scholes framework)
  • We apply the correlated standard normals, Zc into the solution of the Black-Scholes Stochastic Differential Equation (SDE)
  • We start with the earliest observed price levels from the market data
  • We then simulate each day’s prices forward, using each ticker’s respective
    • mean of returns (to apply drift to the process)
    • standard deviation of returns (to apply diffusion to the process, where we scale the correlated standard normals, Zc)
    • correlated Zc time-series
• [r_X(Zc)] = Worksheet showing time-series of asset returns, simulated using our correlated standard normals
• [STATS(r_X(Zc))] = Worksheet with a summary of statistics for each ticker’s simulated time-series of returns.
• [SD(r_X(Zc))] = Worksheet showing the matrix of standard deviations of of simulated annualized returns (ie. annual volatility)
• [CORREL(r_X(Zc))] = Worksheet showing the matrix of correlation of simulated daily returns across the portfolio of tickers
• [COVAR(r_X(Zc))] = Worksheet showing the matrix of covariance of simulated daily returns across the portfolio of tickers
• [VAR_MCSim(X(Zc))] = Worksheet showing the summary of simulated Portfolio VaR and contributions by each ticker

Please contact us:
If you have requests to customize the spreadsheet, or request for a demonstration/walk-through via the internet.

Works on:

• Microsoft Excel 2016, 2013, 2007