ECMT2130 Portfolio optimisation data
Portfolio optimisation data
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
ECMT2130
1. (0 points) Portfolio optimisation data
Andrew is able to invest in 4 risky assets. Using a sample of data he has estimated the average simple
monthly rates of return and the variances/covariances of those simple monthly rates of return for the
various risky assets.
His estimates are contained in columns A to N of this Excel spreadsheet (supplied separately), along with
the correlation matrix implied by the variance and covariance estimates. He has also included CAPM
Beta estimates for each of the assets.
Use the information in the spreadsheet to answer the following related questions in your exam. Use
the spreadsheet to perform the necessary calculations. Document the calculations clearly in the spread-
sheet by placing informative labels next to cells that contain important formulae to ensure that your
calculations are easy to review.
Upload your final Excel spreadsheet, with all of the original data, and your calculations for related
exam questions, as part of your response to the following question. The spreadsheet formulae and solver
configuration will be reviewed as part of assessing your marks for the parts of question 2 involving
optimisation calculations.
Solution:
Question 1 just supplies data for question 2.
2. (15 points) Andrew’s portfolio optimisation analysis
Answer this question using the data from question 1.
If you use the Microsoft Excel solver for parts A and C (and you should), then add a worksheet to the
Spreadsheet and set up the solver twice, once in each of two worksheets, so that markers can see the
way that you have set up the solver for both parts of this question. You will need to create your own
new column of weights in the new worksheet when you set up the solver to answer part C.
(a) (5 points) As a percentage, what is the standard deviation of the return on a fully invested portfolio
of risky assets that minimises risk while achieving an expected simple monthly rate of return of
2.5% (0.025)? A fully invested portfolio involves all wealth being invested.
(b) (5 points) As a percentage, what is the standard deviation of the return on a zero investment
portfolio of risky assets that minimises risk while achieving an expected simple monthly rate of
return of 2.5% (0.025)? A zero investment portfolio involves no net wealth being invested.
(c) (1 point) Explain the difference between the standard deviation of the return on the optimal port-
folio for part A and the standard deviation of the return on the optimal portfolio for part B.
(d) (2 points) Is the optimal portfolio in part A on the efficient frontier? Explain your answer.
(e) (2 points) Is the optimal portfolio in part B on the efficient frontier? Explain your answer.
Solution:
(a) (5 points) The standard deviation of the return on a fully invested portfolio of risky assets that
minimises risk while achieving an expected simple monthly rate of return of 2.5% is 9.6%? See
the Excel solution for details of the problem and solver configuration.
(b) (5 points) The standard deviation of the return on a zero investment portfolio of risky assets
that minimises risk while achieving an expected simple monthly rate of return of 2.5% is 11.3%?
See the Excel solution for details of the problem and solver configuration.
(c) (1 point) To achieve the high expected returns, the optimal portfolios in A and B both short
asset 3 to take a big position in asset 4. The standard deviation of the return on the optimal
portfolio for part A is lower than the standard deviation of the return on the optimal portfolio
for part B because, for portfolio B to achieve the same expected return as portfolio A, it has to
use additional leverage, making the positions in the two highest-return and highest standard
deviation assets more extreme. This is because, without investing any funds, all expected
returns on the portfolio need to be achieved through expected return differentials across assets.
(d) (2 points) The optimal portfolio in part A is on the efficient frontier, because it minimises
return variability for a given level of return while requiring the investor to be fully invested.
(e) (2 points) While optimal portfolio in part B also minimises return variability for a given level
of return, it is not on the efficient frontier because the efficient frontier is defined in terms of
fully invested portfolios.
Page 2
3. (0 points) Terri’s regression data and script
Terri estimates a linear regression, relating the monthly excess simple return on an investment asset (the
difference between the monthly simple rate of return on that asset and the contemporaneous monthly
simple risk-free rate of return) to three regressors that she thinks drive most of the variation in excess
returns:
• excess returns on the market
• anomaly1
• anomaly2
The estimated regression model is:
rit − rft = α+ β(rmt − rft) + γ1anomaly1t + γ2anomaly2t + et
where:
• rit is the rate of return on the investment asset in period t
• rft is the rate of return on the risk-free asset in period t
• rmt is the rate of return on the market portfolio in period t
• anomaly1t is the first anomaly regressor in period t
• anomaly2t is the second anomaly regressor in period t
• et is the error term in period t.
Download “terri R script.R (supplied separately), the R script that she used to obtain OLS estimates of
the model coefficients. You will need to use it to answer the next question.
Also download the data to estimate the model. It is available in this R data file called “terri data.Rds”
(supplied separately). You will need to use it to answer the next question.
Upload the R script you use to produce the answers to the following question, as part of your exam
response.
Solution:
Question 3 just supplies data for question 4.
Page 3
4. (15 points) Terri’s linear regression
As your response to this question, your handwritten or typed answer to all parts needs to be uploaded
(photo / MS Word document / or PDF).
This question uses the linear regression model, the data, and the R script from question 3.
(a) (4 points) Test whether the regression model has any explanatory power using exclusion restrictions
on all three regressors in the model specified in question 3. Conduct the test at the 5% level of
significance. In your written response, include all steps required to conduct a formal hypothesis
test.
(b) (4 points) Test whether the slope coefficients on the two anomaly regressors are equal in the model
specified in question 3. Conduct the test at the 5% level of significance. In your written response,
include all steps required to conduct a formal hypothesis test.
(c) (4 points) Test whether the two anomaly regressors are jointly significant in the model specified in
question 3. Conduct the test at the 5% level of significance. In your written response, include all
steps required to conduct a formal hypothesis test.
(d) (3 points) Test whether the slope coefficient on the ”excess returns on the market” regressor is
equal to 1 in the model specified in question 3. Conduct the test at the 5% level of significance. In
your written response, include all steps required to conduct a formal hypothesis test.
Page 4
Solution:
(a) Overall test of significance for the original regression:
1. Test the hypothesis: H0: β = γ1 = γ2 = 0 against the alternative hypothesis H1: not H0
at the 5% level of significance.
2. The test statistic, F ∗, is computed from the R-squared values of the unrestricted regression
and the restricted regression, imposing the restrictions implied by the null hypothesis. The
statistic formula is:
F =
(R2U −R2R)/Q
(R2U )/(N −K − 1)
where Q is the number of restrictions imposed under the null hypothesis (3).
3. Under H0, given that the sample is large enough (there are 227 degrees of freedom), the
test statistic is asymptotically distributed according to the F distribution. The case could
also be made that the errors are close enough to normally distributed that F distribution
may be applicable in finite samples also.
4. The test is a one-sided upper-tail test. At the 5% level, the critical value is 2.64. The
decision rule is: reject the null hypothesis if the test statistic lies above 2.64. Otherwise,
fail to reject the null hypothesis.
5. For our sample, this statistic is equal to 353.66. It is in the rejection region. Thus, we
reject the null hypothesis at the 5% level of significance. There is sufficient evidence to
warrant concluding that at least one of the three factors, excess returns on the market,
anomaly1 and anomaly2 should be included in the regression explaining variation in the
excess returns on the asset.
(b) Anomaly slope coefficient equality test:
1. Test the hypothesis: H0: γ1 = γ2 against the alternative hypothesis H1: γ1 6= γ2 at the
5% level of significance.
2. The test statistic, F ∗, is computed from the R-squared values of the unrestricted regression
and the restricted regression, imposing the restrictions implied by the null hypothesis. The
statistic formula is:
F =
(R2U −R2R)/Q
(R2U )/(N −K − 1)
where Q is the number of restrictions imposed under the null hypothesis (1).
3. Under H0, given that the sample is large enough (there are 227 degrees of freedom), the
test statistic is asymptotically distributed according to the F distribution. The case could
also be made that the errors are close enough to normally distributed that F distribution
may be applicable in finite samples also.
4. The test is a one-sided upper-tail test. At the 5% level, the critical value is 3.88. The
decision rule is: reject the null hypothesis if the test statistic lies above 3.88. Otherwise,
fail to reject the null hypothesis.
5. For our sample, this statistic is equal to 0.0047. It is not in the rejection region. Thus,
we fail to reject the null hypothesis at the 5% level of significance. There is insufficient
evidence to warrant concluding that the slope coefficients on the anomaly factors are
different.
(c) Anomaly factors exclusion joint test:
1. Test the hypothesis: H0: γ1 = γ2 = against the alternative hypothesis H1: γ1 6= 0 and/or
γ2 6= 0 at the 5% level of significance.
Page 5
2. The test statistic, F ∗, is computed from the R-squared values of the unrestricted regression
and the restricted regression, imposing the restrictions implied by the null hypothesis. The
statistic formula is:
F =
(R2U −R2R)/Q
(R2U )/(N −K − 1)
where Q is the number of restrictions imposed under the null hypothesis (2).
3. Under H0, given that the sample is large enough (there are 227 degrees of freedom), the
test statistic is asymptotically distributed according to the F distribution. The case could
also be made that the errors are close enough to normally distributed that F distribution
may be applicable in finite samples also.
4. The test is a one-sided upper-tail test. At the 5% level, the critical value is 3.04. The
decision rule is: reject the null hypothesis if the test statistic lies above 3.04. Otherwise,
fail to reject the null hypothesis.
5. For our sample, this statistic is equal to 2.60. It is not in the rejection region. Thus, we fail
to reject the null hypothesis at the 5% level of significance. There is insufficient evidence
to warrant concluding that the anomaly factors should be included in the regression.
(d) Excess return on the market exclusion test:
1. Test the hypothesis: H0: β = 0 against the alternative hypothesis H1: β 6= at the 5%
level of significance.
2. The test statistic, t∗, is computed as:
t∗ =
(βˆ − 0)
SE(βˆ)
=
0.750528
0.085608
= 8.767
3. Under H0, given that the sample is large enough (227 degrees of freedom), the test statistic
is asymptotically distributed according to the Student’s distribution. Alternatively, the
errors in the regression model are likely to be close to normally distributed given that they
aggregate many different kinds of information shocks that impact on actual returns. Thus
the test statistic may well also be distributed, under the null hypothesis, according to the
Student’s t distribution, even in finite samples.
4. The test is a two-sided test. At the 5% level, the upper critical value is 1.97 and the
lower critical value is −1.97. The decision rule is: reject the null hypothesis if |t∗| > 1.97.
Otherwise, fail to reject the null hypothesis.
5. For our sample, this statistic is equal to 8.767. It is in the rejection region. Thus, we
reject the null hypothesis at the 5% level of significance. There is sufficient evidence to
warrant concluding that excess returns on the market should be included in the regression
explaining variation in the excess returns on the asset.
(e) Excess return on the market exclusion test:
1. Test the hypothesis: H0: β = 1 against the alternative hypothesis H1: β 6= 1 at the 5%
level of significance.
2. The test statistic, t∗, is computed as:
t∗ =
(βˆ − 1)
SE(βˆ)
=
0.36
0.05
= 7.85
Page 6
3. Under H0, given that the sample is large enough (227 degrees of freedom), the test statistic
is asymptotically distributed according to the Student’s t distribution. Alternatively, the
errors in the regression model are likely to be close to normally distributed given that they
aggregate many different kinds of information shocks that impact on actual returns. Thus
the test statistic may well also be distributed, under the null hypothesis, according to the
Student’s t distribution, even in finite samples.
4. The test is a two-sided test. At the 5% level, the upper critical value is 1.97 and the
lower critical value is −1.97. The decision rule is: reject the null hypothesis if |t∗| > 1.97.
Otherwise, fail to reject the null hypothesis.
5. For our sample, this statistic is equal to 7.85. It is in the rejection region. Thus, we reject
the null hypothesis at the 5% level of significance. There is sufficient evidence to warrant
concluding that coefficient on the excess returns on the market is different from 1.