Welcome to Excel in finance video number 105.
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 finance Excel class section.
In this video, we're going to talk
about expected returns and standard deviation
for our portfolio of stock.
Last video, we saw how to calculate
expected return, which is our average when making estimates
for the future, and standard deviation for individual stock.
And in that video, we calculated, here's stock A,
here's stock B. In the last video,
we took each individual estimated
return times the probability and added them.
But in this video since we're going to the expected
return for the portfolio, we're going
to have to calculate a total return we expect
for each state of the economy.
So like this.
And then we can calculate our expected return
from that, and our standard deviation for the portfolio.
But first, if we have two stocks, A and B,
we need to know how much we have invested in each.
We need to know what weight for the whole portfolio is A,
and what weight is B. So we're simply
going to take this amount divided
by the total and this amount divided by the total.
And that'll give us our weights.
I'm going to say equal that amount for A. Now
I don't have the total in the cell.
So I'll just use the sum function,
highlight both, and hit F4 to lock.
That way I can copy this to the side,
and will give me the weight.
So 40% invested in that, and 60% in that.
Now, for this particular state right
here, we're not going to use the probability because we're
estimating for the state.
We're saying, hey, if it is a recession, what's
the return for the portfolio?
So in this case we say the estimated return for stock
A times the weight.
Plus the return here times the weight.
And now, when I copy this formula down,
I need these two cell references to copy down.
But these need to be locked, so I'm
going to hit the F4 key there, click on this cursor
right there, and F4 to lock that one.
Control Enter.
And now I can copy this down.
Now we have our estimated return for each particular state.
Now we can multiply these times the probability
to get the expected return for the portfolio.
And then we'll also use these in our standard deviation
calculation.
So now over here, we could do it the long way.
Equals this times this.
Plus this times this.
And you know, that is how it's done.
But notice there's a column of values
here, column of values here.
They're exactly the same dimension,
which means there's three rows in one column.
So we can use them the sum products.
So I'm going to click escape equal sum product.
It's made for exactly this.
Array one is the first items to multiply and then add.
Remember product means multiply, sum means add.
They named this function nicely.
And then I take estimated returns for their portfolio
if the state occurs.
And there's my expected return for the portfolio.
Now we need to calculate the standard deviation.
Now similar to last video.
It's the same concept, except for instead
of the individual stock amount calculated,
the standard deviation for the individual stock,
we're doing it for the portfolio.
So we have to calculate deviation first.
And that's always the actual value
minus the average, or the expected value.
And that it needs to be locked.
That's the deviation.
That tells us how far each individual item
is from the average.
Then for our standard deviation calculation
we take the deviation and square it.
Now instead of adding these all up and dividing by the count
we're doing, we have probabilities here
which are, in essence, weights.
A type of weighted average.
So I say this times the probability.
Now that formula work all the way down.
Control Enter.
And drag it down.
Now when you add these all up, and I
neglected to mention this in the last video,
it's called variance.
Now really standard deviation is the one used most often.
So I kind of usually just skip over that.
I say add them all up, even though it's called variance.
Really, you're adding up a bunch of values
that have been squared.
So it's not in the original unit.
So it's always once you add it up,
you have to get it back down to the same unit.
So you take the square root.
Square root function SQRT.
And then we have our standard deviation for the portfolio.
Now for this class, that is pretty much
how you want to do it.
I am going to show you two formulas where
you can do all these calculations in two single cell
formulas.
Not required for this class, but anyone else watching
is interested.
I'm going to take the sum product for expected return.
Now remember, what we did here is
we took this column times each one of these weights,
and then ultimately down here multiplied it by this.
But actually, watch this.
If you know how to do mixed cell references,
this is the best way to understand
how this formula will work.
And it's quite amazing.
Let's go ahead and highlight all these.
And we want to multiply this times this,
and that result times that.
And if we could have a single cell
formula we could copy down and over,
it would be like this equals this.
Relative cell reference all the way
through the range times this.
Now this one needs to be locked going to the side,
but not down.
So we hit the F4 key one, two, three times.
Times, and then this one.
This needs to be locked, I want to go down.
This whole column needs to be locked.
But when we copy the formula from here over to here,
it needs to move to this next weight.
So I lock the row reference by hitting F4 twice.
Now Control Enter will populate all those cells.
And if I add this all up, we get our expected return.
Now that series of calculations, you
could see there's kind of three different arrays here.
There's a range of cells that's three rows by two columns.
And then another one we're multiplying
that's one column by three rows.
And another one that's two columns by one row.
Well wouldn't you know it, you can actually
just use sum product.
Now sum product will not allow you
to separate these three different arrays with commas,
because they have to be the same dimensions.
But no problem.
You can simply multiply that times this.
And guess what, you can do it in any order.
And it works just fine.
That formula right there will work just fine.
Now if you ever were to have numbers here,
like maybe you had no for no return,
then none of the formulas would work.
But with that caveat aside, I'm going to control z.
That is just an awesome formula.
Now standard deviation in a single cell
gets a little bit more complicated.
But let's think about what we did.
We first did this calculation.
And if we look at this as we go down,
it looks like this whole column times that
and this whole column times that.
So we can certainly simulate these vertically aligned values
inside of our formula.
Let's look over here.
We then from each one of these subtracted this.
Well if we're going to do it in this cell right here,
we can just tell it to look there.
We square it.
And then look what happens to this one as we go down.
This cell range keeps changing, which
means ultimately we then multiply
the result times this column.
So we can do this whole formula in one cell.
Now I'm going to do this one first,
and then we'll just put square root on the outside.
Some product.
And we're going to need to square it,
so I'm going to open parentheses.
And now I'm going to simulate this column.
This times this, plus this column times this.
And now I'm going to highlight this and hit the F9
key to evaluate, and just see if we get the same thing as this.
Sure enough, we get exactly that.
Control z to undo it.
Now we need to subtract from it the expected value.
Each one of these gets subtracted the expected value.
So I'm going to minus this.
And that'll work just fine, because the order of operations
will multiply first, and this addition will be done first.
So no problem.
We can just subtract it.
We are going to have to close parentheses there.
And carrot 2.
And we don't have values to compare to yet,
but we can just multiply times this probability.
And we should get exactly these values right here.
So I'm going to highlight all of this, including that
parentheses there, and hit F9.
And sure enough, we get exactly those values there.
Control z.
And now I'm going to close parentheses on the sum product.
And that adds them to get this.
Now we just add a square root around that.
Now I do have another video at YouTube
I made a number of years ago about how to do this.
And if you don't mind doing multiplication inside of sum
product, this formula is just awesome.
A little bit simpler than that other one
they did a couple years back.
All right.
Expected return and standard deviation
for a portfolio of stocks, given just two stocks.
And some estimates for states of the economy.
All right, we'll see you next video.