I.Introduction
Distributions are very beneficial for data visualizations. By knowing certain descriptive statistics information (i.e. mean, standard deviation), an observer can estimate the distribution of a specified data set. Distributions are very useful especially when the observer does not have access to the whole data. The normal distribution is often used because of its central limit theorem, which states that the average of observations taken from the same unknown distribution converges to normal distribution if the number of observations is large enough. A real life application of the normal distribution is its utility in the field of finance, in this case, predicting risk. However, distributions will never be 100% accurate when compared to the actual data. This inaccuracy is often noticed when an observer tries to estimate the risk of a certain stock price.. To prove this statement, we will try to compare the Value at Risk (VaR) of Apple Stock Price at 99% confidence level using both percentile and normal distribution in Excel.
II. Descriptive Statistics and Histogram
To compare the VaR of Apple Stock Price using Percentile and Normal Distribution, we need to find the Mean and Standard Deviation of the data. We would also need to create histogram of the Apple Daily Price Change to picture the dsitribution.
First, we need to find out the daily change of Apple Stock Price by subtracting the close price of a particular day from the day after for all the data except for the last data. This is because the last data will be void.
1. Descriptive Statistics
Using the Data Analysis function, we could find the Mean and Standard Deviation of the data. We will use the Mean and Standard Deviation to find the VaR of Apple Stock Price at 99% confidence level using the Normal Distribution.
2. Histogram
Using Data Analysis function in Excel, we could find the largest and smallest data. We can compute the range by subtracting the smallest data from the largest data. We found that the range of the data is 16.4729.
Next, we need to determine the class width. The number obtained when the class width is multiplied by the number of classes (40) needs to be bigger than the range. This is because we want all our data to be included and displayed in the histogram. However, this number cannot be too large, because we would not want to have any empty classes in our histogram. Through this consideration, we decided to have 0.42 as our class width.
This step isn’t particularly necessary, but we prefer to have equally distributed data in our histogram when assigning each bin. First, we need to remember that when we have n number of classes, we will have n+1 number of bins present. To create equally distributed bins, we decided to assign the middle bin first, which is the 21st bin. Furthermore, we divide the range by two, and add it to the smallest data we found earlier. From there on, we can find each bin by adding or subtracting the class width progressively from the 21st bin.
Then, we have to find the frequency of each classes using the Data Analysis function. Bear in mind that the smallest bin will yield no result and we will discard it later.
Afterwards, we will set the midpoints of each classes. We could determine the midpoint of the first class by adding the first bin to the second bin and dividing it by two, the second midpoint by adding the second bin to the third bin and dividing it by two, and so forth until the 40th class. These midpoints will be the X-axis for our histogram. After that, we assign the frequency of each class we found earlier to each midpoint.
Because we would like to know the probability, we need to allocate Probability Density as our Y-axis instead of Frequency. To find out the Probability Density of each class, we divide the Frequency with the total number of data to find Relative Frequency, and then we further divide the result by class width, which is 0.42.
Finally, we could draw our histogram by drawing a gapless bar chart.
III. Fat-Tailedness and Skewness
Skewness and kurtosis are both used to define distribution. Kurtosis is a measure of extreme values in both tails of the data. Large kurtosis describes large amounts of data with extreme values in respect to the normal distribution. This phenomenon is often described as fat-tail. Negative kurtosis pictures the opposite. Skewness explains the degree of distortion from the symmetrical bell curve, or normal distribution. Negative skewness means that the left tail of the data is longer or fatter than the right tail. Positive skewness translates to the opposite.
The normal distribution assumes that the data has zero as their kurtosis and skewness. This is one of the factors on why the normal distribution is never 100% accurate and precise when describing spread. We can eventually notice that the kurtosis and skewness of the data are both non-zero. Its kurtosis is 6.8883, which means that our data has a huge tail in respect to the normal distribution. Extreme values in the Apple Stock Daily Price Change are not exactly infrequent or unusual. Our data’s skewness is -0.1394. It is relatively symmetrical since our skewness value is close to zero. Still, its value is negative, which means that our data has a longer or fatter tail in the left compared to its right tail. This means that within this case, extremely negative price changes are more likely to occur than extremely positive price changes.
IV. Value at Risk Value at Risk is the measure that estimates potential loss in value of risky asset or portfolio, over a given time horizon, at a pre-defined confidence level. VaR is often implemented in the finance industry to measure the maximum amount of money loss or risk given normal market conditions.
Using the Mean and Standard Deviation found earlier, we could compute VaR for the Normal Distribution of Apple Daily Close Price Change at 99% confidence using Excel’s norm.dist formula. Var at α% confidence level is equal to norm.dist(1-α%) in Excel.
This means that according to the Normal Distribution, we would expect not to lose more than -2.7403 dollars/Apple stock price with 99% confidence level. Bear in mind that this is only applicable given normal market conditions and in a time span of a day (obviously along with larger time span, comes bigger risk).
V. Underestimation of Risk
To find out whether we have underestimated our risk, first we need to compute VaR using our actual data instead of the Normal Distribution. To solve this, we will use the percentile.exc function in Excel.
We found out that the VaR at 99% confidence level for the actual data is -3.5308. This means that if an observer decides to use Normal Distribution to estimate VaR, he/she will miss their estimation by 28.8%, which is a relatively large amount.
This underestimation can be explained by these several explanations.
• The Normal Distribution is not going to be 100% accurate when used to represent data spread. This inaccuracy is seen clearly when we discuss the extreme values in data, in this case, when describing VaR. VaR at 99% refer to the worst 1% of data on the left tail. Normal Distributions do not exactly define tails in the best possible way. It directly assume that both kurtosis and skewness are zero, when in fact, we know in from our data they are not. Kurtosis for Apple Daily Close Price Change is 6.8883, which illustrates that extreme values in the actual daily change of Apple are more typical and frequent than the data described by the Normal Distribution. Our data’s skewness is lower than 0, which means that the actual data has a longer and fatter tail on the left side when compared to the right tail.
• Size of Sample Data taken. A small sample data is useful for getting an initial feel for a problem, but is far too small for reliable results on any interesting questions (in this case when computing our VaR).
VI. Conclusion
The Normal Distribution plays an important role in the practice of risk management. There are many reasons for this. It is a relatively simple and tractable model that seems to capture adequately important aspects of many random variables. Certainly, it has its limitations. Observers, often underestimate the risk of stock returns when they use the Normal Distribution as their only benchmark in calculating risk. From our calculations, we found that an observer will underestimate the risk of the Apple Daily Close Price Change by 28.8% when using the Normal Distribution to compute the VaR at 99% confidence level. This number could mean the difference between a good portfolio and bankruptcy.
By realizing that we have underestimated the risk of a relatively stable blue chip stock like Apple by 28.8%, we could only expect to do so more for other more volatile stocks. As stated by Eugene F. Farma and Kenneth R. French, “ Distributions of daily and monthly stock returns are rather symmetric about their means, but the tails are fatter (i.e., there are more outliers) than would be expected with normal distributions”. This event is caused by Normal Distribution’s lack of ability to represent the tails. Tails are often described by kurtosis and skewness. The Normal Distribution fails to take these variables into account by directly assuming that both these numbers are zero, when they are actually not. Our Apple kurtosis is positive, which means that the actual Apple stock is more volatile when compared to its own Normal Distribution. Its skewness is negative, which describes the fact that the left tail is longer and fatter than the right tail. Moreover, the skewness of Normal Distribution is always zero. This means that the bell curve is always perfectly symmetrical on its mean. The takeaways from this significant case is to expect heavier loss when using the Normal Distribution to calculate risk.
A better way to calculate risk is to use Percentile instead of Normal Distribution when computing VaR. The observer would then proceed to use pre-existing data to compute percentile and assume that history will repeat itself. This method is a part of Historical Simulation, which uses the history of the price change to apply them to the current portfolio as a reference. Monte-Carlo Simulation is another way to solve this problem. The Monte-Carlo simulation relied on random sampling to simulate a model’s outcome many times in order to estimate a probability distribution for the calculated results. Monte carlo simulation is the preffered risk calculation method for volatile stocks with more noise (upredictable random variable). For a relatively stable blue chip stock like Apple, it is more effective to use the Historical Simulation as it provides substantial historical data.