Value at Risk or VaR as it’s usually referred to, is a Risk Management metric widely used by many Banks and Investment Banks around the world. The post explores the basics of the definition, the calculation and the usage of this famous Wall Street Metric.

If you’ve watched the movie ‘Margin Call‘, there is a scene where the Chief Risk Officer (CRO) and a member from the CEO’s Office assess the potential loss the firm is going to suffer. They conclude by saying “*..it’s these VAR numbers that are really setting this thing off.*“

Actually much earlier in the movie, one of the Analysts figures out that the VAR numbers are off on the firm’s trading portfolio and that sets the course for the rest of the movie.

So, what is this VaR, really? Value at Risk or VaR is a Risk Management metric used by almost every trader working on a bank’s trading desks. The typical expression of a VaR number goes like this:

“The 90% monthly VaR is $10,350!”

or

“The 90% monthy VaR is 12.52%!”

You may notice that there are 3 distinct components to a VaR Statement:

1. The Confidence Level (“

90%“)2. The Time Period (“

monthly“)3. The Loss Amount (“

$10,350“) or Loss Percentage (“12.52%“)

The statements above essentially mean:

“

There is a 90% chance that the loss from this portfolio will be limited to $10,350 in absolute value or 12.52% of the portfolio on a monthly basis.“

That might seem like a mouthful. So, instead of trying to muddle in the theoretical definitions, let’s do an example VaR calculation. If you are inclined to know, there are 3 different methods to calculate the VaR for any given Asset or Portfolio: The Variance-Covariance Method, the Historical Method and the Monte Carlo Simulation Method. In my opinion, the Variance-Covariance Method is too simple and the Monet Carlo Simulation Method requires a lot of calculations and can get confusing. So for the purpose of this post, I will use the Historical Method only. I will calculate Historical VaR for an Indian company: **Asian Paints**. Later, we will also talk about how this VaR number can be an useful metric for traders and investors alike.

### Step 1: Obtain the historical share price data of Asian Paints

I will take the ** last 10 years** share price data for Asian Paints. But you can go back even longer if you wish to. The thumb rule is that during the period for which you take the data, there should be no drastic change in the business model or in the way of running the business. For instance, Motherson Sumi Systems (MSSL) started off with the manufacturing of Wiring Harness. But today, they manufacture a whole lot more, as they business model is now dependent on acquiring smaller auto parts manufacturers. This is in addition to the fact that there’s a demerger going on in the business. So, taking the long term share price data for MSSL may not be a good practice in order to calculate VaR. In the case of Asian Paints, there has been no material change in the business model since the last 10 years, so I will go ahead and take that data.

I prefer using Yahoo Finance for getting historical share price data. While downloading the data, also determine the Time Period. Wherever possible, take the ** Adjusted Closing Price** data (Share price adjusted for splits and bonuses), just to be accurate.

### Step 2: Calculate the daily, weekly and monthly percentage price changes

This should be a relatively easy thing to do in Excel. Just calculate the percentage change in share price for every data point (Here, weekly price). The formula should be *(Current Price – Last Price)/Last Price*. Don’t forget to change the format to ‘Percentage’ for this entire column of calculation, just to make things less cumbersome. Let’s say you’re calculating weekly VaR. The idea is to calculate and lay out the weekly change in the share price (On a rolling basis, mind you).

Remember that for weekly and monthly rolling return calculations, the first 6 data points (For Weekly VaR) and the first 29 data points (For Monthly VaR) should not be considered. The calculation starts after that. For Daily VaR, you can calculate from the very first data point.

### Step 3: Organize the percentage changes from low to high and Rank them

This is not a mandatory step. But doing this makes it much more easier to calculate VaR in Excel. You can sort the returns from low to high (Ascending order) and rank them from 1, 2, 3 and so on until the end.

### Step 4: Find out out the Xth Percentile Rank for the required returns (Daily, Weekly, Monthly)

Now, define the Confidence Level and Time Period. Let’s say you want to find the 99% Weekly VaR number. All you have to do is find the 99th Percentile Rank in the Weekly price change that you just calculated. The corresponding returns will give you the required VaR number. In the case of Asian Paints, the 99% Weekly VaR is -9.25%. For those of you stastically inclined, the returns in a VaR calculation are usually expressed in a Normal Curve. You can see below that -9.25% falls towards the tail end of the Normal Graph, which is where you’d expect it to appear for a 99% Confidence Level.

Here, I have to point out that I haven’t taken the exact 99th Percentile percentage returns. Rather, I took the data points immediately before and after the 99th Percentile Rank and averaged them to arrive at this result. This is a standard practice in the industry to avoid anomalies in the data.

That being said, I have calculated the 90%, 95% and 99% VaR numbers of Asian Paints for different time periods. Please note that I have done the actual calculations only until Monthly VaR. Quarterly and Yearly VaR numbers are extrapolated (Also a practice in some parts of the industry).

You can convert Daily VaR into X period VaR by simply multiplying the Daily VaR number by the Square Root of the number of trading days in X period. For instance to convert Daily VaR into Yearly VaR, you simply multiply the Daily VaR number by the Square Root of 251, which is the number of trading days in a year in India. This rule, called the ‘Square Root Rule’, applies any other period as well. But since you are extrapolating the actual number, the accuracy and consequently the usefulness is diminished. Other VaR Calculation methods discussed above may offer better options to calculate VaR for longer time periods.

I promised I will explain how VaR numbers can be used by investors and traders alike in inform their decision making process. Now, let’s pull the numbers for Asian Paints once again.

Let’s pick a number. Say, the Weekly VaR at 99% Confidence Level. Remember the earlier explanation about the components of a VaR statement? So, *the 99% Weekly VaR of Asian Paints is -9.25%*. What this means is that there is a 99% chance that the losses over a week’s period for Asian Paints stock will not exceed -9.25%. Put otherwise, **if Asian Paints stock ever corrects by 9.25% in any given week, it’s an extraordinary event and warrants interest**. The same goes for the other periods as well. If the stock corrects more than 30% in a Quarter or 22% in a Month, it’s something that should catch your attention.

## Use Case for Investors: Filter/Indicator for Undervaluation

Many investors I know use the 200-day Moving Average as a filter for Undervaluation (*Filter* being the key word). In a similar fashion, based on a chosen Confidence Level and Time Period, you can consider the VaR Number to be a filter or an indictor for potential Undervaluation. Again, I stress the words *filter*, *indicator* and *potential*. The Value of a company involves a lot of components and cannot be done done in a jiffy like a VaR calculation.

If it’s confusing, let’s take Asian Paints again. The 95% Monthly VaR is about 18% or so. What I’m saying is, if Asian Paints ever falls 18% in a month, it should interest you as an investor to investigate the business and look for potential Undervaluation. This does not mean that the fall is always unwarranted or it always indicates an Undervaluation. Treat it as a reminder of sorts and nothing more.

## Use Case for Traders: Stoploss

VaR is primarily used a Psuedo-Stoploss mechanism in many trading desks across the world. Typically, when some extraordinary price movement happens – say, in the case of Asian Paints, it falls 20% in a month – the Risk Manager and his team will get a warning saying that there has been a ‘VaR Breach’. What the Risk Manager or his team does with that information is up to them. Usually, the firm already has a set of rules stating how many VaR Breaches are allowed for each type of portfolio.

If there’s a single, standalone Daily VaR Breach surrounded by all normal data, it makes sense to ignore it. But imagine if you’re in a trading desk and you already received 3-4 VaR Breach warnings. This means that an extremely unlikely loss scenario has happened 3-4 times in the recent past. This could very well happen at a time like, say at the beginning of the 2008 Financial Crisis or even the latest 2020 COVID19 crash. It will be prudent to consider the Breaches as a signal of stop loss and exit the position. For a Short positions, of course, you should consider the other end of the spectrum – the extraordinary gain scenarios.

You can set up automated trading rules to inform you on when to act on a VaR Breach and when not to act. In fact, interpreting VaR numbers is one of the crucial duties of a Risk Manager at any Trading firm or Bank. It differs between different Asset Classes and it’s a highly sought-after skill in Wall Street.

I am also attaching the Excel in which I calculated the VaR numbers for Asian Paints. I made a small UI in the first sheet, so you can see the VaR numbers for different Confidence Levels and Time Periods.

Feel free to look at the second sheet where the Returns Sorting / Ranking is done and the third sheet with the Normal Curve for the Weekly VaR numbers.

If you are good with Google Sheets, you can create a similar version that is accessible for all listed firms in India (At least for the ones where Google Sheets can pull the data). In case you are interested in something like this and need additional explanation on how to calculate the VaR numbers, feel free to comment down below. I’m not good with Google Sheets, but I can help you understand how the VaR numbers need to be calculated. We can collaborate if possible.

In case you’re still interested in this, at the beginning of the movie, Eric Dale gets fired from the trading firm one fine morning. At the time, he’s actually working on a complicated VaR model for some of the firm’s portfolios. Peter Sullivan, who gets handed over a pen drive with Dale’s workings, sits back and completes the models. This allows him to see that the firm’s portfolio has had multiple VaR Breaches in the recent past and nobody in the firm had noticed it. This gets escalated to the Chief Risk Officer, Sarah Robertson and a member of the CEO’s Office, Ramesh Shah – both agree that it’s the unnoticed VaR Breaches that has put them in a tight spot.

By the way, apart from all this, the 10-minute sequence in the movie where the Senior Partner meets the CXOs and Traders is very well shot and told. I hope you enjoy it as much as I did.

Margin Call is my most favorite Finance-related movie of all time. If you haven’t see it yet, make it a point to watch it this weekend. You will not regret it.