## 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

## Reviews

There are no reviews yet.