Value at Risk (VaR) is an important tool to measure the market risk of any asset. Investors can use Value-at-Risk to estimate the worst expected loss from a share or a portfolio of shares in a given period of time under normal market conditions and a specified level of confidence, usually 95% or 99%. For example if a daily VaR is calculated as $1000 to a 95% confidence level, this means that there is only a 5% chance that the loss will be more than $1000 in the next day.

VaR is mostly used by investment banks and commercial banks to measure the market risk. There are various methods available to calculate the VaR, namely;

- Variance-covariance method;
- Historical Simulation Method;
- Monte-Carlo Method.

It is not very difficult for an individual investor to work out VaR using the first method, variance-covariance method. It will need a little bit of statistical knowledge but it is not hard stuff.

First we need historical returns of our asset/s and then we can calculate the standard deviation or volatility of each asset. It is easy to work out this in excel using STDEV function

The next step is to create a covariance matrix and this is also can easily be done in excel using CORREL function

Then we can work out the value of the 95% confidence level in excel using NORM.S.INV function.

Finally using MMULT function we can work out the portfolio VaR.

I have worked out the VaR for a sample portfolio of Australian shares and see below for the link to the excel workings. (Please note that the shares in the workings have been selected randomly for study purposes only. One must not take any investment decision based on the workings alone.)

An individual investor can use VaR to rank existing or potential shares and also measure the diversification effect of the portfolio.

The excel example is based on an investor invests $9,000 in 9 different companies from various sectors. The portfolio VaR is $159, i.e. the investor is 95% confident that he will not lose more than $159 from his portfolio of shares during the next day

The effect of diversification is clearly shown in the calculation as the sum of individual VaR comes to $ 259, i.e. $100 more than the portfolio VaR.

As illustrated in the calculation it’s not very hard to calculate VaR using excel and it is a great tool to measure the market risk of your portfolio. However an investor must do proper research on the company before making any investment decision.