By Vibhu Singh
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.
We will understand and perform VaR calculation in Excel and Python using Historical Method and Variance-Covariance approach, alongwith examples.
We'll cover the following about Value At Risk (VaR):
- What is Value At Risk?
- VaR Example
- VaR Calculation
- VaR Calculation in Excel using Variance-Covariance approach
- VaR Calculation using the Historical Simulation approach
What is Value At Risk?
Value At Risk (VaR) was developed in mid-1990s, in response to the various financial crisis, but the origins of the measures lie further back in time.
According to Philippe Jorion,
“VaR measures the worst expected loss over a given horizon under normal market conditions at a given level of confidence”.
This definition implies that it is necessary to choose two parameters, namely holding period and confidence level. Typically the confidence level lies in the range 90% to 99% such as 90%, 95%, 99%. Holding period may vary from a day to a year.
Lets us understand VaR with an example.
Suppose, an analyst says that the 1-day VaR of a portfolio is 1 million dollar, with a 95% confidence level.
- It implies there is 95% chance that the maximum losses will not exceed 1 million dollar in a single day.
- In other words, there is only 5% chance that the portfolio losses on a particular day will be greater than 1 million dollar.
There are various methods that are used for VaR calculation. In this blog, we discuss Variance-Covariance approach and Historical Simulation method for VaR calculation.
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
- For 95% confidence level, VaR is calculated as mean -1.65 * standard deviation
- For 99% confidence level, VaR is calculated as -2.33 * standard deviation
VaR Calculation in Excel using Variance-Covariance approach
Steps for VaR Calculation in Excel:
- Import the data from Yahoo finance
- Calculate the returns of the closing price Returns = Today’s Price - Yesterday’s Price / Yesterday’s Price
- Calculate the mean of the returns using the average function
- Calculate the standard deviation of the returns using STDEV function
- Finally, we calculate the VaR for 90, 95, and 99 confidence level using NORM.INV function. This function has three parameters: probability, mean, and standard deviation. In probability, we use 0.1, 0.05, 0.01 respectively for the VaR(90), VaR(95), and VaR(99)
Steps for VaR Calculation using Python:
1. Let us import the necessary libraries
2. Import the daily data of stock Facebook from yahoo finance and calculate the daily returns
3. Determine the mean and standard deviation of the daily returns. Plot the normal curve against the daily returns
4. Calculate the VaR using point percentile function
VaR Calculation using the Historical Simulation approach
The steps for VaR calculation using the historical method in Excel 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 using count function.
- The VaR(90) is the sorted return corresponding to the 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.
Steps for VaR Calculation using Python:
1. Import the necessary libraries
2. Calculate the daily returns
3. Sort the returns
4. Calculate the VaR for 90%, 95%, and 99% confidence levels using quantile function
As you can see there is a substantial difference in the value-at-risk calculated from historical simulation and variance-covariance approach. This tells us that the return distribution is not normal.
We have covered multiple approaches of VaR calulcation, but there is one more approach of VaR caluclation.
If you wish to gain essential skills required for different Quant trading desk roles such as trader, analyst, developer, check out the Learning Track: Algorithmic Trading for Everyone by Quantra. Perfect for traders and quants who want to learn and use Python in trading.
Files in the download
- VaR calculation in excel.xlsx
Note: We have noticed that some users are facing challenges while downloading the market data from Yahoo and Google Finance platforms. In case you are looking for an alternative source for market data, you can use Quandl for the same.
Disclaimer: All investments and trading in the stock market involve risk. Any decisions 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.