In my previous post, I claimed that the VaR at the 99% confidence level for an asset with a mean daily return of $1000 and a daily standard deviation of $500 was $165, i.e. the maximum daily loss under normal market conditions won’t exceed $165, 99% of the time.
This can be easily checked using excel. Use the Norm.Dist function, replacing x with the VaR amount:
=Norm.dist(-165, 1000, 500, TRUE)
The “True” returns the cumulative distribution function. So what we’ve told excel to do is return the probability that a normally distributed random variable with a mean of $1000 and standard deviation of $500 takes on a value less than or equal to -$165. Since we computed the VaR for the 99% confidence level, excel should return the number .01. You can check that it does (approximately).
We can use this function to compute other values of interest, like the probability that we’ll earn a negative daily return. The setup is:
=Norm.dist(0, 1000, 500, TRUE) = .02275 = 2.275%.
And, finally, to demonstrate the symmetry of normally distributed variables, calculate the probability that the asset will generate a daily return in excess of $2,165:
(1 – Norm.Dist(2165, 1000, 500, true)) =(1 – .990) = 1%
This is equivalent to the VaR value, but in the positive direction. 1% of values lie below $-165, and 1% lie above $2,165. Intuitively we could just add these together to find the probability that a return between $-165 and $2,165 is achieved, but more mathematically we could do so by subtracting the CDF of the smaller number from the larger number:
Pr(-165 < Return < 2,165) = CDF(2,165) – CDF(-165) = F(2,165) – F(-165) = .99 – .01 = .98 = 98%
If we want to change the confidence level, and compute a new VaR, we’ll have to change our z-score of 165 because it depends on the confidence level. We’ll use the same formula, but this time we’ll use z = -1.645 because 95% of observations are greater than -1.645 (in terms of standardized z units) on a normal curve. Remember the substitutions z = -z and VaR = -VaR:
1.645 = (VaR – 1000)/500 -> VaR = -177.5, which is really positive $177.5 because we substituted VaR = -VaR. So our daily return won’t be less than $177.5, 95% of the time. Notice that $177.5, the 95% VaR, isn’t even a loss, it’s just a return that is 1.645 standard deviations less than average.