Lab for Lecture 10: The Single-Factor Model
Estimating alpha, beta, and decomposing risk into systematic and idiosyncratic components
1 Estimating MSFT’s Single-Factor Model
1.1 Data
Use the stock_returns_mag7.xlsx dataset to obtain monthly returns on Microsoft (MSFT), and the fama_french_3_factors.xlsx dataset to obtain the market excess return (Mkt-RF) and the risk-free rate (RF).
You will need to merge the two datasets by date and calculate MSFT’s excess return.
From stock_returns_mag7.xlsx:
| Column Name | Data |
|---|---|
| Date | Date at monthly frequency |
| MSFT | Monthly return on Microsoft |
From fama_french_3_factors.xlsx:
| Column Name | Data |
|---|---|
| Date | Date at monthly frequency |
| Mkt-RF | Monthly excess return on the market portfolio (market return minus risk-free rate) |
| RF | Monthly risk-free rate (1-month T-bill rate) |
1.2 Analysis
Using this data:
- Calculate MSFT’s monthly excess return: \(R_{MSFT,t} - R_{f,t}\) each month in the sample.
- Run a regression (using Excel’s Data Analysis ToolPak) of MSFT excess returns on market excess returns to estimate the single-factor model: \[R_{MSFT,t} - R_{f,t} = \alpha + \beta (R_{m,t} - R_{f,t}) + \epsilon_t\]
- Report the estimated \(\alpha\) (intercept) and \(\beta\) (slope) coefficients, along with their t-statistics and p-values.
- Confirm that the Excel INTERCEPT and SLOPE functions give you the same estimates for the \(\alpha\) and the \(\beta\) as the regression did.
- Report the regression \(R^2\). Confirm that you obtain the same result using Excel’s
RSQfunction.
- Interpret the \(\alpha\) p-value to assess whether MSFT appears mispriced relative to the single-factor model:
- Can you reject the null hypothesis that \(\alpha = 0\) (i.e., that MSFT is correctly priced) at the 5% significance level? At the 1% level? Based on the sign of the estimated \(\alpha\), do we have evidence that MSFT is overpriced (i.e., \(\alpha \leq 0\)) or underpriced (i.e., \(\alpha \geq 0\)) at the 5% level?
- Remember: You can reject the null hypothesis if the p-value is smaller than the significance level.
- Can you reject the null hypothesis that \(\alpha = 0\) (i.e., that MSFT is correctly priced) at the 5% significance level? At the 1% level? Based on the sign of the estimated \(\alpha\), do we have evidence that MSFT is overpriced (i.e., \(\alpha \leq 0\)) or underpriced (i.e., \(\alpha \geq 0\)) at the 5% level?
- Calculate the adjusted beta of MSFT using the standard 2/3 adjustment
- Calculate the single-factor beta of a portfolio consisting of 10% T-bills, 40% the market portfolio, and 50% MSFT.
1.3 Questions
- What is MSFT’s estimated beta? Is MSFT more or less sensitive to market movements than the average stock? What does this mean in practical terms?
- What percentage of MSFT’s total risk is systematic and what percentage is idiosyncratic? If you held MSFT in a well-diversified portfolio, which component of risk would you still be exposed to?
- Based on the regression results, does MSFT appear to be mispriced according to the single-factor model? How would you trade to take advantage of this finding (if at all).
- Why do we adjust betas toward 1? How does the adjusted beta for MSFT compare to the unadjusted estimate, and when would you use one versus the other?
- What is the beta of the three-asset portfolio (T-bills, market, MSFT)? How would the portfolio beta change if you increased the allocation to T-bills?