Build a Better Process
Build a Better Process
/ factorpad.com / fin / quant-101 / capm-model.html
An ad-free and cookie-free website.
Beginner
Videos are available at one organized Quant 101 Playlist on YouTube.
How to find expected return on a stock using the CAPM Model (17:40)
Welcome. Today's goal is to see how anyone and theoretically everyone can find the expected return on a stock at any point in time.
I'm Paul, and I sometimes find it shocking how far the gap is between how investment practitioners manage stock portfolios versus how scholars believe they do.
So here we will walk through a simple regression and see what is built-in to market expectations for every stock in theory. Throughout we will see areas where this practitioner-to-academic gap exists offering suggestions for further study.
This tutorial sits at the beginning of Chapter 6 of Quant 101 where we make the theory practical for aspiring portfolio managers.
You can find a transcript for this video, including the cell formulas, on a web page. The first link in the Description will take you to it.
Here is our plan for the day.
First, we will lay out a roadmap for how to use historical market data and process it to find market expectations.
Second, we calculate beta on the stock Merck using returns in excess of the risk-free rate, called excess returns.
Third, we set macro expectations for returns on the Market and a Risk-Free rate seeing how the work of scholars helps us here.
Fourth, we put on the hat of an active portfolio manager, establish a forecast return for Merck and evaluate an alpha opportunity. So make sure you hang on for the finale.
And in our next episode we will use Excel's LINEST function to customize a regression as an active manager might.
For Step 1, let's see how we transition from the Historical to Expected timeframe.
We use a depiction of Financial Modeling Timeframes to help keep straight the many different data sets we work with when managing stock portfolios.
| Historical | Expected | Forecast |
|---|---|---|
|
|
|
The backward-looking data from the Historical period is what anyone can collect, right? Historical returns are available to everyone. It isn't until we start to process it using our own assumptions that the data becomes our own.
Here we will do just that and by doing so we will set expectations for the present timeframe to find what is baked-in to Market expectations. We call this the Expected timeframe.
Active portfolio managers compare their own forecasts against these expectations to see if a stock is a candidate for purchase or sale. We call this the the Forecast timeframe, with corresponding forecast data sets.
Passive, or Index, investors believe the market is too efficient to second guess, and they assume expected returns are good enough, so they don't bother setting their own forecasts.
In the end, the objective is to build a portfolio with the goal of maximizing the ratio of return to risk. This can be specified any number of ways but one common approach is to use the Sharpe Ratio.
Economic theory backs this up saying that every investor wants more return for less risk.
Our focus in this tutorial is on the numerator, the expected return, and we will take up the denominator, expected risk, later as risk is always more difficult mathematically.
Let's jump right in to an exercise for the stock Merck using data that sits on our Returns data tab, which can be downloaded sign-up free as shown in our System Setup tutorial.
Relevant columns from this table include dates in column B, returns for Merck in column F, the Market in column G and Rf which stands for risk-free, in column H. (The first three rows are shown in the table below)
| B | F | G | H | |
|---|---|---|---|---|
| 6 | Date | MRK | Market | Rf |
| 7 | 4/30/03 | 0.062066 | 0.072716 | 0.001000 |
| 8 | 5/30/03 | -0.044689 | 0.027079 | 0.000900 |
These returns we call raw returns and what we need for this regression is excess returns which is simply the monthly raw return minus the risk-free return.
Let's create a whole new table and call it Excess Returns first by
using =B7 in cell J7.
We will need the full table in a later tutorial in this Chapter.
Next populate columns K to O with the raw return minus the risk-free return in column H.
For example, cell N7 reads
=F7-H7 and O7 is
=G7-H7. Once row 7 is
all set copy these down 59 rows. (Below are the first three rows from
the columns we need for this tutorial)
| J | N | O | |
|---|---|---|---|
| 6 | Date | MRK | Market |
| 7 | 4/30/03 | 0.061066 | 0.071716 |
| 8 | 5/30/03 | -0.045589 | 0.026179 |
Next, let's bring a subset of these excess returns forward just so we can see all of this data in front of us.
In cell J5 we input
=Returns!J6.
Cell K5 has
=Returns!O6 and
cell L5 has
=Returns!N6.
Copy that range down 24 rows and this gives us a 2-year period of monthly returns, too short for a real study, but good enough for an illustration.
Now let's go through our justification for using linear regression to make predictions.
In previous tutorials we touched on how we can calculate a correlation coefficient on any set of data, but if we believe there is some predictive relationship between the dependent y-variable and the independent x-variable, then we can start making predictions of y, given expected changes in x.
Decades-worth of academic literature has backed up the economic rationale for returns on all stocks in the Market to be related, so if we have past returns on the Market, the x-variable here, we can gauge the degree to which a company has been and is likely to respond to changes in returns on the Market in the future.
This is how we move from historical return to expected return.
Next, let's chart this data on a scatter plot and interpret it. The data is aligned properly with the x-variable, the Market first, so let's highlight the range from K6:L29 and hit Insert, then Scatter, then Scatter with only Markers.
To add the formula for the line, click the chart, then Layout, Trendline, More Trendline Options... and check the box Display Equation on chart and Close.
The line of best fit mathematically minimizes the distance between the line itself and the dots. On the chart the formula for that line is in y = mx + b format, so the slope of the line as noted by m is 1.43. This is the beta of Merck and is what we need to compute expected returns. The b here, or the intercept of -0.02 we will address later.
When regressing excess returns this line of best fit is called the Security Characteristic Line for Merck.
In Finance, we prefer an alternative formula for the slope of this line, using y = a + bx + e because it aligns with a for alpha and b for beta, and it includes the error term which we will need later.
In an earlier tutorial on Linear Regression we discussed why it is smart to visualize charts so we can identify outliers or patterns that may make the regression not reliable for setting future expectations. So see that for more information.
Okay, with Merck's beta in our hands, let's move on.
Now for Step 3, let's touch on the CAPM Model that sits behind this procedure.
In the tutorial on Portfolio Theory we went into some detail about CAPM in the context of portfolio construction, the Separation Theorem, describing the investor's trade-off between the risk-free return and the Market Portfolio.
Here instead we use CAPM to set the expected return on a stock using the CAPM equation and for that we need two additional inputs.
Let's address that gap between theory and practice for a moment.
Multiple authors of CAPM developed the theories independently in the 1960s and 1970s and later, Bill Sharpe was awarded the Nobel Prize for his advancements in the area.
CAPM comes with a list of assumptions that are necessary to hold other variables constant for the purpose of putting out a theory.
Assumptions state that investors:
Now we know as practitioners these assumptions are strict, borderline unrealistic, and many like trading costs and taxation are flat-out not true. Additionally, according to theory, all investors perform a regression like this resulting in the same expected return for every stock.
Okay so that's the theory, so as practitioners we need to pull the most relevant parts, and for us that is the CAPM equation.
Now that we have a beta, we use the CAPM equation to set the expected return on the stock Merck. It basically reads:
The part within parentheses is called the Equity Risk Premium, a topic followed and debated by many scholars, so you may see the CAPM equation in a simplified form.
Also, because author Bill Sharpe used excess returns in the regression to derive beta this is why we took that step earlier.
Okay, so now we are clearly in the Expected timeframe and expected returns don't fall from the sky without another two inputs. First, the modeler has to set an expectatation for the Risk-Free Rate of Return, sometimes abbreviated as the RFR.
Practitioners and scholars have several methods for estimating this. Some use the prevailing interest rate on a Treasury Bill as it is deemed the safest asset.
Others with a long time frame may use the yield to maturity on a 10-year Treasury Bond because when holding a bond to maturity it provides that rate of return.
When interest rates rise though bonds lose principal value, which is why I prefer using T-bills, but a debate rages on in academic circles. This is one of the assumptions to your financial model you should make at the outset.
The second modeling assumption is the Equity Risk Premium, often abbreviated as ERP. It often falls within the range of 3% to 7%.
To set it, people often look back at average returns for stock markets for 30 or 50 year periods. For an active manager this decision can make a huge impact on portfolio construction in the end, as I will show you in a moment.
The expected Equity Risk Premium is a complex topic and one that is much-debated in academic circles. I suggest reading up on it at a later time.
There you may come across a more theoretical view referred to the Market Risk Premium when it includes assets like real estate, fixed income, art and collectibles.
With that let's calculate an expected return for Merck.
First, let's set the expected Risk-Free Return at 2.5%, the prevailing rate on T-Bills at the time.
Second, for the Equity Risk Premium I picked a conservative estimate of 5.0%.
| D | E | |
|---|---|---|
| 22 | 2.50% | Expected Risk-Free Rate of Return |
| 23 | 5.00% | Expected Equity Risk Premium |
| 24 | 7.50% | Expected Total Return on Market |
So the total expectation for the Market is the sum of 2.5% and 5%, or 7.5%.
Next, with those inputs we can find the expected return on a stock using the CAPM equation.
| Expected Risk-Free | Historical Beta | Expected Equity Risk Premium | Expected Return | |
|---|---|---|---|---|
| 27 | F | G | H | I |
| 28 | 2.50% | 1.43 | 5.00% | 8.57% |
This is self-explanatory by now. In cell F29 we carry
down =D22.
For the beta we use the =SLOPE
function with the y-variable Merck first, followed by the Market. So
cell G29 reads
=SLOPE(L6:L29,K6:K29). Again, you want
longer periods than 24 months in practice.
Next, in cell H29 pull down
=D23.
These are lined up like the CAPM equation, so I29 is
=F29+G29*H29, and the resulting
expected return for Merck is 9.64%.
Now let's move on to Step 4 and consider this from the active manager's viewpoint which is where most of this happens in the real world.
An active manager recall has a different view than the market. To implement this they now know how to determine what is baked-in to market expectations. After that, they can set their own forecasts for a stock through a combination of fundamental, technical or quantitative analysis and construct a portfolio that deviates from the Market, or technically their benchmark.
Let's create a hypothesized forecast for Merck of 11% and for our last step subtract the expected return on Merck and generate what is called a Forecast Alpha.
| H | I | J | |
|---|---|---|---|
| 32 | Forecast | Expected | Alpha |
| 33 | 11.00% | 9.64% | 1.36% |
This is just simple subtraction here, so in cell H33
input an 11% forecast, then subtract the expected return pulled
down in cell I33 and arrive at a Forecast Alpha of
1.36% in J33 with
=H33-I33.
Now imagine you performed these calculations for 2,000 stocks, each having a forecast return from an active stock selection process. The result would be 2,000 Forecast Alphas.
These can be fed into a portfolio optimizer which will trade-off return versus risk to decide on portfolio weights. It will overweight those with positive Forecast Alphas and underweight those with negative alphas given stated constraints, as we will see in Chapter 7.
For a final topic, let's cover some of those theoretical considerations and how the active manager can make them more realistic. Let's call these decision points.
First, recall that we used excess returns here for the regression. Have you considered whether the slope of the regression will differ if we didn't subtract the risk-free rate? Recall we're subtracting it from both the x-variable and y-variable. Will this make a huge difference if what we mainly need is the slope? We will go over this math in the next tutorial.
Second, what about the -0.02 alpha from the historical regression. That's 2% per month of underperformance! Should we assume that it will persist in the future?
By the book we shouldn't. Alpha in aggregate over all securities has an expected return of zero. Our regression here uses a constant for this alpha, so to be more accurate many practitioners run regressions without an intercept because you can't expect a stock's historical alpha to persist. This too we address in the next tutorial.
Third, with the processing of these alphas, we could also consider the statistics associated with this regression and incorporate confidence levels around our beta estimate into confidence levels in each stock's expected return, resulting in confidence levels in our Forecast Alpha. That's a nice feature to build into a model.
Fourth, active managers might also standardize these Forecast Alphas, or review them from a sector or industry standpoint. There are numerous layers to this analysis that an active manager takes into consideration.
Fifth, we need to agree on the measurement periods used for studies and be consistent with them. In our example here we used 24 observations, knowing it isn't sufficiently long enough and should be 60, but how do we know what other people do?
This takes us back to the point about how far apart scholars are from practitioners. Recall that the theory states that all investors have homogeneous expectations, meaning they all go through this same exercise and have the same settings. We know this couldn't be further from the truth. In the end, our expectations for Merck and all other stocks in our financial model are our own because we are building our own model.
Hopefully this highlights how active managers start to transition their own business from the Historical timeframe to one that is more forward-looking and provides points to consider as you build your own models. It also provides a nice little roadmap for future explorations here in Quant 101 and beyond, so stay tuned in.
This is getting exciting.
By way of summary, we saw how we transition from using historical data to setting expectations using a three-part process.
First, we found the Security Characteristic Line for Merck and saw how important its slope is for us. Second, we set expected returns after making forecasts for the risk-free return and the return on the Market with a view towards theory.
And finally, we saw the forecasting process through the lens of an active portfolio manager and issues that must be resolved to create an effective financial model as we look to construct outperforming portfolios.
As we move forward toward the finish line of this Quant 101 series, we cover a few more return and risk topics then gather data for an optimization right here in Excel. We also are gathering feedback and planning out future tutorials so please chime in with what you would like to see.
In the next episode we will address those points about fine-tuning our
regressions and for that we will employ Excel's
=LINEST() function.
Our following here is growing and we'd love for you to be a part of it so subscribe so you don't miss future tutorials.
Please feel free to join us any time and have a nice day.
Learn faster by pairing this text-based tutorial with the videos so you can see the visuals and catch the finer points.
I also encourage you to check out other topics covered on our YouTube Channel.
/ factorpad.com / fin / quant-101 / capm-model.html
A newly-updated free resource. Connect and refer a friend today.
by