Fama french three factor model excel

FIN360 Sections 03 and 04

Excel Assignment #4

Point value: 50 points (Late work will be accepted with a 50% deduction for each day the work is late)

Name:_________________________________________________________________

Estimate Tesla's expected returns according to the CAPM, Fama-French Three Factor and Five Factor Models:

1. From Isidore’s Assignments tab, open the excel file labeled “Calculation of Multifactor Betas – Tesla Motors – for students.xls”

2. Run the same linear regression you run for Excel Assignment #3. The independent variable is the market model excess returns and the dependent variable is Tesla’s excess returns. Use the regression output to fill in the column labeled “CAPM”.

3. Using the information in the tab labeled “Variable Information”, write out the Fama and French three factor model and explain what each of the factors represents. Explain how this model is different from the Capital Asset Pricing model:

4. Run a multivariate regression in which the dependent variable is Tesla’s excess returns and the independent variables are: 1) the market’s excess returns; 2) the small-minus-big risk portfolio; 3) the high-minus-low risk portfolio. Use the regression output to fill in the column labeled “Fama-French three factor”.

5. Using the information in the tab labeled “Variable Information”, write out the Fama and French five factor model and explain what each of the factors represents. Explain how this model is different from the Capital Asset Pricing Model and the Fama and French Three Factor model. The explanation is available in the manuscript entitled A Five Factor Asset Pricing Model (not yet published).

6. Run a multivariate regression in which the dependent variable is Tesla’s excess returns and the independent varriables are: 1) the market’s excess returns; 2) the small minus big risk portfolio, 3) the high minus low risk portfolio; 4) the operating profitability risk portfolio and 5) the conservativeness risk portfolio. Use the regression output to fill in the column labeled “Fama and French five factor”.

Write-out the CAPM and Fama and French's three and five factor model equations. Under each coefficient estimate, write-out its corresponding t-statistic. Using market and risk factor information, determine the fair market return of Tesla for February 2017. Compare the fair return of Tesla from the three models against the actual return in the market for February 2017 (use the February 2017 factor data for all risk factors from Kenneth French’s data library http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html). Which of these models gives the best representation of Tesla’s actual returns?

Based on these comparisons determine whether Tesla's stock is currently over or undervalued.

Regression parameters for Tesla returns

CAPM (Same as in Excel Assignment #3)

Fama-French Three Factor

Fama-French Five Factor

Alpha

Market Beta

Size Beta (SMB)

n.a.

Value vs. Growth Beta (HML)

n.a.

Profitability Beta (RMW)

n.a.

n.a.

Conservativeness Beta (CMA)

n.a.

n.a.

Idiosyncratic Risk*

Goodness of Fit (adjusted R2)

Treynor information ratio**

* Idiosyncratic risk is the standard deviation of the market model regression residuals

** Information ratio is calculated as the square of the ratio of alpha divided by the standard deviation of market model residuals