1. Calculating Standard Deviation in Excel
So here we have come back into our statistics for finance template workbook
and I've scrolled down to the section called measuring volatility.
Now, I've got two securities here, security one and security two. These
are the same two securities we were discussing in the previous lesson. And
we can see that security one has daily returns of minus one, three, two,
negative two, and 3% respectively over the five day week. And when we
add those together we can see that the return for the week is
5%. So what's the average return? The average daily return, we can use
the average function for that. So I'm going to take the average of
the daily returns, I'm going to press F4 to absolutely reference that and
close bracket. And as we discussed the mean daily return is 1%.
But because I've absolutely referenced that, I can just fill down.
Now I wanna find the differences between the daily returns and the mean
return. That's what X minus mean means. So the X is the daily
return minus the mean, and we can see the differences that we talked
about in the last lesson have been quickly and easily calculated. So on
the first day the minus 1% daily return is 2% below the mean. The
3% daily return on day two is 2% above the mean and so on. So,
remember, when we add these together the issue is that these sum to zero
because some of them above the mean, some of them are below the mean,
some of them are positives, some of them are negatives. So to get
rid of that problem what we do is we square the differences.
So we take the differences, X minus the means, and we square them.
And when we do that the negatives become positives and the positives stay
positives. So now we add them up. So now we want to find
the average differences. We add them up, we press Alt+= to put the
sum at the bottom. So the sum of the squared differences,
we can just press F2, we can say it's 0.22. Now
what we want to do is find the average and there are five
daily returns, and so usually when we find an average we would divide
it by five. But the funny thing happens is when we take a
sample, and this is most likely a sample because there's only five
individual daily returns, when we do a sample it's very unlikely you've
captured the largest daily return in the population and the smallest daily
return in the population. So samples have a bias attached to them when
we calculate standard deviations. So rather than dividing it by five which
is n, what we do when we calculate the sample standard deviation is we divide
it by n minus one. In other words we're going to divide this
by four. And by dividing it by four when we find the average
it makes the result slightly larger which takes into account that bias that
we're very unlikely to have the largest and smallest daily return in our
sample. So n minus one in this case is four because there's five
days, and five minus one is four. So now we can calculate something
called the variance. The variance takes the sum of the squares and divides
them by n minus one. Now this isn't the standard deviation,
it's a part of the way to the standard deviation.
What we need to do to find the standard deviation is take the
square root of the variance. So we need to undo the work that
the squaring has done when we squared X minus the mean. And to
take the square root in Excel we use the SQRT function. We take
the square root of the variance and we get a standard deviation of
2.35%. Now there's a lot of work going on there to get the
sample standard deviation. So there is a standard deviation for a sample
formula in Excel. S T D E V and we want the sample, so
dot S. So I'm just going to select that and hit tab.
So I'm going to go along and take my daily returns which is
what I'm trying to find the sample standard deviation of, and I get
exactly the same answer or exactly the same result but much easier.
But it's useful to do these tables to understand the process of calculating
the standard deviation. So I'm going to do security two now.
If you want to have a go yourself press pause and once you're done,
press play. Otherwise continue to watch and let's see if we can find
the standard deviation for security two. So the first thing that we need
to do is find the average. The average daily returns and absolutely reference
it by pressing F4, close the bracket and we see again
that we have an average daily return of 1% which is unsurprising because
over five days the total return is 5%.
Now we wanna find the distances from the mean so we take the
individual daily returns and we subtract the mean. And we can see straight
away that those differences look wider, they look bigger than for security
one. So this suggests that security two is gonna have a larger standard
deviation. Now again the problem is if we found these averages,
these sum to zero so the average would be zero. So we square the
differences in order to remove the negative numbers and we do that for
all of the five days and then we hit Alt+= to put the
sum at the bottom. All right. And now, I can just check that
and we've got the sum there. So again, this is a sample,
so rather than dividing by five, we're going to divide by n minus one, five
minus one which is four, and we get the variance by taking the
sum of the squares and dividing it by n minus one.
So remember the standard deviation, we still have to undo the squaring that
we did. So we take the square root, we take the square root
of the variance and we see that we have 4.85%. So unsurprisingly,
we have a larger standard deviation and we can just see that by
looking at the differences between the individual daily returns and the
mean return. And again, there's a lot of work there so remember there
is the standard deviation formula, and we're going to assume it's a sample
so we use a dot s and we take our
daily returns and we just close the bracket, tab across let me see
if we've got the same sample standard deviation, whether we use a table
or the Excel function.
2. Let's practice!