Lab for Lecture 13: Cost of Capital Estimation
Estimating MSFT’s cost of equity (CAPM and Fama-French) and weighted average cost of capital
Estimating Microsoft’s Cost of Capital
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), the SMB and HML factors, 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) |
| SMB | Monthly return on the Small Minus Big factor |
| HML | Monthly return on the High Minus Low factor |
| RF | Monthly risk-free rate (1-month T-bill rate) |
Analysis
Step 1: Calculate MSFT Excess Returns
- Merge the two datasets by date.
- Calculate MSFT’s monthly excess return: \(R_{MSFT,t} - R_{f,t}\) for each month in the sample.
Step 2: Estimate CAPM Beta
Use the SLOPE function in Excel to estimate the market beta of MSFT from a regression of MSFT excess returns on market excess returns: \[R_{MSFT,t} - R_{f,t} = \alpha + \beta_m (R_{m,t} - R_{f,t}) + \varepsilon_t\]
Step 3: Estimate Fama-French Three-Factor Betas
Use the LINEST function in Excel to estimate the factor betas from a multiple regression of MSFT excess returns on all three Fama-French factors: \[R_{MSFT,t} - R_{f,t} = \alpha + \beta_m (R_{m,t} - R_{f,t}) + \beta_{SMB} \cdot SMB_t + \beta_{HML} \cdot HML_t + \varepsilon_t\]
Step 4: Estimate MSFT’s Cost of Equity
Using both models, estimate MSFT’s cost of equity. For the inputs, use:
- Risk-free rate: Use the current yield on 10-year U.S. Treasury notes (look this up on FRED or the U.S. Treasury website).
- Factor risk premia: Calculate the historical average of each factor over the sample period (i.e., the mean of
Mkt-RF,SMB, andHMLfrom the dataset). Annualize by multiplying the monthly averages by 12.- Calculate both the arithmetic mean and geometric mean, first using the last 10 years, then using the entire sample from the “fama_french_3_factors” data file (you will have four estimates for each factor risk premium, and hence four estimates for each cost of equity calculation below)
CAPM cost of equity: \[r_E^{CAPM} = R_f + \hat{\beta}_m \times \overline{MktPrem}\]
where \(\hat{\beta}_m\) is your estimated market beta from Step 2 and \(\overline{MktPrem}\) is the annualized risk premium of Mkt-RF (i.e. the market risk premium).
Fama-French cost of equity: \[r_E^{FF} = R_f + \hat{\beta}_m \times \overline{MktPrem} + \hat{\beta}_{SMB} \times \overline{SMB} + \hat{\beta}_{HML} \times \overline{HML}\]
where the betas are from Step 3 and \(\overline{SMB}\), \(\overline{HML}\) are the annualized risk premia of the SMB and HML factors.
Step 5: Estimate MSFT’s Cost of Debt and Tax Rate
To calculate WACC, you need estimates of MSFT’s cost of debt and marginal tax rate. Use the following approach:
- Cost of debt (\(r_D\)): Microsoft currently holds a AAA credit rating. Using the credit spread table from the lecture, add a spread of approximately 0.5%–0.8% to the 10-year Treasury yield you looked up in Step 4. Use the midpoint (0.65%) as your credit spread, so: \(r_D = R_f + 0.65\%\). Alternatively, you can look up the yield to maturity on MSFT’s outstanding bonds (e.g., via FINRA TRACE or a financial data provider).
- Tax rate (\(T_c\)): Use the U.S. federal statutory corporate tax rate of 21%. This is a reasonable approximation of MSFT’s marginal tax rate for new investment decisions.
Step 6: Estimate MSFT’s Capital Structure Weights
Look up MSFT’s current:
- Market value of equity (\(E\)): Shares outstanding \(\times\) current stock price (available from any financial website, e.g., Yahoo Finance).
- Market value of debt (\(D\)): Total long-term debt from MSFT’s most recent balance sheet (available in the company’s 10-K filing or on financial websites like Yahoo Finance). Book value of debt is an acceptable approximation.
Calculate the equity and debt weights: \[\frac{E}{V} = \frac{E}{E + D} \qquad \frac{D}{V} = \frac{D}{E + D}\]
Step 7: Calculate WACC
Using the WACC formula, calculate MSFT’s weighted average cost of capital twice—once using the CAPM cost of equity, and once using the Fama-French cost of equity:
\[WACC = \frac{E}{V} \times r_E + \frac{D}{V} \times r_D \times (1 - T_c)\]
- Report both WACC estimates and compare them.
Questions
- How does MSFT’s estimated market beta from the CAPM regression compare to the market beta from the Fama-French regression? Why might they differ?
- What are MSFT’s SMB and HML factor loadings? Based on their signs, does MSFT behave more like a large-cap or small-cap stock? More like a growth or value stock? Are these loadings consistent with what you know about Microsoft?
- How do the CAPM and Fama-French cost of equity estimates compare? Which factors drive the difference? Which estimate do you think is more appropriate for MSFT, and why?
- How does the \(R^2\) of the three-factor regression compare to the single-factor regression? What does this tell you about whether the SMB and HML factors help explain MSFT’s returns?
- Given MSFT’s capital structure (high equity weight, low debt weight), how much does the WACC differ from the cost of equity? Why is this the case?
- How sensitive is your WACC estimate to the equity risk premium? Recalculate the CAPM-based WACC using an equity risk premium that is 2 percentage points higher than your baseline. How much does WACC change?