VAR for Portfolio Management
VAR FOR PORTFOLIO MANAGEMENT USING @RISK TO CALCULATE PORTFOLIO PERFORMANCE
Measurements of risk are one of the most researched areas in finance. Various measures such as sensitivity and volatility have been suggested to capture the risks inherent in any portfolio of assets. However, these measures capture the variation of the market variable rather than the risk of losses. Risk analysis with @RISK produces results that are direct reflections of our uncertainty, as embodied in the distributions and/or parameters on which VAR and CVAR depend.
Modern portfolio theory, which combines consideration of both mean and variance of portfolio returns, provides the framework for measuring the performance of a portfolio. This mean-variance (M-V) framework – attributed to Markowitz – provided financial institutions and portfolio managers with a powerful tool that allowed them, for the first time, to utilise the concepts of risk and return in a combined paradigm. Despite the progressive acceptance and widespread use of the M-V framework and its numerous extensions, there has been a considerable debate among academics and practitioners on the validity of variance as a representative measure of risk. Deciding which types of risk to mitigate is the first dilemma of a financial institution and requires considerable attention.
Focusing on one particular risk category may lead to a hedged portfolio for a particular source of risk but may result in exposure to other sources of risk. This issue becomes more challenging when optimisation models are used. For instance, optimisation may result in minimisation of the risk included in the model but the solution may be sensitive to other sources of risks that were not considered and are better measured by another metric.
The incorporation of risk in the investment decision process should also reflect the benchmark relative to which a financial institution or individual assesses its portfolio performance. Portfolio performance can be measured relative to a benchmark index or an alternative investment opportunity. In this case, the risk measure is also a function of a target level of return. The standard deviation in this case would then reflect the deviation of the asset returns from the expected target return (for example FTSE100). In particular, the portfolio return approach is mostly suitable for maximum return strategies, whereas the target return framework is suitable for “index tracking” or “goal achievement’ strategies.
These two approaches lead to different asset-mix decisions and, therefore, choosing the appropriate risk framework becomes the second dilemma for financial institutions.
We discuss a number of alternative performance measures and calculate two of them – value-at-risk (VAR) and conditional-value-at-risk (CVAR) – using @RISK. @RISK is the Risk Analysis and Simulation add-in for Microsoft Excel¨. @RISK uses a technique known as Monte Carlo simulation to allow the user to take all possible outcomes into account. The performance measures can be categorised into symmetric and asymmetric measures.
The symmetric measures quantify risk in terms of probability-weighted dispersion of returns around a specific reference point, usually the expected value. Two of the most well-known and widely applied risk measures in this group are Markowitz’ variance or standard deviation and the expected or mean absolute deviation (MAD) of Kono and Yamasaki (1991). The asymmetric measures quantify risk according to results and probabilities below reference points, selected either subjectively or objectively. Such risk measures include the Expected value of loss from Domar and Musgrave (1944), Roy’s (1952) Safety first, the Semi-variance proposed by Markowitz (1959), Value-at-risk – VAR – (JP Morgan, 1993) and its extension Conditional VAR – CVAR – (Uryasev, 1998), and Fishburn’s a-t criterion (1977).
Performance measures can be calculated for the current portfolio of assets a decision-maker is evaluating, or they can be calculated for an efficient portfolio. An efficient portfolio is one that lies on the efficient frontier. The efficient frontier represents the combination of portfolios whose standard deviation is a minimum at each level of return. In this article we use @RISK to determine the efficient frontier and then calculate performance measures from this reference point.
Calculating the Efficient Portfolio We begin with three stocks, Microsoft, Intel and GE. Using ten years of historical data, from July 1993 through July 2003, and sampling 12 monthly growth rates from the ten year history, we generate 10,000 price realisations for July 2004, via Monte Carlo simulation using @RISK.
Next, given a starting set of weights for each stock – 35 per cent for Intel, 40 per cent for Microsoft and 25 per cent for GE – we calculate the portfolio return for each realisation.
After that we use the Excel Solver to find one portfolio that is on the efficient frontier. We use Solver to find the set of weights that minimises the standard deviation of the portfolio returns subject to having the mean of the returns being at least 37.5 per cent. Then we repeatedly perform this calculation for different values of required return in the range (5.0 per cent through 37.5 per cent) in discrete increments. The result can be seen in Figure 1 (left). The red dots represent the plot of numerous portfolio standard deviation-return combinations. The black line is the Solver-calculated efficient frontier – that combination of portfolio returnstandard deviations that is the lowest for each possible return value.
Calculation of Performance Measures With the efficient frontier calculated, we chose the portfolio with weights Intel = 0.351, Microsoft = 0.596 and GE = 0.054. The mean and standard deviation of this portfolio are 0.375 and 0.502, respectively. VAR and CVAR are defined below and calculated.
VAR
The rationale of any risk measurement system and summary statistic is to facilitate risk reporting and control decisions. A former chairman of JP Morgan, Dennis Weatherstone, demanded to know every day by 4:15pm the total market risk exposure of the firm. This prompted the launch of Risk-Metrics, and a program that provided the required information, thus popularising VAR. The §- VAR of a portfolio is generally defined as the possible maximum loss over a given holding period, assuming a confidence level §. If §-VAR is positive, the portfolio needs to be adjusted to be less risky. A negative §-VAR indicates that the portfolio can accommodate a more risky position.
The measure VAR is used by financial institutions to quantify current trading exposures especially since the Basle Committee accepts VAR as a risk measure. VAR also monitors customer credit exposure, sets exposure limits and enforces collateral requirements. In addition, VAR can be used as a summary risk measure across different financial instruments and business activities. VAR as a measure is an approximation of the maximum reasonable loss an institution can expect to realise from all its financial exposures.
The VAR can be calculated from the data described above. Using the portfolio with mean return = 0.375 and standard deviation = 0.502, the 95 per cent VAR is –0.29 – 0.375 = –0.665. That is, there is a 95 per cent chance that the portfolio return could decline 0.665 relative to the target return of 0.375.
CVAR
Embrecht et al (1997) and Artzner et al (1997, 1999) introduced CVAR – also known as excess loss, mean shortfall or expected shortfall – which is a meaningful extension of VAR as it focuses on the behaviour of the “tail”. The definition of CVAR at a specified level § (§ = 0.95 or 0.99) is the mathematical transcription of the concept “average loss in the worst (1–§) cases”. The 95 per cent CVAR for our portfolio is -0.388. That is, there is a 95 per cent chance that the average return could be as low as –0.388. This calculation can be done easily in @RISK using the filtering capability. For this conditional distribution, eliminate all observations except the lowest 5 per cent of the 10,000 realisations. The graph of this tail is shown in Figure 3 (see previous page).
A simulation model emulates a situation incorporating uncertainty in one or more quantities. In recent years, spreadsheet simulation modelling has become extremely popular, both in academic and corporate communities and especially in the finance sector. The reason for this popularity is due to simulation add-ins such as @RISK.
There are two primary advantages to using such an addin.
Firstly, the add-in gives easy access to many probability distributions that the end user might want to apply in the simulation models. Secondly, as demonstrated in this article, the add-in makes important calculations, such as VAR and CVAR, a straightforward exercise. The random number generator in @RISK plays a key role in many aspects of the stochastic analysis, including search and optimisation. If we rely only on built in Microsoft Excel functions, we are quite limited in the probability distributions used for simulation models. Risk analysis in @RISK is a quantitative method that seeks to determine the outcomes of a decision as a probability distribution and then measure the associated risk.
Computational Procedures
There are various specialist methods to construct the efficient frontier. In this paper, we illustrate building the efficient frontier using @RISK. A detailed discussion of this model is by Winston (2000, 2001).
We assume a universe of three stocks: Microsoft (MSFT), Intel (INTC) and GE (GE). Although the M-V model requires the computation of stock returns and covariance, we approach the estimation of these parameters in a different way. We wish to have the estimates of these parameters a year from now so that our portfolio problem is an annual decision-making model. We enter the last ten years (1 July 1993 to 1 July 2003) of monthly stock prices for MSFT, INTC and GE into the columns of our spreadsheet and compute their monthly returns. Then, we use simulation with @RISK to derive estimates for our model parameters. We sample 10,000 future estimates of each stock return from the rows of our historical data which ensures that each forecast stock return maintains the same correlations with the other stocks as observed in the past.
Mitigation Using Put Options on the Assets
The analysis can be extended to include mitigation of exposure if the portfolio VAR does not meet the risk appetite of the decision-maker. One method is to purchase put options for the underlying assets in the portfolio. This exercise is left to the reader.
Summary and Conclusions
In this article we have set out how a simulation approach [using @RISK] can be used as a simple but effective financial planning tool. The formal process of risk analysis consists of the following steps:
• Develop the analytical model to address the area of interest;
• Identify the risks;
• Quantify the risks;
• Perform the risk analysis using @RISK;
• Present the results;
• Make the decision based on the decision-maker’s risk appetite: mitigate, terminate or proceed.
This methodology can be equally applied to other areas: industrial project risks, environmental risks, public sector and business planning. In all these domains, risk-based planning has become an important and essential management tool.
GEORGE M. BIRBILLS AND PROFESSOR GAUTAM MITRA
PALISADE
Entry Filed under: Risk Management