Professors video instructions if the written instructions are confusing.
Class Info - https://youtu.be/ZR-4aizf5bE
Excel Info - Infohttps://youtu.be/t4Hy-lj4jjY
Project 6 Assignment Info - https://youtu.be/1XgGq2u0es4
Background: CAPM and Regression in Excel
The Capital Asset Pricing Model computes the expected return of a given stockbased on the risk-free rate (Rf), the expected return of the market (Rm), and Beta(B).Often to implement the CAPM, we use an index model and thus use realized/historical returns. Given that the CAPM is a statement about expected returns, the index model can be modified to reflect this.
The CAPM decomposes a stock’s variability into market (systematic) risk and firm-specific effects that can be diversified away.
CAPM and Regressions Project 6: CAPM Steps for CAPM Project (with S&P 500) Part 1 1. Get stock’s historical prices and from those find the stock returns 2. Get the S&P 500’s historical prices and from those find the market returns 3. Divide the RF from French by 100 to get in the same format as the returns calculated in steps 1 and 2 4. Subtract the risk free rate from the stock returns from step 3 to get your observations for y 5. Subtract the risk free rate from the market returns from step 3 to get your observations for x 6. Solve for beta with regression or slope function Options for getting Beta 1. Excel Functions 1. Slope function = Slope 1. Gives you Beta but can only be used with a one factor model (one x variable) 2. Data Analysis – Regressions 3. Scatter Plot and Trendline (Not doing in this project) Downloading and Sorting Data from French CAPM and Regressions Project 6: CAPM Diversification Why is it important? What is a portfolio? How can we achieve diversification? Diversification Market risk, systematic risk, non-diversifiable risk ◦ Risk factors common to the whole economy Unique risk, firm-specific risk, nonsystematic risk, diversifiable risk ◦ Risk that can be eliminated by diversification Expected Return and Risk for Stocks in a Portfolio Stock prices and returns should not depend on their overall level of risk Instead should primarily be determined by how a stock contributes to the risk and return of the investor’s portfolio ◦Assume investors hold a portfolio of stocks Need a measure of risk for individual stocks that measures how much risk they contribute to a portfolio Capital Asset Pricing Model (CAPM) Method for estimating expected stock returns based on a measure of how the individual stock contributes to the risk of a portfolio ? ?? = ?? + ?? ? ?? − ?? Beta (??) measures how much risk the stock contributes to portfolios in general ?? is the return on risk-free security ? ?? is the expected return on all stocks (the market, usually captured by S&P 500) Market Risk Premium (? ?? − ??) is the addition return that risky securities earn relative to the risk-free rate Risk Free Rate Opinions on what to use differ, some suggest using a short- term rate while others a middle or long-term rate Can get US Treasury Bond Rates from Yahoo/Finance, Bloomberg, St. Louis FRED data, French’s data etc. *Be careful if the data is annualized, if this is the case, you will have to divide by 12 to get the monthly returns or by 252 to get daily* We will use French’s data which is already daily Market Returns True market portfolio should contain all the market’s risky assets in proportion to their value (impossible to calculate) Simple approach is to take the returns of a major market index Could use Vanguard’s 500 Index Fund (VFINX) as a proxy for the market or use prices of S&P 500 Index (^GSPC) ◦ Use Adjusted Close Risk The appropriate risk premium of an asset is determined by its contribution to the risk of the overall portfolio Need to be compensated for systematic risk that cannot be diversified away Risk premium of an asset is proportional to its Beta Risk and Return Suppose the risk free rate is 4%, the market return is 12.6%, and a stock has a beta of 1.3. Based on the CAPM, what is the expected return on this stock? What would happen if the beta doubled? ? ?? = ?? + ?? ? ?? − ?? ? ?? = .04 + (1.3 ∗ .126 − .04 ) = 15.18% ? ?? = .04 + (2.6 ∗ .126 − .04 ) = 26.36% Beta CAPM: Beta = 1 ◦The stock has the same risk and return as the overall market Beta = 2 ◦The stock has twice the risk and twice the expected return as the overall market Beta = .5 ◦The stock has half the risk and half the expected return as the overall market Negative Betas are rare ◦ Implies that the stock’s price goes down when the overall stock market goes up CAPM Applied – What is Used for Project & for Solving for Beta ??,? − ??,? = ?? + ?? ??,? − ??,? + ??,? Where ?? = the return of security i ?? = the risk-free rate of interest ??= the return on the market portfolio Applied Solving for Beta Use historical values to get an estimation of Beta This Beta can then be applied to future estimates ◦ To solve for the expected future return of stock When solving for beta with the CAPM you will need ◦ Historical stock returns ◦ Historical Risk Free Rate ◦ Historical Market returns ◦ S&P 500 Applied Solving for Beta Impact of historical values to get an estimation of Beta ◦ The time period you select will impact your Beta Estimate Impact of the market portfolio ◦ Many different portfolios can be used to proxy for the market. Which you choose to use will impact your Beta estimation ◦ For this project we will use the S&P 500 Regression: CAPM Applied Y= mx +b Y: Here our y is the stock return for a given month minus the risk-free rate for a given month X: Is the market return for a given month minus the risk-free rate for a given month The m (or slope) is the beta that we are trying to find Steps for CAPM Project (with S&P 500) Part 1 1. Get stock’s historical prices and from those find the stock returns 2. Get the S&P 500’s historical prices and from those find the market returns 3. Divide the RF from French by 100 to get in the same format as the returns calculated in steps 1 and 2 4. Subtract the risk free rate from the stock returns from step 3 to get your observations for y 5. Subtract the risk free rate from the market returns from step 3 to get your observations for x 6. Solve for beta with regression or slope function Regression Analysis in Excel Example Download class file Run regression for ABC Corp Add Data Analysis Regression Labels and Output Regression Output For Project: Beta Beta from CAPM will be the coefficient on the Market variable (Note: label will differ depending on what you call/label the variable) Options for getting Beta 1. Excel Functions 1. Slope function = Slope 1. Gives you Beta but can only be used with a one factor model (one x variable) 2. Data Analysis – Regressions 3. Scatter Plot and Trendline (Not doing in this project) Downloading and Sorting Data from French Kenneth R. French - Data Library 5/29/20, 9:49 AMKenneth R. French - Data Library Page 1 of 9https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html Current Research Returns In August 2019, we added emerging markets portfolios to the bottom of the page. The global portfolios and factors have been renamed to developed. April2020 Last 3 Months Last 12 Months Fama/French 3 Research Factors Rm-Rf SMB HML 13.65 2.80 -1.25 -9.57 -2.51 -19.43 -2.08 -9.47 -29.43 Fama/French 5 Research Factors (2x3) Rm-Rf SMB HML RMW CMA 13.65 2.83 -1.25 2.59 -1.08 -9.57 -6.91 -19.43 -1.28 -2.04 -2.08 -15.48 -29.43 0.37 -0.89 Fama/French Research Portfolios Size and Book-to-Market Portfolios Small Value Small Neutral Small Growth Big Value Big Neutral Big Growth Size and Operating Profitability Portfolios Small Robust Small Neutral Small Weak Big Robust Big Neutral Big Weak Size and Investment Portfolios Small Conservative Small Neutral Small Aggressive Big Conservative Big Neutral Big Aggressive 16.17 12.97 18.73 14.39 10.75 14.33 21.90 12.16 16.53 14.16 12.33 14.35 17.82 13.14 17.09 12.77 12.27 15.67 -25.99 -19.00 -9.62 -25.78 -18.03 -3.28 -24.30 -19.41 -13.99 -5.45 -11.99 -13.20 -16.72 -20.53 -16.14 -8.62 -12.05 -5.11 -31.62 -17.27 -5.33 -22.93 -12.50 9.62 -27.23 -19.88 -11.99 8.42 -5.97 -7.54 -17.46 -19.51 -15.31 4.37 -3.78 4.00 U.S. Research Returns Data (Downloadable Files) Changes in CRSP Data Fama/French 3 Factors TXT CSV Details Fama/French 3 Factors [Weekly] TXT CSV Details Fama/French 3 Factors [Daily] TXT CSV Details Fama/French 5 Factors (2x3) TXT CSV Details Fama/French 5 Factors (2x3) [Daily] TXT CSV Details http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/index.html http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/biography.html http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/curriculum_vitae.html http://papers.ssrn.com/sol3/cf_dev/AbsByAuth.cfm?per_id=1455 http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html%23Research http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html%23Breakpoints http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html%23BookEquity http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html%23International http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html%23Developed http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/consul_rel.html http://www.dimensional.com/famafrench/ http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/contact_Information.html https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/Data_Library/changes_crsp.html https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors_TXT.zip https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors_CSV.zip https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/Data_Library/f-f_factors.html https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors_weekly_TXT.zip https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors_weekly_CSV.zip https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/Data_Library/f-f_factors.html https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors_daily_TXT.zip https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors_daily_CSV.zip https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/Data_Library/f-f_factors.html https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_5_Factors_2x3_TXT.zip https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_5_Factors_2x3_CSV.zip https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/Data_Library/f-f_5_factors_2x3.html https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_5_Factors_2x3_daily_TXT.zip https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_5_Factors_2x3_daily_CSV.zip https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/Data_Library/f-f_5_factors_2x3.html 5/29/20, 9:49 AMKenneth R. French - Data Library Page 2 of 9https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html Univariate sorts on Size, B/M, OP, and Inv Portfolios Formed on Size TXT CSV Details Portfolios Formed on Size [ex.Dividends] TXT CSV Details Portfolios Formed on Size [Daily] TXT CSV Details Portfolios Formed on Book-to-Market TXT CSV Details Portfolios Formed on Book-to-Market [ex. Dividends] TXT CSV Details Portfolios Formed on Book-to-Market [Daily] TXT CSV Details Portfolios Formed on Operating Profitability TXT CSV Details Portfolios Formed on Operating Profitability [ex. Dividends] TXT CSV Details Portfolios Formed on Operating Profitability [Daily] TXT CSV Details Portfolios Formed on Investment TXT CSV Details Portfolios Formed on Investment [ex. Dividends] TXT CSV Details Portfolios Formed on Investment [Daily] TXT CSV Details Bivariate sorts on Size, B/M, OP and Inv 6 Portfolios Formed on Size and Book-to-Market (2 x 3) TXT CSV Details 6 Portfolios Formed on Size and Book-to-Market (2 x 3) [ex. Dividends] TXT