Welcome to Finance and Excel video number 98.
Hey, if you want to download this workbook for chapter 10,
click on the link directly below the video
and scroll all the way down to the Excel Finance class
section.
Wow.
We have an important video here.
We've got to talk about variation in stock return
and ultimately how to measure variation in stock return
with standard deviation and ultimately see
that standard deviation is a great way to measure
risk, the risk of a particular stock.
Now, before we jump into how to do it,
there's just a great easy way to see how come variability
is so important to understand when picking a stock
or judging a stock.
Let's just calculate.
We have stock A and B, and here's our past returns.
Let's go ahead and calculate the average, the arithmetic mean.
So I calculated for that, and then I'm
going to Control Enter.
And I'm going to drag it over.
Whoa, both are returning on average
for a typical year 4.1%.
But look at these values, 5, 10, 12, 17, 19, minus 15, minus 3,
3, 5, 10, 6.5, minus 2, minus 22, all over the place.
And look at this one, 2, 4, 3, 5, all very similar.
Very clearly the best way to understand variation
means how much change is there, how much variation is there,
is to look at a chart of this.
And if you want to see how to calculate this,
you kind of have to know a couple tricks.
I have a video exactly that shows you
exactly how to calculate this.
But here's our-- and here's the legend over here.
Stock A. That's this one.
There is the average, stock B. There's the average.
Here's all the values, all over the place,
some way negative, some way positive.
Right here, here's stock B, and here's all of the values.
They're all clustered around the mean.
These ones are way spread out.
So in terms of variation, this stock return as seen--
this is the mean, and these are all the individual returns.
This one has much more variation than this does, much more
variability than this one, much more volatile in its return
than this one.
So it's somehow, if you want to--
based on picking a stock, there's
average return, average return, which one would you want.
Well, the spread is here.
The spread is there.
Which one would you choose based on this average return?
Well, you'd probably choose this one, right?
Because there's less risk in any year
that you're going to deviate from this average.
And that's very clear here.
Up here, right, if you get the stock
and expect to get an average return of 4.1,
at any particular year there's a huge risk
that you might deviate very far from this return.
So that's visually how to understand variation
or the risk of the stock.
Now, we need to figure out how to calculate
standard deviation, because we're not always
going to have this visual here.
We want a single number.
And by the way, if we're going to calculate the single number
standard deviation that tells us how much variation there is
or dispersion or risk, a single number,
we're going to calculate that using standard deviation.
All right, let's go over here.
The whole trick is, again, this picture.
Here's the mean.
For this one, the difference between these two numbers
is called a deviation.
For this particular point, this one
is minus a huge amount from the mean.
This one's a minus amount.
This one over here, the deviation
between the actual return and this mean
is positive by this much.
So all of those deviations, whereas down here,
they're all going to be really small.
So the trick to standard deviation
is we're going to calculate an average of all
these deviations.
And it means then on average what
amount do we deviate by from the calculated arithmetic mean.
So first, we got to calculate--
first we have to count.
And I'm going to use the count function.
Count counts numbers.
Enter.
And then I'm going to calculate the arithmetic mean
using the average function.
And I'm going to copy these, because these are relative cell
references.
It will work all the way down.
All right, so that's the same.
We're going to need both of those numbers
in our calculations.
Now, the question is the deviation.
And I'm actually going to blow this up here.
On this particular day, I'm going
to take that minus the average-- whoops, minus the arithmetic
mean, and I'm going to have to lock it.
Control Enter.
These are all called deviations.
And actually, I should not have double clicked
and send it down.
So on this particular time, we're 0.9 above the mean,
above, above, above, below, way below, below, below.
So you can see these are deviations.
So for any particular year, it looks
like compared to this other stock,
we really run the risk of a huge loss or a huge gain,
some on the low side, some on the upside.
Now, our goal is to have a single measure.
So if we could average all these numbers, that would be perfect.
Then we would have an average deviation,
and it would be a great measure for risk.
OK, so to average you have to add up and divide by the count.
So I'm going to Alt equals.
And we're going to find something very unfortunate
about deviations.
When you add them all up, of course,
you're going to get zero.
I'm going to Control Shift tilde, which is a--
no, maybe I won't Control Z. There's
a slight minus to the 16.
So there's 16 zeros before that.
I'm going to Control Z and leave that there.
Actually no, I'm going to go to Format Cells, which is actually
right.
I don't even know because I know the keyboard shortcut.
It's either right there or Control 1.
Go to number, and I'm going to apply number
with maybe two decimal places.
OK, so we have a problem.
We can't do that.
Well, here's the trick.
If we square them, each one of these items,
we get rid of the negatives.
And if we square them, which is that, carat 2, obviously
that's going to, in essence, square each value.
So any resultant number we get will be too high,
but we're going to square it to get rid of all the negatives.
They'll all be positive.
And when we add it all up and divide by the count minus 1,
because of statistical reasons, we'll
then take the square root.
And we'll get it back down to the right unit.
So we're going to square it.
Now we're going to add all of these up.
And I actually have little notes over here
for how this is calculated.
We're going to add them all up.
These are adding up all the deviation squared.
Again, we had to use the square to get rid
of all the negatives.
That way have all positives.
That way we can add them.
Now, to get an average, you'd think
we'd just divide by the count.
So we'd say like this.
But in my statistics class, we talk about why
we have to do subtract 1.
It in essence gives us a little bit more accurate answer.
All right, so I'm going to say the count minus 1
close parentheses.
All right, that's still not going
to get us the right answer.
That's called variation.
But really, ultimately, we want to use something called
standard deviation, because it'll get us back
to the correct unit.
Since we squared all these, now we're
going to take our final answer and take the square root.
Now we could do--
we've been doing roots before in this class,
raising it to the 1 divided by whatever it is.
But here, we can use the square root function.
And there it is.
There is our numerical measure called standard deviation.
Actually this one should be right there.
It's a numerical measure of the volatility
or the risk of the stock.
Very commonly used to measure based
on past data, the risk of the stock.
Now, we did all that just explain the math behind it,
but there is, as you guessed it, a function
that will do all of it pretty much instantly.
So STDEV.
Now 2010, they have these new functions.
If you're in 2010, you can use the standard deviation
of a sample.
Is this every single value?
No, it's not.
So it's a sample.
But if you have the population, you could use this,
and then you don't have to subtract 1.
So ultimately, the fact that we subtract 1
has to do with the fact that we don't have all the values just
some of them.
In earlier versions, you use STDEV.
I'm going to use that one for right now.
You simply highlight these returns right there.
And it will do all of this calculating for you
and calculate.
Whoops.
Close parentheses, Enter.
And there's our numerical measure
of the volatility or risk of this stock,
the risk that in any particular year,
we may deviate from our average.
Now, I'm going to cut to the chase over here
and just do the STDEV.
This is the standard deviation for a sample.
This number is going to be a lot smaller, tiny in comparison
to here.
So we can see obviously in one single number,
we can see that this stock has much less variation
or volatility.
The deviations on average are much smaller.
And going forward in this class, we
will get to use this measure of the risk of the stock
numerous times.
All right, we'll see you next video.