Project 2: Historical Analysis Project This is the time to apply in practice what you learned about portfolio theory. Choose two stocks from your portfolio (see attached documents) for an historical analysis. Download into a spreadsheet the weekly closing prices for these two stocks for 11/19/2012 ? 11/18/2013 (one year) from Yahoo or Google, along with the dividend amounts and dates. Do the same for the S&P 500 index. Due Date: 12/04/2013. Calculations: Calculate the following for each asset (in Excel, using the statistical functions given in parentheses): 1. Calculate the average return (AVERAGE), standard deviation of returns (STDEV), and variance of returns (VAR) for each stock and the S&P 500 index. 2. Calculate the covariance (COVAR) and correlation (CORREL) between the returns of stock 1 and stock 2. Remember to adjust the COVAR since the Excel functions divides by n rather than n-1. 3. Calculate the return and the standard deviation of a portfolio that held these two stocks in the following weights: (-30)%-130%, ?0%-100%, 10%-90%, 20%-80%, 30%-70%,?, 90%-10%, 100%-0%, ?, 130%-(-30)%. 4. Plot these portfolio returns ? standard deviation combinations (Important: choose a ?scatterplot? and not a ?line?!). 5. Calculate the weights for the minimum variance portfolio, and then solve for the return and standard deviation. Mark this on your plot. 6. Calculate the optimal risky portfolio?s weights in the two stocks, its average return, and its standard deviation. Mark this on your plot as well. 7. Calculate the Sharpe ratio for each combination. 8. Assuming the weekly risk-free rate is 0.1%, on your plot, plot or draw the optimal CAL and mark the optimal risky portfolio. 9. Plot two additional investment opportunity sets ? first assuming the correlation of your stocks was exactly 1, and second, assuming the correlation was ?1. Written Report: Address the following questions in a one-page, single-spaced written report. Note: this should be a short essay that describes your results. Assume that you are preparing a primer on portfolio composition and analysis for the new members of your investment committee. This is an opportunity to practice writing about financial results. You will be graded on the quality of your writing as well as the quality of your analysis. Do not just answer the questions! This is an essay for your investment committee. 1. Looking at the statistics for your two stocks, does the risk-return trade-off hold? Which combinations should you avoid? Why? 2. Look at the amount you invested in stock 1 and stock 2 at the beginning of the semester. Assume, for this assignment, that these stocks are your entire portfolio. a. Which combination would deliver the least amount of risk? b. Which would deliver the maximum return per unit of risk? 3. Looking at the CAL you have drawn and your calculations for the minimum risk and optimal portfolios, what conclusions can you draw about the efficiency of the combination you chose? Deliverables: 1. A hard copy of your plot and your written analysis. 2. E-mail the Excel file you worked with to 12/04/2013. Please name your stock_analysis.xls. Make sure your Excel file is well-organized.
Paper#7046 | Written in 18-Jul-2015Price : $25