Economics assignment- data analysis and description
In this project, our task is threefold: 1) apply statistical methods to the calculation of the historical
return and risk of two stocks and the constructed portfolio using historical stock price data; 2) estimate
the cost of equity for a company using the SML approach; 3) perform a simple statistical test for the
CAPM. Please follow the instructions and submit the report with necessary explanations, findings, and
references attached, including data sources, website links, theories and models, formulas, calculation,
Excel functions, and software codes.
I. Data analysis and description
1. Download the daily Dow Jones Industrial Average and S&P 500 Index from 2017-01-01 to present.
a. Plot the two series in a graph; describe their patterns (trends, cycles, seasonality, volatility, structural
change); are their patterns similar or different? What are the underlying driving forces of these patterns?
b. Draw histogram for the two series respectively. Report the mean, median, minimum, maximum,
variance, standard deviation of each series. Can the patterns in the graph be implied by these statistics?
c. Compute the covariance and correlation of the two series for the entire sample period. Can the patterns
in the graph be explained by these two statistics? Explain.
d. Tabulate the dates when the two market indices move in opposite direction for the entire sample period
and calculate the corresponding probability. Provide economic explanation.
2. Choose two stocks A and B and download their daily prices (Pa and Pb) from 2017-01-01 to present.
a. Plot the two series in separate graphs; describe their patterns (trends, cycles, seasonality, volatility,
structural change); are their patterns similar or different? What are the driving forces of these patterns?
b. Draw histogram for the two series respectively. Report the mean, median, minimum, maximum,
variance, standard deviation of each series. Can the patterns in the graph be implied by these statistics?
c. Compute the covariance and correlation of the two series for the entire sample period. Can the patterns
in the graph be explained by these two statistics? Explain.
3. Compare individual stocks and the market index for the same period examined.
a. Compute the correlation between each stock and the DJIA Index. Which stock is more strongly
correlated with the market? Provide economic explanation.
b. Compute the correlation between each stock and the S&P 500 Index. Which stock is more strongly
correlated with the market? Provide economic explanation.
E4K Econometrics Spring 2018
Baruch College CUNY – Zicklin School of Business
II. Historical return, risk, and the risk correlation of two stocks
1. Compute the daily return series from the daily price data for the S&P Index and the stocks.
2. Annualize each daily return series (multiplying by 252). Call them Rm, Ra, and Rb.
3. For each return series, report their historical average return and risk with Excel functions.
4. Plot each return series and describe their patterns. Which series is more volatile?
5. Draw the histogram for each return series and characterize their distributions, respectively.
III. Return predictability in simple regression models
1. Compute the correlation between Rt on Rt-1 for each return series. Apply OLS regression to each return series
by running Rt on Rt-1. Report the results and explain whether stock return is predictable in the regression.
2. Run regressions of Rt on Rt-1 and Rt-2 for each return series. Report the results and check whether these results
are improved when adding Rt-2 in the regression. Explain.
3. Re-run regressions in 1 and 2 and report the heteroscedasticity and autocorrelation robust standard errors.
How are these results different from those in 1 and 2?
IV. Evaluating the historical performance of a constructed portfolio
1. Construct a portfolio consisting of the two stocks chosen. Call its return Rp. Choose five different weighting
schemes (w1=0.2, w2=0.8), (w1=0.5, w2=0.5), (w1=0.8, w2=0.2). Report the historical return and risk of the
portfolio under these schemes. Compare the portfolio return and risk with those of the market.
2. Compare the risk of the constructed portfolio with those of each individual stock. Can the portfolio
outperform individual stocks in return and risk tradeoff?
V. Estimating the cost of equity through SML approach
1. Choose a proxy for risk-free rate Rf in the market. Explain why making such choice.
2. Show a X-Y Scatter Plot in Excel for (Ri-Rf) and (Rm-Rf). Comment on the graphical relation (i=A and B).
3. Run a regression of (Ri-Rf) on (Rm-Rf) using Excel function SLOPE and INTERCEPT. Report the results.
4. Estimate the Beta coefficient through COV/VAR or TRENDLINE function. Compare with the results above.
5. Estimate the Beta coefficient for stock B and comment on its difference with stock A. Explain the difference.
6. Compute the cost of equity for each stock using SML since Beta are estimated. Comment on their difference.
7. Compare the SML cost of equity with the historical average return.
VI. Statistical test for CAPM and SML
1. Is the historical excess return per unit risk of stock A equal to that of the market? How about stock B?
2. Is the estimated excess return per unit risk of asset A equal to that of the market? How about stock B?
3. Test whether the coefficient Beta is significantly non-zero using TSLOPE function in Excel.
4. Test whether the intercept Alpha of the regression is significantly non-zero using TINTERCEPT function.
Follow the instruction carefully and answer research questions. Please email the digital copy in a single file
(PDF version is preferred) to biwei.chen@cuny.edu The subject of the email must be E4K Project. Please
double-check everything and send me the final version. Late submission and resubmission will cost students
several points. I expect all students to follow the instruction requirements, which can be found from the course
assignment webpage. Evaluation depends on the effort, quality, and neatness of the final report.
