Value at Risk is a measure of the worst case loss that may occur over a specified holding period for a given probability. It is a measure used widely to assess the market risk inherent in a given investment or portfolio of investments.
Portfolio VaR – EXCEL Example is a detailed calculation sheet that demonstrates the calculation of VaR for a portfolio of six instruments comprising of 3 foreign exchange contracts (EUR, AUD and JPY) and three commodities (WTI, Gold and Silver). Before calculating the VaR for the portfolio, the metric is calculated for each instrument within the portfolio using the Simple Moving Average Variance Covariance Approach and the Historical Simulation Approach. It shows how a graph of Trailing Volatilities is constructed and the calculation of a crude estimate of the VaR number using the maximum volatility from this trailing volatility series.
The historical simulation method is also illustrated using EXCEL’s Data Analysis Tool for Histograms, applied to the daily return series, for each of the currencies as well as for the portfolio.
The derivation of Portfolio VaR for Variance Covariance Approach is done using the traditional variance and covariance matrix method as well as using a short cut by calculating a weighted average return series for the portfolio.
The Data Table functionality of EXCEL is used to calculate the 10-day holding VaR for varying odds (as given by the confidence level used).
Check out our finance course store for more courses on the Value at Risk concept. In particular:
Our video courses for sale:
Our free html course:
Our pdf course for sale: