Build a Better Process
Build a Better Process
/ factorpad.com / fin / quant-101 / expected-return.html
An ad-free and cookie-free website.
Intermediate
Videos can also be accessed from the Quant 101 Playlist on YouTube.
Expected return and the Security Market Line (SML) Explained (18:16)
Welcome. Today's goal is to cover the Security Market Line and to see how it pushes us in the direction of decomposing return and risk into systematic and specific components.
I'm Paul, and with all of the procedures for accessing data in Excel plus all of the theoretical concepts in managing stock portfolios it is easy to get lost.
So here we will create our own Security Market Line chart so the theory sinks in, then use simple Excel functions to spot where our four stocks would sit on the chart and open a conversation on return and risk decomposition.
Some people like text with their video, so we have both. A web page with all of the formulas can be found at the first link in the Description.
Okay, here is our plan for the day.
First, we will review the CAPM model and its required inputs and assmptions.
Second, we create our own chart of the Security Market Line (SML) that will adapt to the inputs.
Third, we calculate expected returns on our four stocks.
Fourth, we introduce the concept of return and risk decomposition.
And in our next episode we will shift back to portfolios and see how each stock contributes to portfolio risk.
For Step 1, let's get our bearings with a quick review of what we know about the CAPM formula and how it helps us find expected returns on stocks.
First, in our tutorial on the CAPM Model we started with returns on one of our stocks and ran a regression against the Market.
From this we derived the formula for a line, called the Security Characteristic Line for Merck, with a slope of 1.43 and an intercept of -0.02, or 2 percent per month, as shown in Excel's default y = mx + b format.
Then our desire was to shift from the Historical Timeframe with returns that occurred in the past, to the Expected Timeframe and setting expectations for stocks using the CAPM formula.
With it all we needed from the regression was the beta and two other inputs, or assumptions that are left up to the modeler.
The first was the expected return on a Risk-Free investment. One of the proxies we gave for this was the T-bill rate, but it should really be what you as the modeler think will be the return in the future.
The second input was the Expected Equity Risk Premium which is the expected rate of return on the stock market minus the Risk-Free rate. It represents the premium offered by all risky assets, and in our case we simplified it to mean an index of stocks.
Then we plugged our figures and found the return for Merck that is theortically baked-in to market expectations of 9.64%. We needed to review the overly strict assumptions to Capital Market Theory, but once we were good knowing that CAPM is the best model we have, then we moved on to looking at the Forecast Timeframe, setting a hypothetical forecast for Merck of 11% and we saw how active portfolio managers find an Forecast Alpha, which is plugged in to portfolio optimization programs.
As we have addressed numerous times, sometimes the gap between theory and practice is wide and as we moved to the tutorial on Excel's LINEST function we circled back to two procedures that some practitioners either question, or elect to simplify.
The first was on the difference between using raw returns versus excess returns in the regression. So we tested it, rather informally and saw that the most important metric, beta, was very close, offering a reason some practitioners skip the step of removing the risk-free rate from the initial regression.
Second, the theoretically correct way to run the regression according to William Sharpe and CAPM is to run the regression using an intercept, which is the most common approach. But again, we offered some logic as to why some practitioners might accept lower certainty in their beta estimate by eliminating the intercept.
Here in Finance, the intercept refers to alpha, and a case can be made for alphas not to repeat in the future. For example, should we assume that Merck will underperform the Market by 2% on a monthly basis going forward? I don't think so.
The point of going through that exercise was to introduce two new
functions in Excel, =LINEST() and
=INDEX(). They can be used to both
customize a regression, like to eliminate the intercept, and to
run regressions over multiple cells of a spreadsheet, called rolling
regressions, which is a realy feature to have in financial models, as
we saw in the tutorial on
Time-Series Modeling.
There we created a rolling beta chart from many regressions.
In the end, the goal was to show you additional financial modeling techniques to add to your quiver, so you don't have to just duplicate what other people do, but to develop your own assumptions for your own model.
This brings us back to where we are today and the Security Market Line, and Step 2, where we define it and see how theortically the expected return for all stocks sit on that line.
What is the Security Market Line? It is a graphical depiction of the expected return to beta relationship from the CAPM formula.
On that line sit expectations for all stocks, in theory.
Let's set one up and then interpret it.
First we need to set the Expected Risk-Free Rate (RFR) of return in cell E8 and the Expected Equity Risk Premium (ERP) in cell E9.
While we're here, it'll be easier later if we name these cells, so
click on cell E8 and in the Name Box
type RFR followed by
Enter. Then use the same procedure
to name cell E9 as
ERP.
Next, down column F populate 11 hypothetical betas from 0.0 to 2.0, which is the range in which most stocks fall.
To complete the table, input the CAPM formula in cell
G8 with =RFR+F8*ERP.
Because we named these earlier, we can copy this CAPM formula down ten
rows and our table is complete.
| D | E | F | G | |
|---|---|---|---|---|
| 7 | Inputs | Beta | Expected Return | |
| 8 | Risk-Free (RFR) | 2.5% | 0.0 | 2.5% |
| 9 | Equity Risk Premium (ERP) | 5.0% | 0.2 | 3.5% |
| 10 | 0.4 | 4.5% | ||
| 11 | 0.6 | 5.5% | ||
| 12 | 0.8 | 6.5% | ||
| 13 | 1.0 | 7.5% | ||
| 14 | 1.2 | 8.5% | ||
| 15 | 1.4 | 9.5% | ||
| 16 | 1.6 | 10.5% | ||
| 17 | 1.8 | 11.5% | ||
| 18 | 2.0 | 12.5% |
From there, to create the chart, we can select the range from F8:G18, select Insert from the menu then Scatter and the chart named Scatter with Straight Lines.
Then, because we want the formula on the chart, click on the chart and under Layout select Trendline and More Trendline Options then check the box Display Equation on chart and Close. After that, edit it to suit your style.
Now that we have a Security Market Line built, we can modify the expected Risk-Free Rate (RFR) or the Equity Risk Premium (ERP) and the line will tilt and shift.
So if we expect the Risk-Free Rate to be 1%, then the whole line shifts down and the intercept is now at 1%. Notice too that the formula changes, as do all of the expected returns in column G.
We can also change the expected Equity Risk Premium to 7.0% and see the resulting change in the slope of the line.
Now, other than something fun to play with, what is the purpose?
With beta on the x-axis now we have a line that theoretically shows the expected return trade-off for every stock. So an active manager who operates in the Forecast Timeframe, by selecting their own return forecast through Fundamental, Quantitative or Technical stock selection processes will arrive at a different return expectation.
Earlier, we had settings of 2.5% for the Risk-Free Rate, 5.0% for the Equity Risk Premium and a Forecast Alpha of 11.0% for Merck when its beta was 1.43. If we plot those coordinates, then Merck would sit above this line, and it would thus be a candidate for purchase.
A stock that sits below the line is a candidate for sale. Recall each stock has its own beta and that is a takeaway from the Security Market Line and the CAPM theory.
Of course the active manager isn't taking the time to chart all stocks like this, instead they likely build this in rows and columns in a spreadsheet, or better yet, in a statistical programming language like R or Python. That said a chart like this offers an easy way to grasp what is going on and an easy way to communicate to senior management or clients.
Now for Step 3, let's confirm that our four stocks sit on this line.
First we need each stock's beta and for that we will use the easiest
method using the =SLOPE() function.
While this doesn't allow you to modify the settings of the regression,
here we will take the more theoretically correct approach using
excess returns and run the regression with an intercept.
First off, our data sits on the Returns data tab which can be downloaded sign-up free with instructions in the System Setup tutorial.
Earlier we calculated a table of Excess Returns, which is the raw return minus the risk-free return each month.
Select the range from Returns!K7:K54
and in the Name Box name it
MSFT_excess. Use the same convention
for each of the remaining columns, including the Market.
by Returns!K7:K54
Returns!L7:L54
Returns!M7:M54
Returns!N7:N54
Returns!O7:O54
With that we are ready to populate the betas across row 23. I'll show you the first and you can finish the rest in your spreadsheet.
In cell F23 for Microsoft type
=SLOPE(MSFT_excess,Market_excess) for
the dependent y-variable Microsoft and the independent x-variable the
Market. This gives us a beta of 0.64 for Microsoft.
| E | F | G | H | I | |
|---|---|---|---|---|---|
| 22 | MSFT | EBAY | ABT | MRK | |
| 23 | Beta | 0.64 | 1.75 | 0.45 | 1.16 |
| 24 | Expected Return | 5.68% | 11.27% | 4.77% | 8.28% |
Now we are ready to calculate the expected return for Microsoft in
cell F24 with
=RFR+F23*ERP making sure we have our
original two expectations of 2.5% and 5.0%, and this gives us an
expected return of 5.68% for Microsoft.
Now that the table is complete, if we were to plot Microsoft on the Security Market Line at 0.64 for beta and 5.68% for the expected return, it would sit on the line.
eBay, with a high beta of 1.75 would sit at 11.27%, again on the Security Market Line.
You can also change expectations and see the whole plot and expected returns for our stocks change.
There you have it, the Security Market Line.
Now let's take a deep breath and change gears for a moment in Step 4 and shift from this Expected Timeframe to the Historical Timeframe.
We already saw how Forecast Alphas are incorporated for an active manager looking forward. Now, looking back I'd like to take you through an exercise that will be required to kick off a conversation on an added feature of CAPM and the direction we are heading next, which is to decompose return and risk into systematic and specific pieces.
As mentioned earlier, these are given many names by practitioners and scholars, especially on the risk analysis side. Systematic risk for example is also called market risk, non-diversifiable risk, even common-factor risk.
Specific risk is often called unsystematic risk, idiosyncratic risk, non systematic risk, diversifiable risk or residual risk.
The point here is not to confuse you, but for you to be aware of other terms when they come up. I try to clear those things up in our small Glossary of Terms because half of the challenge is keeping straight what people are talking about.
Here, we stick with systematic and specific to be consistent.
The best way to think about this is with a one-stock example and what I will do first is walk through the logic and later we will come back and fill in the formulas.
I should mention that this exercise is something not often considered by academics, but is definitely part of the workflow for practitioners and portfolio accounting and performance attribution software vendors. So I think it is important to point out so you can build more practical financial models.
For this example we will use Merck, starting with the period ending 3/30/07. When we ran a regression using a 48-period lookback the beta was 1.16, and here it matches.
I will break this table into two parts, first the beginning of month regression over the previous 48-month period which in our first month was run on 3/30/07, actually on the last day of the month.
This was the information available to the active portfolio manager at the time heading into the month of April, right? It was the Expected Timeframe as we used it to set expectations.
| E | F | G | |
|---|---|---|---|
| 29 | Beginning of Month Regression | ||
| 30 | Date | Alpha | Beta |
| 31 | 3/30/07 | -0.71% | 1.16 |
| 32 | 4/30/07 | -0.54% | 1.23 |
| 33 | 5/31/07 | -0.34% | 1.24 |
As you can see, the beta changed as one month drops off and another month was added. This as we know changes the expected return for Merck and with that let's shift our focus to the Historical Timeframe one month later, so now we are looking back.
The first row of the second section refers to the historical record of performance after the month of April closed.
We will complete the rest of the table in a moment, but just go with me on this for a moment.
| H | I | J | K | L | M | |
|---|---|---|---|---|---|---|
| 29 | Beginning of Month Regression | |||||
| 30 | Date | Market Return | Predicted Return | Actual Return | Systematic Return | Specific Return |
| 31 | 4/30/07 | 6.56% | 6.88% | 16.02% | 7.59% | 8.43% |
| 32 | 5/31/07 | -0.35% | -0.96% | 1.55% | -0.42% | 1.98% |
| 33 | 6/30/07 | -4.01% | -5.33% | -4.74% | -4.98% | 0.24% |
In April, the Market, in excess return terms, was up 6.56% and given Merck's beta of 1.16, we would have expected it to return 6.88%. Instead, Merck posted a return of 16.02%.
This difference of 16.02% can be accounted for and decomposed into a systematic piece associated with the Market Return times the beta or 7.59% and the remainder is specific to Merck at 8.43% is incorprated into the error term of the regression. The total of these two is 16.02%.
Now let's move to May and I will show you all of the formulas in row 32 so you can create the rest of the table on your end.
Heading into May we use the regression from 4/30/07, with a new 48-month window, arive at a new alpha and beta. The Market returned -0.35% in May and we expected Merck to decline by -0.96% but it's actual return was 1.55%, so it outperformed again. Here the systematic return was -0.42% and the specific return was 1.98%.
=Returns!J55
=INTERCEPT(Returns!N8:N55,Returns!O8:O55)
=SLOPE(Returns!N8:N55,Returns!O8:O55)
=Returns!J56
=Returns!O56
=F32+G32*I32
=Returns!N56
=G32*I32
=K32-L32
The logical question is: why is the Systematic Return here -0.42% instead of the Predicted Return of -0.96%. Again, practitioners use different approaches, but if you recall from the regression, we can't and shouldn't assume that the alpha associated with Merck will continue into the future, so the -0.42% Systematic Return ignores this alpha. The rest we lump into the Specific Return because that was specific to the idiosyncracies of Merck at the time.
This takes us back to why I showed you multiple ways to run regressions because some practitioners run the regression from the start without an intercept, which alleviates this in the end.
By decomposing return into a systematic piece and specific piece like this we also assume that the two are not correlated, which makes them additive.
So from here, let's assume this table had 48-rows in it. From there we can calculate a stream of systematic and specific returns. Also, with respect to risk, we can compute a variance and standard deviation of both of these uncorrelated pieces that now become additive.
Recall, this is in the Historical Timeframe. If you stick around for the next tutorial we will walk through how this is done for the Expected Timeframe. This will allow us to set expectations for not only returns, but also for risk.
Now we're getting somewhere.
By way of summary, we walked through the theoretical aspects of the Security Market Line, seeing how all stocks sit on the line in the Expected Timeframe.
While yes, we set our own expectations for the Risk-Free Rate and Equity Risk Premium, and we selected our own lookback period of 48 months, these expectations in theory are the same for all investors. This comes from one of the assumptions from CAPM is says that every investor has homogeneous expectations. Again this isn't the case in the real world, but it gives you an idea as to how to build a model and some of the levers you can push and pull along with some really neat Excel skills that will allow you to build a model unique to you, yet is fundamentally sound.
We also got a vision for how risk is estimated using a Single-Index Model, the same modeling procedure we will use as we look to build an optimal portfolio right here using Excel's Solver Add-In.
In the next episode we will bring back the conversation about portfolio risk requiring our covariance matrix and keep going with this decomposition, this time focusing on expected risk and risk analysis.
As we near the end of Quant 101 start thinking about how you would build out your own financial model of stocks. We covered different methods, Excel functions, array formulas, plus have added some color as to why some practitioners use raw returns versus excess returns, and why others include or exclude the intercept.
What will you do? Remember, as long as you stick with the concepts, financial modeling gives you options to do things as you see the world. Hopefully, Quant 101 has helped you see that.
I should mention I'm gathering feedback for how we take something like this to production in another series, so chime in and connect so you don't miss that.
With that, feel free to join us at any time, and have a nice day.
To learn faster make sure you watch the videos because unlike our more code-based tutorials, much of this is easier to visualize in Excel.
Don't miss the next tutorial on our YouTube Channel.
/ factorpad.com / fin / quant-101 / expected-return.html
A newly-updated free resource. Connect and refer a friend today.