Portfolio Theory

Please answer by showing results and formulas in Excel.

Download 10 years of monthly prices for 2 stocks of your choice (Tesla and Exxon). Find these series of prices on Bloomberg or on websites such as Yahoo! Finance. Begin by computing a series of monthly returns for the 2 stocks. The optimal price info to work with is the adjusted close price. This price has been adjusted for dividends and splits, so changes in adjusted price over time represent the stocks full return. Compute a third series of returns as an equal-weighted portfolio of the two stocks by averaging the returns each month. Compute a fourth series of returns as a value-weighted portfolio of the two stocks by taking the weighted average of the returns each month (use the market capitalizations as of the previous month as your weights).

1. Build a histogram in Excel of frequencies for each of the 4 series of returns (your 2 stocks separately, the equal-weighted portfolio, and the value-weighted portfolio). What do you observe? Are there any differences between the four graphs?

2. Compute the average monthly return and the standard deviation for each series of returns.

3. Calculate and report the correlation between the two stock return series. Use formulas from the Introduction to Portfolio Theory note to calculate average monthly returns and standard deviations for 25/75, 50/50, and 75/25 portfolios of the two stocks. For clarification, a 25/75 portfolio assigns 25% weight to the first stock and 75% to the second.

4. Calculate the variances and covariance of the two stock return series. Show how the variances and covariance can be used to calculate the correlation you found in question 3.

5. For each return series and portfolio (two stocks separately and three potential portfolios), graph the monthly average return on the y-axis and its standard deviation on the x-axis. [Note: Use Insert->Chart->Scatterplot.] Which of the two stocks seems better in terms of the risk return trade off? How do the stocks compare to the 25/75, 50/50, and 75/25 portfolios?