What is the most I can lose on an investment? This is the question every investor who has invested asks at some point in time. Value at Risk (VaR) tries to provide an answer since it is the measurement of the maximum expected loss a portfolio bears.
We will understand and perform VaR calculation in Excel and Python using the Historical Method and Variance-Covariance approach, along with examples with this blog that covers:
- What is VaR?
- Why use VaR?
- Essential points to know while using VaR
- How is VaR calculated in Excel?
- VaR calculation using Variance-Covariance approach
- VaR calculation using the Historical Simulation approach
What is VaR?
Value at Risk or VaR is the measurement of the worst expected loss over a specified period under the usual market conditions. The VaR is measured using ‘confidence levels’ which lie in the range of 90% to 99% such as 90%, 95%, or 99%. The holding period of the financial instrument may vary from a day to a year.
In other words, VaR is a measure of market risk. It is the measure of maximum loss that can occur with a certain confidence interval over a given period of time. Using VaR, financial institutions can determine the sufficient capital reserves they need to cover losses. Also, VaR helps determine whether higher than acceptable risk holdings need to be reduced.
According to Philippe Jorion,
“VaR measures the worst expected loss over a given horizon under normal market conditions at a given confidence level”.
Also, according to one of the studies ⁽¹⁾, it was concluded that VaR does not provide any certainty but rather expectation of outcomes based on certain assumptions.
Download Var Models in Excel & Python
Example
Lets us understand VaR with an example.
Suppose, an analyst says that the 1-day VaR of a portfolio is 1 million dollars, with a 95% confidence level.
- It implies there is a 95% chance that the maximum losses will not exceed 1 million dollars in a single day.
- In other words, there is only a 5% chance that the portfolio losses on a particular day will be greater than 1 million dollars. Learn quantitative portfolio management in detail in the Quantra course.
Why use VaR?
Now, let us find out why one uses VaR or Value at Risk for measuring the expected loss. A trader utilises VaR for the following reasons:
- Understanding the Value at Risk result is easy
- Applicable to all asset types
- Universally utilised
Understanding the result of Value at Risk is easy
Value at Risk represents the extent of risk a trader bears for investing in a portfolio in a single figure. For instance, the Value at Risk is between 90% and 99% which makes it easy to interpret the level of risk.
Applicable to all asset types
Vale at Risk can be easily applied to all asset types, namely, bonds, shares, currencies, derivatives, etc. Also, Value at Risk is utilised by all kinds of financial institutions and banks in order to assess the profitability over risk borne for trading a portfolio or asset. Learn Financial Time Series Analysis for Trading in detail in the Quantra course.
Universally utilised
Value at Risk is an accepted standard for basing buying, selling and all trade-related activities. Hence, you can use it anywhere across the globe.
Download Var Models in Excel & Python
Essential points to know while using VaR
There are a couple of essential points that a trader must know while using VaR as the measurement of risk against investment in a portfolio or financial instrument. These points are:
1. VaR is difficult to calculate for portfolios with a diversity of assets (such as cash, currency, stocks etc.) or a greater number of assets
Calculating Value at Risk for a portfolio needs one to calculate the risk and return of each asset. But, along with the risk-return calculation, the correlations between the assets are also to be calculated. Hence, the more the number or diversity of assets in a portfolio, the more difficult it is to calculate VaR.
2. Different methods or approaches lead to varying results
When different approaches to calculating VaR lead to different results for the same portfolio or the financial instrument, it implies the return distribution is not normal.
How is VaR calculated in Excel?
There are two well-known methods that are used for VaR calculation.
In this blog, we will discuss the following:
- Variance-covariance approach
- Historical simulation approach
Let’s start with the Variance-Covariance approach.
The Variance-covariance is a parametric method which assumes that the returns are normally distributed. In this method,
- We first calculate the mean and standard deviation of the returns
- According to the assumption, for 95% confidence level, VaR is calculated as a mean -1.65 * standard deviation
- Also, as per the assumption, for 99% confidence level, VaR is calculated as mean -2.33* standard deviation
Please note that the abovementioned figures are on the basis of a subjective assumption ⁽²⁾.
Moving on, the steps for VaR calculation using the Historical simulation approach are as follows:
- Similar to the variance-covariance approach, first we calculate the returns of the stock Returns = Today’s Price - Yesterday’s Price / Yesterday’s Price
- Sort the returns from worst to best.
- Next, we calculate the total count of the returns.
- The VaR(90) is the sorted return corresponding to 10% of the total count.
- Similarly, the VaR(95) and VaR(99) is the sorted return corresponding to the 5% and 1% of the total count respectively.
Download Var Models in Excel & Python
VaR calculation using Variance-Covariance approach
Now, we will find out the calculation if Variance-Covariance approach using Python.
Output:
Now, we will use confidence level, mean and standard deviation in the Python code.
Output:
Confidence Level Value at Risk ------------------ --------------- 95% -0.0319445 99% -0.0457278
The values above imply the confidence level of the particular “value” that is at a risk of being lost.
Hence, there is a 95% of confidence that the loss of value may be -0.0319445 and not more and there is 99% confidence that the loss will go to -0.0457278 and not beyond. Since all the values at risk are in negative, the probability is higher that the portfolio will return more than invested amount.
Download Var Models in Excel & Python
VaR calculation using the Historical Simulation approach
Now, let us calculate the VaR using the historical simulation approach.
Output:
Output:
Confidence Level Value at Risk ------------------ --------------- 90% -0.0219604 95% -0.0288828 99% -0.0527869
The above values imply the confidence level of the particular “value” that is at a risk of being lost. Hence, the confidence rate is 90% that the loss may be -0.0219604 and not above.
Similarly, there is a 95% of confidence that the loss of value may be -0.0288828 and nor beyond and there is 99% confidence that the loss will go to -0.0527869 but not further than that. Since all the values at risk are in negative, there is a higher probability of the portfolio returning more than invested amount.
From the calculation above, you can see there is a substantial difference in the percentage of value-at-risk calculated from historical simulation and variance-covariance approach. This tells us that the return distribution is not normal.
Although both the approaches return negative values (indicating more probability of higher returns), there is still a difference in the figures of values which implies that the distribution is not normal.
Recommended reads:
- Covariance Matrix and Portfolio Variance: Calculation and Analysis
- Covariance and Correlation: Intro, Formula, Calculation, and More
- Portfolio Analysis: Calculating Risk and Returns, Strategies and More
Conclusion
In this blog, we covered the two integral ways of using Value at Risk in both Excel and Python. A trader can use VaR for measuring the risk of trading if the essential points of using the same are kept in mind.
If you wish to learn more about VaR, you can gain essential skills required for different quant trading desk roles such as trader, analyst, and developer, you too can do it. This learning track which is algorithmic trading for everyone by Quantra is perfect for traders and quants who want to learn and use Python in trading. Enroll now!
File in the download
- VaR calculation in Excel
- VaR calculation in Python
Note: The original post has been revamped on 9th December 2023 for accuracy, and recentness.
Disclaimer: All investments and trading in the stock market involve risk. Any decision to place trades in the financial markets, including trading in stock or options or other financial instruments is a personal decision that should only be made after thorough research, including a personal risk and financial assessment and the engagement of professional assistance to the extent you believe necessary. The trading strategies or related information mentioned in this article is for informational purposes only.
