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 for more courses on the Value at Risk concept. In particular:
- Calculating Value at Risk (VaR) – Package
- Calculating VaR – Includes case study
- Calculating VaR – EXCEL Example
- Calculating VaR for Futures and Options – EXCEL Example
- Collateral Valuation in Credit Risk Management
- Comparing Value at Risk – Model, Methods and Metrics – EXCEL
- Portfolio Risk Metrics – EXCEL Example
- Portfolio VaR – EXCEL Example
- Setting Counterparty Limits
- Setting Limits – EXCEL Example
- Setting Counterparty Limits – Package
- Sample Counterparty Limit Proposal
- Value at Risk Example for Fixed For Floating Interest Rate Swaps – EXCEL
- Value at Risk with Liquidity Premium
- Value at Risk using the Monte Carlo simulation with Historical Returns approach