Analysis: Risk and return, portfolio diversification (35% of your final grade)

Analysis: Risk and return, portfolio diversification (35% of your final grade) Here is the time to apply in practice what you learned about portfolio theory! The purpose of this assignment is to build your own efficient frontier spreadsheet. Choose two stocks from your buy-and-hold portfolio for analysis to make your work easier. Log into FACTSET/WRDS (CRSP) and Download the monthly closing prices for these two stocks for the period January 2010 December 2015 into a spreadsheet along with the dividend amounts and dates. Do the same for the S&P 500 index. Calculate the returns for each month for each of these three assets. Calculate the following for each asset (in Excel, using the statistical functions given in parentheses): average return (AVERAGE), standard deviation of returns (STDEV), and variance of returns (VAR). What is the covariance (COVAR) and correlation (CORREL) between the returns of stock 1 and stock 2? Is the average return you calculated for stock 1 and stock 2 your expected monthly return this semester? If not, what monthly return does your group reasonably expect and why? What would be the expected return and the standard deviation of a portfolio that held these two stocks in the following weights: 0%-100%, 10%-90%, 20%-80%, 30%-70%, 90%-10%, 100%-0%? Plot these portfolio returns standard deviation combinations (Important: choose a scatterplot and not a line!). Connect the dots by hand. Address the following questions (about 1 typed page): Looking at the statistics of the two stocks, does the risk-return trade-off hold? Which combinations should you avoid? Why? Look at the amount you invested in stock 1 and stock 2 at the beginning of the semester. Assume, for the moment, that these stocks are your entire portfolio. What can you say about the efficiency of the combination you chose? Which combination would deliver the least amount of risk? Use the formula for the minimum variance portfolio to get the exact weights, calculate its return and standard deviation, and mark it by hand on your plot printout. Would your results change if you had short sale restrictions: whereby (i) you cannot have a lower than -200% weight in any single stock (=50% margin requirement on short sales) and (ii) you are prohibited to short sell? Calculate your portfolio weights and the figures requested above in these two special cases separately using Excels solver. Draw in the CAL (by hand) you would choose from, if the monthly risk free rate is 0.05%. Mark the optimal risky portfolio. Calculate the optimal risky portfolios weights in the two stocks, its average return, standard deviation, and Sharpe ratio. Would your results change if you had short sale restrictions: whereby (i) you cannot have a lower than -200% weight in any single stock (=50% margin requirement on short sales) and (ii) you are prohibited to short sell? Calculate your portfolio weights and the figures requested above in these two special cases separately using Excels solver. How much money should a risk averse investor with $1,000,000 wealth put in each stock and in the risk free rate to maximize his utility, if his risk aversion coefficient is A=2.82? Calculate this for each of the 3 cases in the prior question. Also mark on your plot where the market index (i.e. S&P 500) falls. What does this suggest about your stock selection?Sources must be Factset and WR. you can create user name and password for factset website.