Value at risk (VaR) is a commonly used risk measure in the finance industry. Monte Carlo simulation is one of the methods that can be used to determine VaR.

There are two things we need to specify when stating value at risk:

- The time horizon. This may be daily for some portfolios or a longer period for less liquid assets. The time horizon is accounted for in the portfolio model.
- The confidence level, 95% is common.

If the time horizon is one day and the confidence level is 95%, VaR is the most we would lose 95% of the time. One out of twenty days we would expect to lose more than VaR.

An important topic beyond the scope of this article is if VaR is exceeded in the 5% of cases, how much could we expect to lose. This is expected shortfall or conditional VaR. Refer to the article on expected shortfall for more information.

Before doing a VaR simulation, there needs to be a model of the portfolio returns. This model should also include any correlations among returns of each portfolio member. Developing a model with appropriate return probability distributions and correlations among assets is the most important and time consuming part of determining VaR.

A simple model consisting of five investments is shown below. It is assumed that the return of each investment is normally distributed. A Spearman rank correlation matrix is included to account for correlation among the investments. Four of the investments are positively correlated while investment C is negatively correlated with the other investments.

The output of the simulation is calculated in the Gain/Loss cell, B5.

The output of the simulation is shown below. To calculate value at risk for a 95% confidence level we look up the (100-95) = 5th percentile value.

VaR = 49,706

Note that we are using the sign convention where losses are positive. The 5th percentile is -49,706 (a loss), but we're stating it as a positive value.

Value at risk is the maximum loss 95% of the time. Also, 5% of the time we can expect to lose more than the value at risk.

The simulation was performed with Simulation Master. To learn more, visit the Simulation Master product page. Simulation Master has a VaR | Expected Shortfall tool that easily calculates these measures for any confidence level.

To learn more about the VaR | Expected Shortfall tool, refer to the tutorial that shows how it operates.

Excel is a registered trademark of Microsoft Corporation. Used with permission from Microsoft.