Financial Modelling 2018-19 Project

This project is due on 28th January 2019 by 12:00 noon

Portfolio Optimisation

The project’s aim is to perform a portfolio optimisation analysis based on a portfolio of thirty UK stocks. The project is split into two parts: the first part is carried out as a group; the second part is done individually by each student and submitted separately from the group project. Further details about the submission of the work can be found below.


The data required for the empirical analysis is provided in the spreadsheet “IC206_2018-19_Project_Data_UK Stocks.xlsx”. The file contains data for 30 stocks that form your portfolio, comprising 10 years (Dec. 2007 – Dec. 2017) of monthly price and market capitalisation data for each of the stocks as well as prices for the FTSE100 market index and proxies for the risk-free rates over various sub-periods of the sample. Note that the provided risk-free rates are already transformed into monthly risk-free rates.


PART I: Group work:

a) One Excel file (per group) containing all your calculations for tasks (1)-(7) with clear detailed workings and explanations. Please present the calculations for each sub-task in a separate worksheet of the workbook. The file should be submitted in an electronic format (.xlsx) via

Blackboard, by one group member on behalf of the entire group. It should state the group number

and the group members’ student numbers.

[60 %]

b) One accompanying report (per group) that explains your workings for tasks (1)-(7), discusses your findings and answers the respective questions. Your report should include tables and graphs where relevant, and should clearly indicate what you have done and what your results mean. You should focus on providing an intuitive discussion and linking your findings to the theoretical background and relevant academic literature, where appropriate. The report should be divided into sections and sub-sections, including a short introduction and a conclusion. All references should be cited in the text and listed at the end of the report in a separate reference section.

The file should be submitted in an electronic format (Word or pdf) via Blackboard, by one group member on behalf of the entire group. It should state the group number and the group members’

student numbers. Maximum word limit: 1500 words.

[20 %]

PART II: Individual work:

c) One Excel file per student containing the calculations, workings and explanations for task (8). Please include the description of your workings and the discussion of your findings in the same

Excel file. The file should be submitted in an electronic format via Blackboard by each student individually. It should state the student’s group number and his or her student number. Maximum

word limit: 500 words.

[20 %]

Financial Modelling 2018-19 Project

Group Allocation:

There should be 5 students per group. Please designate a leader of your group. Each group leader shall send an email with the names and student numbers of all group members to Lisa Schopohl ( by Monday 12 November 2018. She will assign you a group number.

Once you have received your group number, each member of the group should self-enrol into their group on Blackboard by Friday 16 November 2018.

To do this proceed as follows:

1. Access the Blackboard site for Financial Modelling.

2. Under “TOOLS” (on the menu showing on the left), select “FM Project Group Sign-in” and click on the group number that corresponds to the one communicated to your group leader by Lisa.

3. Enrol yourself to that group.

Note: Once you enrol in a group, you cannot undo it. So be careful! If you mistakenly enrol yourself to the wrong group please contact

This is a group assignment and all group members MUST contribute. It is the responsibility of all group members to ensure that you collaborate sensibly and allocate responsibilities among group members.

If you experience any problems in your group, e.g. group members refusing to contribute to any group work, you can contact Lisa via prior to 4th January 2019. She will then initiate appropriate measures.

In addition, all students have the option to fill out an online survey after the submission of the group work to provide an evaluation of their peers in case that they feel that one or more group members have not contributed (sufficiently) to the group work. Specifically, 20% of every student’s mark on the group work tasks will be adjusted according to the average rating provided by his/her peers in the same group. Please note that you should only fill out this online survey if you have the strong feeling that one or more group members did not contribute enough and their marks should be adjusted downward. For those team members for whom you decide that they have done enough and their marks should not be adjusted downwards in any way, you do not need to provide a rating or fill in any details. Further details on the peer evaluation survey as well as the link to the survey will be provided after the submission of the project.

Financial Modelling 2018-19 Project

Task Description:

PART I: Your tasks as a group:

(1) Transform the stock prices into returns and generate the sample variance-covariance matrix for the

30 stocks.

Allowing for short-selling, find the minimum-variance portfolio and form and plot the mean-variance frontier. Plot the 30 stocks on the same graph.

Based on your findings, explain the benefits of diversification.

(2) Find the market portfolio (tangency portfolio). In a separate graph to the one of (1), plot the capital market line as a tangent to the mean-variance frontier.

Present and explain in words the compositions of the market portfolio. Do you believe that the composition of the market portfolio that you have found is a desirable or practical one as an investment? Explain why or why not, based on the positions of your stocks.

Explain how the introduction of a risk-free asset changes the optimal portfolio holdings for investors. (Hint: Think about the two fund separation theorem.)

(3) Repeat steps (1) to (2), restricting short selling.

Compare the composition of the market portfolio from (2) which allows for short selling and the one that you calculated in this part which does not allow for short selling. What has changed? Which portfolio do you find to be the more desirable or practical portfolio as an investment? Justify your answer.

(4) Divide the sample in half, i.e. first half (Jan. 2008-Dec. 2012, “in-sample period”) and second half (Jan. 2013-Dec. 2017, “out-of-sample period”). Construct equally-weighted portfolios and market capitalisation-weighted portfolios of the 30 stocks for both sub-sample periods. Compare the performance of these four portfolios on a risk-adjusted basis (using the Sharpe ratio). Discuss your results.

(Hint: For the market capitalisation weighted portfolios of the 30 stocks use the market capitalisation at the start of the in-sample period (Dec. 2007) and at the start of the out-of-sample period (Dec. 2012) as weights for the in-sample and out-of-sample periods, respectively.)

(5) Assuming that short selling is not allowed and using the first half of the data only (Jan. 2008-Dec. 2012), re-estimate the market portfolio and the minimum variance portfolio. Use the weights of the market portfolio and the minimum variance portfolio from the first half of the data and apply those to the second half of the data (Jan. 2013-Dec. 2017).

(Hint: You do not need to re-estimate the minimum variance portfolio and market portfolio for the second half of the data but you should use the weights from the first half of the data and keep these fixed for the second half of the data.)

Compare the risk-adjusted performance of the four portfolios using the Sharpe ratio. Discuss your results.

(6) How do your findings regarding the relative performance of the out-of-sample portfolios from tasks (4) and (5) differ from what you might have expected? Compare your findings with the findings of other studies in the empirical literature.

(Hint: Think about which of the portfolios should (theoretically) have the best risk-adjusted performance.)

Financial Modelling 2018-19 Project

(7) Calculate and compare the utilities of the returns from each of the above portfolios (from tasks (4) and (5)). You should justify the choice of the appropriate risk aversion parameters and discuss your results.

PART II: Your individual task:

(8) Test the CAPM based on the set of companies using the 1st pass testing and 2nd pass testing procedures, inspired by Black, Jensen and Scholes.

(Note: For the sake of this analysis, use the average risk-free rate over the entire sample period in order to calculate excess returns.)

Do your results support the CAPM? How do your results compare to earlier findings? Briefly describe the relevant literature and compare and contrast with your results.

Marking Criteria for the Project

I will take a full view of your work rather than a tick-box approach. I will award a single mark for the group work part and a single mark for the individual part. The final mark for each student is calculated by aggregating the group mark and the individual mark. 20% of the group mark might be adjusted based on peer evaluation (see explanation on p. 2 of this brief).

Feedback will take the form of the mark awarded and a set of comments discussing the strengths and weaknesses of the work.

Please note that this assignment accounts for 50% of your overall module mark.

An outstanding project is likely to embody the following characteristics:

Excel Workings

– Evidence of Knowledge

(i) Technical Level: high degree of knowledge in Excel including, e.g. the use of charts, excel tools, various functions (where appropriate) etc.

(ii) Analysis: high level of accuracy and detail and appropriate presentation of results in Excel.

– Evidence of Scholarship

(iii) Theoretical Applications: high standard of applying the concept of portfolio theory and CAPM in Excel.

– Originality and Independence

(i) Novelty: should show some elements of “uniqueness”.

(ii) Independence: should be “independent” of previous studies and of current peers.


– Presentation/Structure/Style: clear and logical structure, high standard of presentation including good grammar/spelling. Correct referencing of any existing work cited.

– Independent research: conclusions supported by some previous empirical studies with clear links how your results compare to previous findings.

– Demonstration of implications of results: Discussion of findings demonstrating a deep understanding of the subject matter, e.g. by drawing implications from the findings, highlighting limitations of the approach and its results etc.

Financial Modelling 2018-19 Project

Late submissions

The University’s penalty structure for late submissions will apply to any work submitted after the deadline.

Extensions to the deadline will only be given under very exceptional circumstances. Any request for an extension must be agreed by the ICMA Centre’s Senior Tutor (Dr Gita Persand) and must be accompanied by a completed extenuating circumstances form available from:

Plagiarism and collusion

All sources should be acknowledged in the text based on the Harvard or Oxford referencing system. Groups and/or individuals suspected of plagiarism, either of published work or work from unpublished sources, including the work of other students/groups, or of collusion will be dealt with according to University guidelines. Please note that the electronic copy of your assignment will be put through the plagiarism detection service.

Suggested Reading:

Benninga, S. (2017). Principles of Finance with Excel, 3rd Edition, Oxford University Press. (or earlier editions).

Berk, J. & van Binsbergen, J.H. (2017). How Do Investors Compute the Discount Rate? They Use the CAPM. Financial Analyst Journal, 73 (2): 25-32.

Bodie, Z., Kane, A. & Marcus, A.J. (2014). Investments, 10th Global Edition, Part II and Part III, McGraw-Hill Education: Maidenhead (UK). (especially Chapter 6).

Brooks, C. (2014). Introductory Econometrics for Finance, 3rd Edition, Cambridge University Press:

Cambridge (UK).

Fama, E.F. & French, K.R. (1996). The CAPM is Wanted, Dead or Alive. Journal of Finance, 51 (5) 1947-1958.