1. Calculating Probabilities
Once we confirm that data is normally distributed, we can start doing some
really useful calculations. For example, let's assume the daily standard
deviation of returns for an equity is 2.35%. Because one day is a
very short time period, let's assume that the average daily return,
or the mean return, is 0%. Now, this seems fairly reasonable.
If you took lots of trading days and average what the returns were,
around half the days will have a positive return, around half the days
will have a negative return, and so the overall average daily return will
be very close to 0%. We can now answer the question,
what is the probability on a given trading day that the return of
the equity security will be higher than 2.35%? Remember, 2.35% is one standard
deviation. Essentially, we are trying to find the probability under the
curve here in orange. We know from earlier that the area under the
curve from the mean up to one standard deviation is 34%.
We also know that the data is symmetrical around the mean,
so 50% of the data lies above the mean. By subtracting 34%
from 50% we get the remaining area in the tail of the curve,
which is 16% rounded to the nearest whole percentage. Excel has built in
functions to calculate this. So here we are back on our statistics for finance
template workbook, and I've scrolled down to the calculating probability
section on the demo sheet. Let's think about what we were just talking
about. We had an example security with a 2.35% daily standard deviation
with a mean return of zero. So what's the area
shaded dark blue? Well, we know that the area shaded dark blue is
zero standard deviations away from the mean because it's from the far left
hand side of the bell shaped curve up to the mean,
and the mean is zero standard deviations away from the mean. So looking
at this, we can see that's going to be 50%,
but let's just use our NORMSDIST function just to make sure that that's
correct. So we're going to pick up the zero for the z, and
then we're going to set TRUE for cumulative and we can see that
the area under the curve is 50%. In other words, on any given
day, the stock had a 50% chance of having a negative return. What
about the light blue area? Well, the light blue area is one standard
deviation above the mean. So let's just put a one there,
and again, we can use the NORMSDIST function. I'm just going to type
it out, just to get a bit of practice. One standard deviation above
the mean is and then inside we want to have the TRUE for
the cumulative distribution is 84.13%. So that's the area from the far left
hand side up to one standard deviation above the mean. In other words,
it's the dark and light blue put together. So the question is on
what's the probability on any given day that the stock will have a
daily return greater than 2.35%. Well, that's the orange area shaded on
our bell shaped curve. We know that the total area under the curve
is one, and if we subtract the 84.13, the dark blue and the
light blue shaded areas, what's left over must be the orange shaded area,
which is 15.87. So the probability that this stock has a daily return
greater than 2.35% is 15.87.
2. Let's practice!