Anybody can do Value at Risk: A Teaching Study using Parametric Computation and Monte Carlo Simulation

Abstract

The three main Value at Risk (VaR) methodologies are historical, parametric and Monte Carlo Simulation. Cheung & Powell (2012), using a step-by-step teaching study, showed how a nonparametric historical VaR model could be constructed using Excel, thus benefitting teachers and researchers by providing them with a readily useable teaching study and an inexpensive and flexible VaR modelling option. This article extends that work by demonstrating how parametric and Monte Carlo Simulation VaR models can also be constructed in Excel, thus providing a total Excel modelling package encompassing all three VaR methods. [PUBLICATION ABSTRACT]

Headnote

Abstract

The three main Value at Risk (VaR) methodologies are historical, parametric and Monte Carlo Simulation. Cheung & Powell (2012), using a step-by-step teaching study, showed how a nonparametric historical VaR model could be constructed using Excel, thus benefitting teachers and researchers by providing them with a readily useable teaching study and an inexpensive and flexible VaR modelling option. This article extends that work by demonstrating how parametric and Monte Carlo Simulation VaR models can also be constructed in Excel, thus providing a total Excel modelling package encompassing all three VaR methods.

Keywords: Value at risk, Parametric value at risk, Monte Carlo simulation, Financial modelling, Pseudo-random number generator.

JEL Classification: G17.

(ProQuest: ... denotes formulae omitted.)

Introduction

Cheung and Powell (2012) showed the procedures of doing one-step ahead Value at Risk (VaR) in Microsoft Excel using the non-parametric historical method. This paper extends this prior research by calculating VaR using parametric and Monte Carlo simulation methods. In the parametric method, the asset returns are assumed to follow a known probability distribution whilst the Monte Carlo method assumes that asset returns are driven by a known stochastic process.

The major attraction of using a nonparametric approach, as argued by Cheung and Powell (2012), is avoiding the misspecification of probability density functions of risk factors in an era of frequent financial disturbance. If trading conditions are deemed to be normal then the VaR calculation can be simplified considerably if the distributions of the risk factors can be assumed to belong to certain parametric families, such as normal or gamma distribution. This leads to the use of the parametric method. Some researchers, especially those with a statistical background, may find the use of the parametric method to derive VaR rather restrictive and over-simplified, preferring instead that the probability distributions of the risk factors are derived empirically. This can be done by Monte Carlo simulation if the mechanisms of changes in the risk factors are known. In this paper, we assume that a stochastic process can model the mechanism of changes in asset returns, thus the asset returns are presented as a probability distribution rather than values. Moreover, we incorporate a self-contended pseudo-random number generator into our Monte Carlo simulation method, which as far as we know is a first in financial modelling using an Excel 2007 spreadsheet.

There are several studies which compare the relative merits of historical, parametric and Monte Carlo VaR approaches, for example Lechner & Ovaert (2010), Deepak & Ramanathan (2009), Jorion (2001), Pritsker (1997) and Stambaugh (1996). In general these studies find that there is no particular best method. Parametric methods are simple to implement and very useful when returns follow a normal distribution, but they are not appropriate when there is non- normality such as asymmetry or leptokurtosis. Monte Carlo has the advantage of increasing the number of observations but it can be time-consuming and computer-intensive to implement. The historical method accurately measures past returns but it can be a poor estimator of future returns if the market has shifted. Stambaugh (1996) notes that each method has strengths and weaknesses and that they should not be viewed as competing methods but as alternatives which might be appropriate in certain circumstances. Different approaches may be appropriate for different types of portfolio, different purposes and different levels of resources available to invest in the analysis.

To illustrate the use of the two methods described in this paper, we continue the Cheung and Powell (2012) teaching study. Four listed shares (Coca Cola, Bank of America, Boeing and Verizon Communication) from the New York Stock Exchange are used to demonstrate the calculation of VaR of a single asset and a portfolio. In the case of a single asset, an investor has an exposure of $1 million (V) worth of Coca Cola shares at time t (any trading day after 3 August 2010, which is the closing share price date in our sample). The risk factor is share price (p), risk horizon is one trading day, historical data series is 10 years of daily adjusted closing prices (from 4 August 2001 to 3 August 2010, a total of 2,513 observations), and the level of confidence (?) is 95%. The question of interest is: in 95 out of a 100 times, what would be the worst daily loss the investor could experience by holding $1 million Coca Cola shares? In the case of a portfolio (using the same historical period, number of observations, risk horizon and confidence level used for the single asset above), the investor extends his/her share portfolio exposure (V) to $5 million, comprising $1 million Coca Cola (20%), $1.5 million Bank of America (30%), $1.5 million Boeing (30%), and $1 million Verizon (20%). Again, we ask the question: in 95 out of a 100 times, what would be the worst daily loss the investor could experience by holding this $5 million portfolio?

This paper is organised as follows. The next section discusses the application of the parametric method to a single asset. The third section describes the workings of the Monte Carlo simulation method, again only applied to a single asset. The fourth section expands the two methods to calculate VaR for a portfolio of assets. The fifth section compares and discusses the results from the various methods. The last section is the conclusion.

Parametric Method: Single Asset

Using the parametric method, the researcher specifies a probability distribution that characterises the likely values of a risk factor. Bachelier (1900) used the central limit theorem to derive a normal distribution for share price movements in the Paris Stock Exchange, and discovered that successive changes in share prices are approximately normal. This normality assumption for asset returns has been in place since then. However, in the Black-Scholes (1973) model, share prices are assumed log-normally distributed, consistent with continuous compounding.

The crucial step in the parametric method is to obtain the mean and standard deviation of the normal distribution from the historical data series. Once these values are obtained, we can proceed to calculate the 5% VaR return by entering 5% in the first argument of the Excel function NORMINV (probability, mean, standard deviation). The 5% VaR value is then calculated by multiplying the exposure by (1 - the absolute value of the 5% VaR return). To plot the parametric VaR diagram, we construct a table with 80 bins for the calculation of the relative frequencies of the normal distribution. In Excel, the probability density function of a normal distribution is calculated by NORMDIST (x, mean, standard deviation, cumulative) where x is the x-coordinates showing the daily returns, mean and standard deviation are the parameters of the normal distribution, and cumulative = FALSE for the probability density function. The execution of this procedure is presented as a screenshot in Table 1.

Armed with the relative frequencies, we plot the parametric one-day VaR for Coca Cola shares in Figure 1.

Monte Carlo Simulation Method: Single Asset

Monte Carlo simulation relies heavily on probability theory to drive the simulation process. It involves conducting repeated trials of the values of the uncertain input(s) based on some known probability distribution(s) and some known process to produce a probability distribution for the output. That is, each uncertain input or parameter in the problem of interest is assumed to be a random variable with a known probability distribution. The output of the model, after a large number of trials or iterations, is also a probability distribution rather than a numerical value. In the context of VaR, the uncertain input is the one-step-ahead asset returns and the uncertain outputs are the 5% VaR return and value. The process linking the inputs with the output is the geometric Brownian motion process.

Intuitively, the researcher can think of simulation like scenario analysis. Instead of having three or five scenarios, the simulation process generates thousands or tens of thousands of scenarios. From this long list of scenarios, we gain a much better understanding of the nature of the problem, the most likely outcome and the extent of uncertainty surrounding it.

Instead of defining the probability distribution of the risk factor (in this case, the return of a share) as in the parametric method, the Monte Carlo simulation method derives the distribution of the share returns using a stochastic process. In most finance studies, we assume that asset prices, though largely unpredictable, follow a special type of stochastic process known as geometric Brownian motion, described by the following equation:

... (1)

where St is the share price at time t, e is the natural log,?t is the time increment (expressed as portion of a year in terms of trading days, e.g. one trading day will yield ?t = 1/251.4 of a trading year in our exercise), k = µ-(σ2 2) is the expected return (which equals annualised mean return µ minus half of the annualised variance of returnσ2 ), and [varepsilon]tis the randomness at time t introduced to randomise the change in share price. The variable [varepsilon]t is a random number generated from a standard normal probability distribution, which has a mean of zero mean and a standard deviation of one. Sengupta (2004, pp.285-295) provides a solid discussion of equation (1).

The return of a share price can be obtained by rearranging equation (1) to yield equation (2):

... (2)

The key to our exercise is generating the future returns according to equation (2). The main problem in modelling and simulating stochastic processes is generating a stream of random numbers. Excel provides several ways to generate random numbers, some true ones and some pseudo ones. True random numbers between 0 and 1 can be generated by the Excel function RAND (). The problem with true random numbers is their volatile nature, which means a new value is returned every time the worksheet is recalculated (e.g. by pressing F9). This can be problematic if the researcher wants to repeat the experiment with the same set of random numbers or to re-examine the simulation results. This is where pseudo-random numbers come into play. Pseudo-random numbers are generated by formulas. As long as the seed number is fixed, the set of random numbers will be fixed, which enable the researcher to have a second chance to re-examine the simulation results. Excel provides a pseudo-random number generator in its Random Number Generation tool in Data Analysis buried deep in the Data Ribbon. Figure 2 is a screenshot of Excel's Random Number Generator. The number of variables box is the number of random number columns desired by the researcher, and the number of random numbers box is the required number of rows. The seed number is any whole number selected by the researcher, which is fixed to a specific set random numbers. For example, every time the number 10 is re-entered, those same random numbers will be generated.

To avoid the tedious task of calling up and filling in the Random Number Generator dialog box every time the researcher wants to change the seed number or simulate another stream of pseudo-random numbers, we recommend that the researcher build their own pseudo-random number generator. This can be easily incorporated into the simulation model.

One of the most popular random number generators is the linear congruential method developed by Lehmer as discussed in Sheskin (2007, pp.402-407). Equation (3) is a multiplicative variant of the linear congruential method which is designed to generate a stream of uniformly distributed random numbers x between 0 and 1:

... (3)

where mod is the modulo operation (it is conducted in Excel by the function MOD (number, divisor)), 0? a is the multiplier (a recommended number for a, as used by most statisticians, is 75), m is the modulus and it has to be greater than a (a recommended number for m, as used by most statisticians, is 231-1 or 231), and lastly, 0< x0is the initial seed number or starting value. The longest possible length of non-degenerated and non-cycled random numbers of this method is the value of the modulus.

The random numbers (x) generated by equation (3) are uniformly distributed random numbers representing probabilities of the events that certain rates of return will occur. They have to be transformed into normally distributed numbers ( [varepsilon]) before incorporating into equation (2). The transformation is carried out the using the Excel function NORMSINV (probability) where the random numbers enter the function as the only argument.

If the researcher wishes to use true random numbers, the Excel calculation function needs to be set up before incorporating the RAND () function (note that this Excel function does not have an argument). In Excel 2003 or before, go to Tools and then Options. Once the Options dialog box appears, go to the Calculation tab and tick the Iteration box and set Maximum iterations to 1 and Maximum change to 0.001 (see Figure 3). Once iteration is turned on, iterations are generated by pressing the F9 key (instead of the random numbers continually recalculating themselves). Excel then recalculates the worksheet the number of times specified in the Maximum iterations box (when you press the F9 key) or until the results between calculations change less than the amount specified in the Maximum change box.

If using Excel 2007 (with Vista), click the Microsoft Office Button, then Excel Options at the bottom of the dialog box, select Excel Add-Ins, and then select Formulas on the left-hand side panel to display the dialog box below. In the Calculation Options section, tick the Enable Iteration Calculation box and set the Maximum iterations to 1 and Maximum change to 0.001 (see Figure 4).

The above discussion lays the groundwork for performing Monte Carlo simulations for calculating 5% VaR return and value for an individual asset.

The simulation process for 5% VaR returns and value includes five steps. Step one calculates the parameters in the geometric Brownian motion process. Step two generates uniformly distributed pseudo-random numbers between 0 and 1. Step three converts the uniformly distributed random numbers from step one to normally distributed random numbers between 0 and 1. Step four applies the normally distributed random numbers into the geometric Brownian motion process to yield the simulated asset returns. The final step calculates 5% VaR returns and 5% VaR value in a fashion similar to that discussed in the parametric method. Table 2 succinctly captures the calculation of the Monte Carlo simulation process.

Table 3 shows the calculation of VaR return and VaR value. To plot the probability distribution for the simulated returns, we construct an 80-bin table from -8.00% to 8.00% (with bin size of 0.2%) and use the FREQUENCY (data array, bins array) function to calculate the number of returns that fall into each bin. We then use the relative frequencies to construct a scatter with a smooth line chart, as shown in Figure 5. Apparently the returns are not normally distributed, with the distribution skewed to the left and showing a jagged curve. As usual, a volatile 5% VaR return line is fitted to the diagram.

There are two important issues to consider in relation to the Monte Carlo simulation method. The first issue concerns the initial seed number. Since this can be any positive value, what is the appropriate number? The second issue relates to the number of iterations. In our example, we run 2,000 iterations, which is an ad hoc decision. Is there a minimum number of ideal iterations? In the following paragraphs, we briefly discuss these two issues.

In Table 2 the initial seed number used in the simulation process was arbitrarily selected as 230. The resultant 5% VaR return and the 5% VaR value are -2.318% and $23,177.81 respectively (Table 3). Table 4 uses a range of other seed numbers to illustrate that there is no appropriate initial seed number.

The 5% VaR return, in this sample of five seed numbers, fluctuates between -2.41% to -2.28%, while the 5% VaR value fluctuates from $24,099 to $22,832. The differences in the latter are insignificant with respect to the exposure of $1 million. The differences will narrow as the number of iterations increases. In view of this, any initial seed number is acceptable as long as a large number of iterations are simulated as discussed in the ensuing paragraphs.

Put into context, the number of iterations n is the number of pseudo-random numbers ([varepsilon]) we have to generate. The minimum number of trials n depends on how precise you want your simulation to be. Equation (4) gives the minimum number of iterations to achieve the desired accuracy D, defined as D= y-µwhere y is the simulated value of the risk factor andµ is the mean of the probability distribution of the risk factor.

... (4)

Most researchers, however, ignore equation (4) and simulate at least 10,000 to 20,000 times, which should give an approximately normal distribution for the risk factor. In our teaching study, we simulate only 2,000 times for illustration purposes.

Parametric Method: Mutiple Asset Portfolio

Assume our investor increases their portfolio holdings by purchasing $1.5 million shares in Bank of America (BoA). The investor now has a portfolio of $2.5m with $1m (40%) Coca Cola and $1.5 million (60%) BoA. When additional assets are introduced into the portfolio, we need to account for correlation and covariance between the assets before calculating the VaR. We use the variance-covariance matrix, which is the approach used by RiskMetrics (J.P. Morgan & Reuters 1996), who introduced VaR. We start with a two asset portfolio. The steps involved are shown in Table 5, and further reading on this approach can be obtained in Choudhry (2004).

Matrix multiplication is required to calculate variance-covariance for several assets. Matrices need to be set up with the number of columns in matrix A equal to the number of rows in matrix B. To calculate the value of a matrix C from matrices A and B, (where i is the row index and j is the column index for matrix A, and j is the row index and k the column index for matrix B), the following formula is used:

... (5)

Let us assume our investor's portfolio consists of $5 million. In addition to the shares mentioned in the previous section, the investor has $1.5 million shares in Boeing and $1 million in Verizon. The portfolio now contains shares in four companies with 20% Coca Cola ($1 million), 30% BoA ($1.5 million), 30% Boeing ($1.5 million) and 20% Verizon ($1 million).

Variance and correlation matrices need to be created and multiplied together to form a variance-correlation matrix. This in turn multiplies with the variance matrix to create a variance- covariance matrix, which is then multiplied with the weightings to form a weighted variance- covariance matrix, the sum of which gives the portfolio standard deviation from which the VaR can be calculated as shown in Table 6.

The table shows matrix multiplication for the four share portfolio. The historical return series for each of the four assets are named cocadaily1, BoAdaily1, boeingdaily1 and verizondaily1. Further shares can be accommodated by increasing the number of rows and columns equally, limited only by the number of columns in Excel. Matrices in Excel can be multiplied together using the formula MMULT () as shown in the formulae in Column G. The variance matrix is multiplied by the correlation matrix to form the variance-correlation matrix, which is then multiplied by the variance matrix and share weightings to form the variance-covariance matrix. The latter is summed to calculate the portfolio variance from which the standard deviation and VaR are calculated as per Rows 32:37. It should be noted that, if preferred, the Excel Data Analysis Add-in can be used an alternative tool to generate individual matrices such as the correlation matrix.

Monte Carlo Simulation Method: Multiple Asset Portfolio

First undertake a Monte Carlo simulation for each asset in the portfolio. Then obtain the daily weighted average returns from which VaR is calculated as per Table 7.

An identical simulation process is followed for each of the four shares in our portfolio as was followed for Coca Cola in Table 2, and the daily weighted average returns are then calculated. The summarised results are shown in Cells B24:F35. VaR is then calculated in Cells I25:I29 for the weighted average returns in exactly the same manner as was used for a single asset in Table 3. Formulae are not repeated from Tables 2 and 3. The share prices in Cells I7:L7 are the closing prices of the last day of our data sample (3 August 2010).

A Comparison of the Teaching Studies Results

The smallest and the largest 5% VaR returns in Table 8 differ by 0.12% (Coca Cola) and 0.30% (portfolio), while the smallest and the largest 5% VaR values differ by $1,200 (Coca Cola) and $15,163 (multiple asset portfolio). These differences are insignificant given the portfolio sizes of $1 million (individual asset) and $5 million (multiple asset). Based on the similar results obtained, it is difficult to argue which method is better. Indeed, the results depend on the method and the historical data series collected. Further back testing, beyond the scope of this paper, needs to be performed to yield further information to ascertain the appropriateness of these methods (Berry 2009).

Conclusion

The study, together with the prior work of Cheung and Powell (2012), shows how a complete range of VaR models, encompassing all three main VaR methods, can be constructed in Excel. The step-by-step teaching study approach allows teachers, students and researchers to build inexpensive VaR models. These range from simplistic parametric methods suitable for normal trading conditions through to more complex historical and (most complex) Monte Carlo models not dependent on a normal distribution assumption and more suited in times of frequent financial disturbance. The Excel models are highly flexible and easy to change as well as offering a range of modelling techniques such as the real or pseudo random number generators.

References

References

Bachelier, L. 1900, Théorie de la Spéculation. Paris: Gauthier-Villars. Translated by A. J. Boness. In The Random Character of Stock Market Prices, ed. P. Cootner 1964, pp.17- 78. Cambridge, MA: The MIT Press.

Berry, R. 2009, Back Testing Value-at-Risk. Retrieved 1 September, 2011. Available at http://www.jpmorgan.com/tss/General/Back_Testing_Value-at-Risk/1159398587967

Black, F. & Scholes, M. 1973, 'The Pricing of Options and Corporate Liabilities', Journal of Political Economy, vol.8, no.13, pp.637-654.

Cheung, Y. H. & Powell, R. J. 2012, 'Anybody Can Do Value at Risk: A Nonparametric Teaching Study', Australasian Accounting, Business and Finance Journal, vol.6, no.1, pp.111-123.

Choudhry, M. 2004, Fixed Income Markets, Instruments, Applications, Mathematics, John Wiley & Sons (Asia) Pty Ltd, Singapore.

Deepak, J. & Ramanathan, T. 2009, 'Parametric and Nonparametric Estimation of Value at Risk'. The Journal of Risk Model Validation, vol.3, no.1, pp.51-71.

Jorion, P. 2001, Value at Risk: The New Benchmark for Managing Financial Risk. 2nd edn, McGraw-Hill, New York.

J.P. Morgan & Reuters 1996, RiskMetrics Technical Document. Retrieved 14 August, 2011. Available at www.faculty.idc.ac.il/kobi/RiskMGT/rmtd.pdf

Lechner, A. & Ovaert, T. 2010, 'Techniques to Account for Leptokurtosis and Assymetric Behaviour in Returns Distributions', Journal of Risk Finance, vol.11, no.5, pp.464-480.

Pritsker, M. 1997. 'Evaluating Value-at-Risk Methodologies: Accuracy versus Computational Time', Journal of Financial Services Research, vol.12, no.2/3, pp.201-242.

Sengupta, C. 2004, Financial Modeling Using Excel and VBA. John Wiley & Sons, New York.

Sheskin, D. J. 2007, Handbook of Parametric and Nonparametric Statistical Procedures. 4th edn, Chapman & Hall/CRC, Boca Raton, FL.

Stambaugh, F. 1996, 'Risk and Value at Risk', European Management Journal, vol.14, no.6, pp.612-621.