Assignment
First, calculate the following 60-month return statistics on each of the ten stocks identified above and on the value weighted market-wide index:
1. Mean 60-month rate of return
2. Variance of the 60 monthly rates of return (although the 60 months of returns are obviously returns sampled from the population of returns, you can assume that the 60 monthly returns are the entire population of returns for each stock and for the index);
3. Standard deviation of the 60 monthly rates of return and
4. Correlation coefficients between monthly rates of return over the 60 months for every possible pairing of two return series, including the return series for the value weighted market-wide index.
Second, rank the ten stocks from lowest to highest standard deviation of returns. Based on this ranking, identify ten portfolios as follows:
1. Portfolio 1 -- a one stock portfolio comprised of the stock with the lowest standard deviation of returns
2. Portfolio 2 -- a two stock portfolio formed by adding to Portfolio 1 the stock with the next lowest standard deviation of returns and so on through
3. Portfolio 9 -- a nine stock portfolio formed by adding to Portfolio 8 the stock with the second highest standard deviation of returns and
4. Portfolio 10 -- a ten stock portfolio formed by adding to Portfolio 9 the stock with the highest standard deviation of returns.
Third, calculate the monthly rates of return for each of the above ten portfolios, formed as equally weighted portfolios of the stocks in each portfolio and calculate the monthly rates of return for just the fourth of the above ten portfolios, formed as a value weighted portfolio of the stocks in the fourth portfolio.
Fourth, calculate the average mean return, average variance of returns and average standard deviation of returns for the stocks in each of the ten portfolios (10 average means, 10 average variances and 10 average standard deviations, one for each of the ten portfolios).
Fifth, for the equally weighted portfolios, calculate the portfolio mean return, portfolio variance of returns and portfolio standard deviation of returns for each of the ten portfolios and then do the same for the fourth value weighted portfolio.
All references to portfolios from here on are references to portfolios with equally weighted investments in each of the stocks in the portfolios.
Sixth, calculate the correlation coefficients for monthly returns for each of the ten securities against the monthly returns for Portfolio 10 (10 correlation coefficients).
Seventh, calculate the covariance of monthly returns for each of the ten securities against the monthly returns for Portfolio 10 (10 covariances).
Eighth, provide a one sentence summary of any relationship you see between a stock's covariance of returns (against the returns of the portfolio in which it is held) and the correlation coefficient between that stock's returns and the returns for the portfolio in which the stock is held.
Ninth, calculate the average of the ten security covariances (against Portfolio 10) calculated in the previous task and compare that average covariance of returns to the variance of returns for Portfolio 10.
Tenth, provide a one sentence summary of what you think the relationship is between portfolio variance and the covariances (against the portfolio) for the stocks in the portfolio.
Eleventh, calculate the ratio of each covariance (against Portfolio 10) to the variance of returns for Portfolio 10 and calculate the average for the 10 stock ratios.
Twelfth, provide a one sentence statement summarizing what you think the relationship is between the ratios calculated in the previous step and the "betas" for each of the ten stocks, where by "beta" we mean the beta measure you have learned about in your finance course.
Thirteenth, build a chart (in its own tab you added in your G3 (x) file) showing both the average of the stock standard deviations and the portfolio standard deviations for Portfolio 1 through Portfolio 10, with the standard deviation on the y-axis and portfolio numbers on the x-axis.
Add information to this chart, in any way you think useful, to denote what happens to the ratio of portfolio standard deviation of returns/average standard deviation of returns for the stocks in the portfolio as we move for the one stock Portfolio 1 to the ten stock Portfolio 10.
Fourteenth, provide a one sentence statement summarizing what you think the relationship is between the portfolio risk and the average risk for the stocks in the portfolio as more stocks are added to the portfolio
Task
1. Calculate Mean, standard deviation, Correlation coefficient and draw a graph using the results.
2. Calculate the rest like covariance. etc . Mean, Variance and has already been populated for
3. Use input from G2 files and move the data to G3 using excel formula [no direct copy paste from G2 to G3 should be used. So that the reader can understand the date clicking on the cells in G3 knowing from where the data is populated.
Format your assignment according to the give formatting requirements:
1. The answer must be double spaced, typed, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the course title, the student's name, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The references and Citations should follow APA format. The reference page is not included in the required page length.
Attachment:- G3x.rar