Welcome to finance and Excel video number 110.
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 finance Excel class
section.
Hey, this is our last video for this class here.
And maybe we saved the best for last.
Let's go over to our PowerPoints,
which can also be downloaded.
Last video we talked about portfolio beta, and the video
before that we talked about the reward to risk ratio.
And we talked about how we take the r expected returns
for particular asset minus the risk
free, and divided by the beta.
That gives us the return per one unit of systematic risk.
Since systematic risk is the only risk
that's rewarded in the financial markets.
This number then can be compared to the market risk premium.
But we want to take this one step further,
and we kind of did hint at this two videos ago.
In a well functioning competitive market,
prices tend to converge.
And what that means is, here's our formula
we've been using for the beta, for the asset.
But what's this?
This is the actual same ratio, but for the market.
And beta of the market is 1.
So really, all the prices, if it's competitive,
people are betting up and down, up and down,
they all tend to converge on one single line called the security
market line.
With the same reward for one unit of systematic risk.
Now that doesn't mean everyone gets exactly one unit,
because some companies are two.
It just means this is reduced down to this right
here, because this is always one.
So then we can take, in essence, this reward
here, multiply it times the beta,
and get the return that we should expect.
Now let's look at a graph of this.
Here it is.
Security market line.
Starts at risk free.
And at any particular point on here,
the slope is always going to be the same.
It's the return on the market, minus the risk free.
And again this is for well functioning financial markets,
like the New York Stock Exchange.
But at this point right here, this is beta equals 1.
We already saw that there's some betas less than 1,
some greater than 1.
There's even some that are negative.
But right here, that equals exactly the expected return
on the market.
Now if we go way up here to 2, we
would just take the slope, multiply by 2,
and that would be the return that we would expect
for an asset with a beta of 1.
Now let's take this right here.
And if we transform it, we're going
to get something called the capital asset pricing model.
So here's our equation set up here.
We simply get rid of beta.
We multiply both sides, we're left with this.
We have the minus risk free over here,
so we simply add it to here.
And lo and behold.
The capital asset pricing my model
gives us a model that we can use to figure out
what the expected return for an asset at a given level of beta,
which is our systematic risk, should be.
We look out in the market.
We get this number right here.
We look at some proxy for risk free.
Plug that in there.
And all we need is our beta.
And we saw that we can calculate that based on past data.
We're going to do calculation over in Excel.
Here's the capital asset pricing model.
SML and CAPM tell us the minimum return
we should expect at a given systematic risk level.
Again, we just plug that beta in,
and hopefully we can get reliable information
for these inputs from the markets.
Let's look at a chart down here.
Here's the SLM slope if the market rate was 9%.
So the slope is going to be 9% market
risk premium for one beta, meaning the market.
has a beta of 1.
So the going rate.
This will give us the going rate.
Risk free is 2%.
In our example over in Excel, we'll do this.
But what's going to happen is we have this line here.
And all assets trading in this well functioning market,
where this information is assimilated quickly
into prices, everything should price along here.
If there is anything above or below,
it shouldn't last for too long.
Now here's the deal.
Let's look at this first.
Here's the market 1.
And I did the calculation right here.
2% plus 9% equals 11%.
So if you have a beta of 1, you should
expect to get a return of 11%.
Now beta of 2, we say the risk free 2% plus 9% times 2.
So it would get a return of 20.
So at beta 2, this security market line
would tell you that the expected return should be 20%.
Now if we plot and something's above.
Now look at this line, we go like this and over.
Notice that the rate is higher than the line.
So when there's a higher rate, that
is the discount rate in essence.
And that means that the stock is undervalued.
Higher rate, lower price.
So it's going to be undervalued in relation to this line.
If it's underneath, it's going to be exactly the opposite.
Overvalued.
Let's go over and make a calculation.
And guess what.
Two videos ago we did the reward to risk ratio.
We did the same exact example here.
But now we're just going to use the CAPM.
When we did the risk to reward ratio,
we compared the risk reward ratio directly
to the market risk premium.
Now with CAPM, we're going to do something slightly different.
We're going to calculate the expected return
for this particular asset using CAPM.
And then we're going to compare it to this right here.
So let's calculate this.
We got our risk free, so I'm going to click that.
And then plus the risk premium on the market times the beta.
Now right here, we've already calculated this.
So I'm simply going to take this 9 and then times, that's
the market risk premium, times the beta.
So it's going to give this little bit right
here is going to be 1 times that plus 0.2 extra,
because this stock A is a little bit
more systematic risk than the average asset in the market.
And boom, what do we get?
12.8.
So this return using the market inputs,
and you could say those are the market inputs,
and the beta, which is from past data, it should be 12.8.
But our estimate is 12.5.
No way are we going to buy this asset.
Notice that we came to the same conclusion
when we used our risk reward ratio.
It's just that we calculated this reward to risk ratio
and compared it directly to the market risk.
But we still said no way.
Here we're calculating using market data.
We've estimated that it's only 12.5.
So no way are we going to buy.
Over here, we're going to do the same calculation.
Risk free plus, and using the market information,
this is the premium on the market, times the beta of 0.6.
So the return should be 7.4.
So the market says it should be giving everyone 7.4,
but our calculations say 9.
Then we are definitely going to buy this.
All right, now let's look over at our chart.
We've estimated 9.
If we look at this plot up here, the market line says it's 7.4.
We think it's going to give us more
return than the market does.
That means it's a good buy.
That means we think the stock is undervalued.
The opposite is true for this other one.
This is the plot, although it's pretty close to the line,
it's hard to see.
The market returns for beta, according
to the systematic risk, should be 12.8.
We've calculated 12.5.
So no way.
This is less than this one.
That means we are not going to buy, it's overvalued.
That means it's underneath.
So 12.5 is what we've estimated.
If you move it up a little bit on the line,
that would be that 12.8.
And so that one is overvalued.
All right, that's it for Chapter 11.
And that's it for this finance and Excel series.
We'll see you next series.