HiI don't know how to do part4.(d) write down the CAPM model(capital market line)for each of the stock, would you please help me?

thanks!

FACULTY OF AGRIBUSINESS AND COMMERCE LINCOLN UNIVERSITY FINC601 Finance Theory and Corporate Policy Semester 2, 2017 Project 1 Portfolio Construction and the CAPM Due Date: August 16 @ 11am To be submitted in the classroom Overview: The aim of this project is for you to become familiar with statistical properties of stock market returns. This project is intended to be a learning process for you, which will give you a hands-on experience with financial data and integrate what you have learned with regards to return, variance, portfolio formation and the asset pricing model. Therefore, you are expected to go through many trial-and-errors. It provides an opportunity to explore the answers to questions such as \"what should I buy?\" and \"what portfolio should I hold?\" Please present your answers in a report (no page limits) and email the excel sheet to the lecturer as a supporting document. This project must be completed individually and submitted with a completed assessment coversheet. The project should follow the guidelines in the Faculty of Agribusiness and Commerce Style Guide. Requirement Part 1: Data Collection 1. (a) Select three stocks (stock code 000006, 000159, 000585) from the CSMAR China Stock Market Trading Database. Then download monthly dividend re-invested returns on an Excel spreadsheet for the period January 2009 to December 2015. (b) Select the SSE Composite Index 000002 from the CSMAR China Stock Market Trading Database, download daily index closing price from January 2009 to December 2015. As we have not subscribed to the monthly index return from CSMAR, we need to calculate the monthly return from daily data. The self-study resource is available to help you to get the monthly data frequency from daily data frequency. Refer to section A. Pivot Table under Helpful Resource. (Hint: get the monthly index closing price from daily data first via Pivot Table, then calculate the monthly index return) FINC601 Finance Theory and Corporate Policy 1 Part 2: Portfolio Selection 2. (a) Calculate the realised average monthly returns and standard deviations for each stock, the market index, and the equally weighted portfolio consisting of three individual stocks. (b) Compare and contrast the mean and standard deviations of these five possible investments, noting the key differences. (c) Calculate the correlation between each of the three stocks and between the market index and the equally weighted portfolio. Part 3: Visualize the data 3. (a) Using the excel template provided, construct a graph that shows the realized returns and standard deviations of all possible combinations of the three stocks. The template was developed in a fashion that starts with a two-asset portfolio, then a three-asset portfolio for students to learn the process. (Use the \"Monthly return\" and \"Graph of Portfolio Three assets\" templates in project) (b) Calculate the Sharpe Ratio for each of the possible combinations of the three stocks. (c) Locate the portfolio with highest Sharpe Ratio on the graph. (d) Locate the SSE Composite Index 000002 on the graph. (e) The risk free asset can be combined with the market portfolio to yield another set of possible investments. Assume the risk-free bond generates 0.1% return a month (0.001). Locate the risk free asset on your graph. (f) Locate the tangency portfolio in your graph. What is the return and standard deviation of this tangency portfolio? FINC601 Finance Theory and Corporate Policy 2 Part 4: CAPM model 4. (a) Explain what would be the diversification benefits from adding a risk free asset. (b) Which portfolio should be used as a market portfolio? Please use your results from earlier parts to answer this question. (c) Calculate the beta for each of the three stock using the formula i=i,m/2m (page 183 of textbook) (d) Write down the CAPM model (Capital Market Line) for each of the stocks. (e) Add the capital market line to your graph Part 5: Discussion 5. (a) The data exercise we had in the Parts 1 - 4 give you some idea of risk, return, portfolio construction and CAPM. In what way are the results consistent with what you have learned in this course? (b) In what way are the results inconsistent with what you have learned in this course? (c) In your opinion, does the CAPM hold? Summary: These steps/exercises illustrate the essential link between risk and returns as it would be viewed in a risky world where individuals can (and therefore do) hold portfolios of investments. There are a number of reasons one needs to be cautious about this assumption, and in many cases the results for past data would not be a reasonable estimate for the future. Marking Schedule: The full mark for this project is 15 and each part is equally weighted. Marking Criteria Questions and calculations (80%) - Each question is equally weighted Understanding of the question and material; Quality of data analysis and calculation. Evidence of raw data presentation; discussion of issues. Format of assignment (10%) Correct spelling and grammar; organization and articulation in creative and professional style; concise and clear writing. FINC601 Finance Theory and Corporate Policy 3 Referencing (10%) Appropriate referencing using APA style; appropriate sources used. Helpful Resources For a detailed review, one can watch the following series of videos in Lynda.com. A. Pivot Table Login \"Lynda.com\" via Library, Teaching and Learning webpage (http://library.lincoln.ac.nz/Find/Databases/), then in the \"search\