CAPM, Diversification, & Using Excel to Calculate Betas

1. CAPM, Diversification, & Using Excel to Calculate Betas The Capital Asset Pricing Model (CAPM ) • Equilibrium model that underlies all modern financial theory • Derived using principles of diversification with simplified assumptions • Markowitz, Sharpe, Lintner and Mossin are researchers credited with its development (early 1970’s) • Basic result: By holding many securities in a well-diversified portfolio, investors all but eliminate “diversifiable” (aka “unique” or “unsystematic”) risk. But they are still exposed to “un-diversifiable” (aka “market” or “systematic”) risk. This risk is measure by beta. The “market portfolio” has a beta of 1. • Securities or portfolios with beta = 1 have same systematic risk as the market. • If beta > 1, more systematic risk. If < 1, less systematic risk.

2. CAPM The Capital Asset Pricing Model (“CAPM”) [ Rs- Rf ] = b0 + b1 [ RM - Rf ] + e • People commonly refer to the b0 in this model as the stock’s “alpha” and the b1 is simply called “beta.” • “R” stands for return. The subscript “s” indicates that the model is for stock “s” (e.g., Coca Cola or Microsoft). We can also let “s” be a portfolio or ETF or mutual fund. The subscript “f” stands for the risk-free security (e.g., a 30-day Treasury bill). The subscript “M” stands for the stock market. • [ Rs- Rf ] is the risk premium of a stock; [ RM - Rf ] is the risk premium of the market. • The “e” designates the error term. • Betas can be calculated for individual securities, ETFs, mutual funds, and portfolios.

3. Theoretical CAPM Portfolio Betas Beta can be calculated using a regression (we’ll show how on later slides). Beta for a portfolio, ETF, or mutual fund also can be calculated from the betas of its holdings: Beta for a Portfolio of Securities = (w1* beta1 ) + (w2* beta2 ) + … Where w1 is the dollar-weight of the 1st security and beta1 is its beta, w2 is the dollar-weight of the 2nd security and beta2 is its beta, etc. For example, we’ll use this approach in Assignment 5 to calculate the beta for a the sub-portfolio of legacy stocks in the Regents’ Portfolio. We’ll also use it to perform a “what-if” analysis of the change in beta if we sell some of the legacy stocks. We’ll calculate the betas for individual securities using historical weekly return data.

4. The security market line (SML) and a positive alpha stock:- The SML is a graphical representation of the expected-return-beta relationship of the CAPM. The SML is determined by 2 points: the risk-free rate and the market’s expected return.- Alpha (a) is the abnormal rate of return on a security in excess of what would be predicted by an equilibrium model such as the CAPM. In this example: The stock has a beta of 1.2. The market’s expected return is 14%. The stock’ s expected return is E(r) = 6%+1.2(14%-6%)=15.6%. (Note: this is just our formula from slide 2. The risk-free rate = 6%. Suppose the market’s return actually is 14%. If the stock’s actual return is 17%, then the stock’s a = 1.4%

5. Example of actual returns for a Well-Diversified Portfolio: The points are the actual returns of all the securities held in the portfolio. The line is the SML. There are about as many points above as below the SML. Expected Return Line or “SML”

6. Example of actual returns for a Well-Diversified Portfolio: The points are the actual returns of all the securities held in the portfolio. The line is the SML. There are about as many points above as below the SML. Expected Return Line or “SML” • * If markets are “efficient”: • Stocks are priced “right” and it is very difficult to find positive alpha stocks. • Actual returns do not equal expected returns because “stuff” happens. • We refer to “stuff” as diversifiable risk.

7. Scatter Diagram and OLS Regression to Calculate Beta for a Stock This graph plots Dell’s excess return (i.e., its return minus the risk-free rate) versus the excess return of the market (e.g., the S&P 500). Each point on the graph is for a different year. Sometimes people confuse this graph with the graph of the Security Market Line.

8. Scatter Diagram and OLS Regression to Calculate Beta for a Stock How is the Security Market Line (SML) different from this OLS regression used here to calculate Beta from historical return data? 1) SML relates E(r) to b 2) CAPM and our OLS regression relates security’s excess return to the market portfolio’s excess return; the points on the graphs are returns for Dell over different weeks, months, etc. Suggestion: compare the X-Axis on this graph to the X-Axis for the earlier slide that showed Security Market Line

9. Estimating the CAPM beta of individual stocks • Use monthly or weekly historical data • Use Ordinary Least Squares (OLS) Regression to regress risk premiums for individual stocks or portfolios against the risk premiums for the S&P 500 • The slope of the regression line is the beta

10. OLS Regression Models & Using Excel to Calculate CAPM Betas Some Problems with CAPM Beta: • Timeframe (frequency of returns and historical time period used) for the regression of the historical data greatly impacts our estimate of beta. • As company’s change business or conditions change, the company’s beta changes. • The CAPM is based on expected conditions, but we only have historical data to use to estimate beta. Warren Buffet example: Warren Buffet is a “value” investor. He tries to buy stocks when they are “cheap”. Several years ago Bank of America was trading for $6/share. Its beta (using 2 years of historical data) was almost 3. A beta of 3 is very high (suggesting buying BofA was very risky). Buffets said buying BofA at $6 wasn’t very risky but rather good value. He said backward-looking beta was not useful for estimating BofA’s future risk. Suggestion: calculate BofA’s beta after that.

11. Calculating CAPM Betas From slide #2, the Capital Asset Pricing Model (“CAPM”) is: [ Rs- Rf ] = b0 + b1[ RM - Rf ] + e To estimate b0 ( “alpha”) and the b1 (CAPM “beta”), you estimate the model (i.e., perform the OLS regression): y = b0 + b1 x + e Note that [ Rs- Rf ] is “y” and [ RM - Rf ] is “x”. Don’t reverse them!  Two-Step Process: • Step 1: Estimate the model (i.e., perform the regression). • Step 2: Determine whether the model is good in a statistical sense.

12. OLS Example: Can outdoor temperature be estimated using cricket chirps? The Data: Go outside some evening with a thermometer. Count the number of cricket chirps you hear for 15 seconds. Repeat on various evenings when the temperature is different.

13. Can outdoor temperature be estimated using cricket chirps? Descriptive statistics: You can use Excel’s formulas for mean and sample standard deviation.

14. Can outdoor temperature be estimated using cricket chirps? Plot the data: Can use Excel’s XY (Scatter) chart type. Temperature is “y”. Chirps is “x”.

15. Can outdoor temperature be estimated using cricket chirps? Specify the model of the relationship between chirps and temperature: y = b0 + b1 x + e • Temperature is “y”. • Chirps is “x”. • “e” is “error”. This error is the difference between our model’s prediction of temperature and the temperature we actually observed. • We’ll estimate b0 and b1 using ordinary least squares (“OLS”) regression. • If our model’s any good, once we’ve estimated b0 and b1 , we can use the following formula to estimate temperature just by counting cricket chirps: Temperature = b0 + (b1 )( # of chirps) + 0 • Note: our model assumes that “y” and “x” have a linear relationship --- i.e., our model is the equation of a line with intercept b0 and slope b1 .

16. How will we determine the best line? We’ll use Excel to perform an ordinary least squares (OLS) regression. ? ? ?

17. Can outdoor temperature be estimated using cricket chirps? Estimate the model (i.e., perform the regression): y = b0 + b1 x + e • We’ll need Excel’s “Analysis ToolPak” Add-In. You may already have added it. If not, here’s how you do it: • For Excel 1997-2003: On the tool bar, click “Tools”, “Add-Ins”, “Analysis ToolPak”. • For Excel 2007: On the upper left-hand corner “MS” logo, click “Excel Options”, “Add-Ins”, “Analysis ToolPak”. • If you’ve added the “Analysis ToolPak” Add-In., you’ll be able to: • For Excel 1997-2003: On the tool bar, click “Tools”, “Data analysis”, “Regression”. • For Excel 2007: On the tool bar, click “Data”, “Data analysis”, “Regression”. >>>>

18. Can outdoor temperature be estimated using cricket chirps?

19. Can outdoor temperature be estimated using cricket chirps?

20. Can outdoor temperature be estimated using cricket chirps? y = b0 + b1 x + e = 42.997 + 0.906 x + e

21. Best estimate: Temperature = 42.997 + (0.906)(Chirps) + e. In other words, the best line intercepts the y-axis at about 43, and it has a slope of about 0.9.

22. For each of our temperature observations, we can determine how much our model’s prediction for temperature is in error. “Predicted y” = b0 + b1 x + e = 42.997 + 0.906 x + 0 Error (“e”) = “Predicted y” – “Actual y”. For 1st observation, “Predicted y” = 42.997 + (0.906)(18) = 59.30. Error = 59.30 – 57 = 2.30.

23. For each of our temperature observations, we can determine how much our model’s prediction for temperature is in error. “Predicted y” = b0 + b1 x + e = 42.997 + 0.906 x + 0 Error (“e”) = “Predicted y” – “Actual y”. For 1st observation, “Predicted y” = 42.997 + (0.906)(18) = 59.30. Error = 59.30 – 57 = 2.30. Ordinary least squares (OLS) finds the line that minimizes the sum of the squared errors (e2).

24. Is the model any good? Take another look at the regression output.Model: Temperature = b0 + ( b1) (Chirps) + e. Best estimate: Temperature = 42.997 + (0.906)(Chirps) + 0. If our coefficient b1 is 0, then knowing chirps doesn’t help us predict temperature. How can we tell whether b1 is different ENOUGH from 0? In this example: Is 0.906 different from 0? There might be lots of variance in the data! Suppose sometimes chirps are 0 and sometimes 1,000.

25. We can tell how good the model is in a statistical sense by examining the Adjusted R Square (R2) of the regression and the t-statistics and p-values of the coefficients. Adjusted R2 = 94.8%. This means our model has explained 94.8% of the variance of Temperature. You could look up these T-stats in a statistical table to determine whether they were significantly different from 0. It’s easier just to use the P-values. The p-values are the probability of obtaining our estimates of b0 and b1 if b0 and b1 are in reality equal to 0. If our coefficient b1 is 0, then knowing chirps doesn’t help us predict temperature. How can we tell whether b1 is different ENOUGH from 0?

26. Determine whether the model is good in a statistical sense. The p-value of each of the coefficients is less than 5%. Therefore, we say each coefficient is significantly different from zero at the 5% level.The p-value of the F-stat tells us how good the model is overall. It also has a p-value less than 5%. Therefore, we say the regression is significant at the 5% level.

27. Suppose the p-value of the coefficient for CHIRPS had been > 5%. We would have concluded that it was NOT significantly different from zero at the 5% level.In that case, we would have to conclude that our best model for estimating temperature would be: Temperature = b0 . And b0 would just equal the average of our Temperatures (67 degrees). We would have to conclude that CHIRPS are of no help for determining outdoor temperature.

28. CAPM looks like the Temperature-Chirps model: Temperature = b0 + b1 (Chirps) + e. The Capital Asset Pricing Model (“CAPM”) [ Rs- Rf ] = b0 + b1 [ RM - Rf ] + e • People commonly refer to the b0 in this model as the stock’s “alpha” and the b1 is simply called “beta.” • [ Rs- Rf ] is the risk premium of a stock; [ RM - Rf ] is the risk premium of the market. • The “e” designates the error term.

29. In case you were curious: the word “ordinary” comes from the fact that OLS assumes that the errors are continuous random variables drawn from a normal distribution with a mean of zero.That’s the reason earlier we wrote: Temperature = b0 + (b1 )( # of chirps) + 0. The error term equals 0 on average. 0.