Welcome to Finance in Excel, video number 104.
If you want to download this workbook for Chapter 11,
click on the link directly below the video
and scroll all the way down to the Excel Finance Class
section.
Hey, we're on chapter 11.
And last chapter, chapter 10, we looked at historical data.
And we calculated arithmetic mean and geomean
and standard deviation.
The averages gave us a typical value
we could use to predict the future.
And the standard deviation helped
us gauge the variability in return, the volatility,
or the risk.
Now, here in this chapter, we're going
to be estimating the unknown future.
So here's our example.
We have a stock A and a stock B. And we
want to estimate for the future what the expected returns might
be and what the standard deviation is for the stock.
Now, we've had to estimate.
So we say states of economy, maybe we
think we're going to be three different ones--
down, normal, and up.
And based on past data frequency tables,
we estimated probability of the state of the economy.
And looking at past data, we estimated
what we think each stock, stock A,
will return in each one of these states.
Now, these estimates are based on past data and also
future projections.
Now, what we're going to do here is pretty straightforward.
We're going to say, well, if there's a probability of 0.45
that we're going to be in a recession and the return
that we think we'd get in a recession is this,
we'll simply multiply the probability times this.
Then we'll take the probability of normal times
the rate we expect here, multiply those,
and we'll continue this on down and add them all up.
Because it's assumed that these data move
like a random variable, we're going
to use this probability statistical technique called
expected return.
All right.
So let's get right to it.
I'm going to say the probability of the state times the return.
Now, I'm going to enter this and copy it down.
You could see their relative cell references.
And now I simply have to add them up.
Equals S-U-M.
And there is my expected return.
And the way we write this is E and then
R sub A. That means the expected return
for this particular asset, asset A. We can then
do the same thing over here, the probability times the rate
we've estimated.
Copy it down.
And then add it up.
All right.
So this way we did it the longhand way.
And it's very important to do this the longhand way
and see how we did this.
But once you get the hang of it and you do it a few times,
we want to see a single cell method to calculate
the expected return.
Now, I want you to notice something.
What do we do here?
We multiply this times this, this times this,
and added them, and this times this.
Now, notice if this blue box was here but it was here and here,
the whole column, if we could multiply it
times this whole column and then add--
if there was a special function that did that, we would use it.
Well, sure enough there is one.
It's called sum product.
And sum product is just like it sounds.
It multiplies, which is product, and then adds.
The arguments you put in are arrays.
So you can simply highlight the one array comma and then
the other array.
Now, these arrays have to be exactly the same dimension.
This is three rows and one column.
And that way it knows.
It will take all the elements here, multiply them
by all the elements here.
So this times that and then plus this times that
plus this times that.
Sum product, totally beautiful.
Now, I'm going to enter this just to show you.
It gives you the same thing much faster
than doing that whole thing there.
But now I want to think about the cell reference,
because I want to copy this over.
So I want this blue one to be multiplied
by this one over here when I copy it over to this cell.
So I'm simply going to lock this one
and leave this one relative.
Click there, F4.
Now I can copy this over.
Same thing.
So now, step one here, we calculated our expected return,
which is a type of average when estimating
how a random variable moves in the future.
All right.
Now, we would like also to calculate
standard deviation, which will be our measure of risk
or volatility.
Just as we did in chapter 10, we're
going to set up a little table.
Now, this is the same as we did last time in that we
have to calculate-- actually, let me hide these.
I'm going to hide these rows here, like that.
I highlighted the row headers.
And then right click Hide.
Now, just as we did last chapter with historical data,
we calculate an average.
And then here we have three returns.
Well, if we calculate the deviation
between each one of these values here and then square them,
add them up, divide by the count,
that would sound like what we did last chapter.
The only difference here is we're going to take the actual
return, that one right there, minus the expected return--
that's our average--
squared.
And then we have to multiply it by the probability,
because we're estimating these amounts for the future,
the unknown future.
All right so we're going to say equals open parentheses.
We're going to then take the actual return
minus the expected value.
Now, when I copy this down, it needs to be locked.
So I'm going to hit the F4, closed parentheses, carrot 2.
That's just like we did last time.
The only difference is, since we have
a number of different values and a number
of different probabilities, we're
going to have to multiply it by the probability.
All right.
Control Enter.
I'm going to copy it down.
Right to 3.
Now, just like we did last chapter,
we're going to add these up.
Now, we added these up.
But unlike last chapter where we added them up and divided
by the count, we don't have to do that here.
We have a probability.
We're multiplying each one of these
times these probabilities.
This is the type of weighted average.
So at this point, we simply add them up.
And now we have to take the square root.
We have, up here, we squared everything.
And when we square it, we have to get it
back down to the same unit by taking the square root
of the final total.
So I'm going to use the square root, our SQRT function,
and then we have our estimate for standard deviation.
That's our standard deviation.
Our estimate of our total risk--
this is the volatility or the risk of the stock.
Now, I have denoted it as SD sub A, so standard deviation
of this particular stock A. In the textbook,
they use a little sigma stock A.
Now we can do the same thing over here.
Equals the particular open parentheses,
the particular value, minus our expected return,
which is our type of average.
F4 to lock it.
Carrot 2 times the probability.
And then I'm going to copy this down.
I'm going to add it.
Alt equals keyboard shortcut for AutoSum.
And then, because we squared it, we
need to bring the units back down.
So we'll use square root.
So we have an expected return of 0.045 with a standard deviation
of 0.08, expected return of 3.4%,
and a standard deviation of 0.21.
That is our estimate of risk.
Now, for this particular class, that's all you need to do.
I am going to show you a single cell formula.
But this is beyond the scope of the type
of formulas we do here.
Now, I want to think about this.
Look at this here in relation to here for a single cell formula.
Notice, as we copy this down, we took this whole column.
And from this whole column, we subtracted a single value.
Then we raised it to 2.
And then it's as if we're going to copy this down and add it.
We used this whole column here.
And we can illustrate that.
See the lavender and the blue box?
If I hit Enter and F2, Enter to F2,
you can see how we really used both of those columns there.
So we can come down here.
We're ultimately going to have to add all of these.
And notice we're multiplying.
So we're going to create, in the sum product, this part and then
comma, because sum product multiplies, and then
this other range of probabilities here.
Then that'll be inside the sum product.
And then inside of that, we'll give-- actually,
let's see if we can get this number first.
And then we'll just put square root on the outside.
So I'm going to say equals sum product.
And the array is going to be open parentheses
our actual returns minus our expected return.
Now, this is in the realm of array formulas,
because we have a bunch of values.
And we're subtracting a single value.
So that's what we have to use sum product.
So we close parentheses on that, square.
And that gives us this part, right here.
Now we just type a comma and go get our probabilities.
And then close parentheses on the sum product.
If I hit Enter, you can see we get that amount, right there.
And now, to get the square root, we simply say square root.
There is a square root function.
Now, to copy this over, I'm also going
to think about cell references, the only ones I
need to worry about.
These all need to move relatively.
That needs to move over.
But this one doesn't.
So I'm going to come here and hit the F4, Control Enter,
and then copy it over.
OK.
So standard deviation, a measure of risk.
It is the total risk, as we'll see later on in this chapter.
There are two types of risk.
But this is the total risk for us.
All right.
So that was a little bit--
I'm going to highlight these to unhide them, the 5 and the 14.
Right click on Hide.
We saw how to do right click.
I hid.
Right click, unhide.
We saw how to do expected return for a single stock.
And we saw how to do standard deviation for a single stock.
When we come back in the next video,
we'll see how to do expected return and standard deviation
for our portfolio stocks.
All right.
See you next video.