Part I: (75 marks)
you will find an Excel file named Weekly data contains of historical weekly prices
from 2/01/2020 to 03/02/2022 for the 13 Week Treasury Bill (Risk-free rate, RF),
S&P500 (market), Microsoft Corp.(MSFT) Intel Corporation (INTC) and Cisco Systems
Inc. (CSCO). Data are obtained from yahoo finance website.
Required:
(A)Compute the weekly returns for S&P500, MSFT, INTC and CSCO.
(B)Compute the excess returns for S&P500, MSFT, INTC and CSCO (Hint: Excess
Return of X = Return of X – Risk Free Rate)
(C)Provide summary statistics for the excess returns of S&P500, MSFT, INTC and
CSCO and comment on the output.
(D)Conduct correlation analysis among the four variables (S&P500, MSFT, INTC and
CSCO) using correlation coefficients. Formulate the null and the alternative
hypothesis for the correlation between S&P500 and each stock. What you can
conclude?
(E)Run a scatter plot between the excess returns of S&P500 and MSFT, INTC and CSCO.
What can you conclude about linearity and how would you describe the relationship
between the variables?
(F) Check for the existence of outliers using Boxplot. What can you conclude?
(G)Check the stationarity of the four excess returns (S&P500, MSFT, INTC and CSCO).
(H)Estimate the Capital Asset Pricing Model (CAPM) parameters Betas and Alphas by
regressing each stock excess returns on the market's excess returns assuming that the
S&P500 index is the market index and comments on the estimated Betas and Alphas
coeifiencts. Does any regression give us the reason to reject the assumptions of the
CAPM? (An implication of the CAPM is that the intercept coefficient, Alpha, should
be zero. From the regression output, are the Alphas coefficients are statistically
different from zero?].
2
(I) Verify that the squared correlation between market excess return and stock excess
return is equal to R-square from regression output.
(J) Check the assumptions of the regression models including linearity, collinearity,
normality, heteroscedasticity and autocorrelation.
(K)If any regression model suffers from heteroscedasticity problem, how you will correct
for that? Explain.
(L) For each stock, determine the risk that comes from market sources and that comes
from firm-specific sources.
(M)Suppose the risk free rate today is the mean 13 Week Treasury Bill from January 2020
through February 2022, and risk premiums are constant. As a potential investor, use
the CAPM described below to estimate the expected return of MSFT, INTC and
CSCO?
𝐸(𝑅𝑖
) = 𝑅𝑓 + 𝛽𝑖
(𝐸(𝑅𝑚) − 𝑅𝑓 )
Part II: (25 marks)
The data of this part is available in the excel file named “MutualFunds.xls”. The dataset
includes the following five variables:
- Fund Type: The type of fund, labelled DE (Domestic Equity), IE (International
Equity), and FI (Fixed Income). - Net Asset Value ($): The closing price per share.
- 5-Year Average Return (%): The average annual return for the fund over the
past five years. - Expense Ratio (%): The percentage of assets deducted each fiscal year for fund expenses.
- Morningstar Rank: The risk adjusted star rating for each fund; Morningstar ranks go
from a low of 1-Star to a high of 5-Stars.
Required:
(A) Develop an estimated regression equation that can be used to predict the 5-year average
return given the type of fund. At the 5% level of significance, test for a significant
relationship.
(B) Did the estimated regression equation developed in part (A) provide a good fit to the
data? Explain.
(C) Develop the estimated regression equation that can be used to predict the 5-year average
return given the type of fund, the net asset value, and the expense ratio. At the 5% level
of significance, test for a significant relationship. Do you think any variables should be
deleted from the estimated regression equation? Explain.
3
(D) Morningstar Rank is a categorical variable. Because the data set contains only funds
with four ranks (2-Star through 5-Star), use the following dummy variables:
3StarRank=1 for a 3-Star fund, 0 otherwise; 4StarRank=1 for a 4-Star fund, 0
otherwise; and 5StarRank=1 for a 5-Star fund, 0 otherwise. Develop an estimated
equation that can be used to predict the 5-year average return given the type of fund,
the expense ratio, and the Morningstar Rank. Using 5% significance level, remove any
independent variables that are not significant.
(E) Use the estimated regression equation developed in part (D) to predict the 5-year
average return for a domestic equity fund with an expense ratio of 1.05% and a 3-Star
Morningstar Rank.
Submission guidelines
• This is an individual assignment. It comprises 50% of your final grade
• The research report must be submitted on or before week 9.
• The soft copy of the report must be submitted through BlackBoard on the
deadline as well. This would automatically generate Turnitin Report for your
manuscript.
• The title page of the report must have the name, student ID, mobile no and email
address of the student.
• Remember, the feedback on your draft (face to face) will be given until week 8.
• Please follow strict norms on plagiarism. For further details, please refer to the
student handbook.
• Use the referencing style (Harvard)