Portfolio Analysis

CLICK HERE TO DOWNLOAD THIS TUTORIAL INSTANTLY 

Data Collection:
Collect the monthly prices for TWO stocks and the S&P 500 index over the past 5 years. The stocks you pick have to be from different industries (do not pick Microsoft or Wal-Mart). The best place to get your historical price data is from Yahoo’s finance website. Use the monthly price data to calculate monthly rates of return for both your stocks and the S&P 500 index.
You will also need the risk-free rate for your data analysis. You can use the interest rate on 5-year Treasury-Bond as the risk-free rate. This can be obtained either from the Yahoo finance website or the WSJ website.
Part A: Create a Portfolio of two risky stocks.

1. Calculate the expected return (average return) and the standard deviation for both your stocks over the 5-year period.

2. Calculate the correlation coefficient between your two stocks.
3. Create a portfolio of your two stocks and calculate the standard deviation and expected return of your portfolio. (Your portfolio weights should depend on the market value of the two stocks)

Part B: Calculating Beta
1. What is a beta coefficient, and how are betas used in risk analysis?

2. Estimate the beta for both your stocks using 5 years of data.

1. In order to estimate beta you will have to do the following for each stock: a. Construct an XY scatter plot in excel that shows the stocks return on the Y-axis and the S&P 500 return (market return) on the X-axis.

b. Estimate each stocks beta by running regressions of the stock returns against the market returns. Are the betas consistent with your scatter plot? (Please show the scatter plot, regression line and the equation for each stock)
Part C: Security Market Line
1. Write out the equation of the Security Market Line (SML). Use the interest rate on 5-year Treasury bonds as the risk-free rate. Use the average returns on the S&P 500 index as the expected returns on the market.

2. Use the SML equation to calculate the required returns of both the stocks.

3. Graph the equation of the SML.
4. How do the expected rates of return compare with the required rates of return?

Show the expected rates of return and the required rates of return for each stock on the graph.

5. On the basis of the two stocks’ expected and required returns, which stock would be more attractive to a diversified investor?

6. Calculate the beta and required return of a portfolio that has $75,000 invested in one of your stocks and $50,000 invested in the other stock.

7. Suppose investors raised their inflation expectation by 2 percentage points over current estimates. What effect would higher inflation have on the SML and on the required returns of each stock? Explain and show graphically.

8. Suppose that investors’ risk aversion increased enough to cause the market risk
premium to increase by 2 percentage points (Inflation remains constant) What effect would this have on the SML and on required returns of each stock? Explain and show graphically.

Report: The report must be typed double-spaced. Please provide a cover page with the names of every one in your group, title for the project and the names of the company you picked. Your report should contain an introduction. In the introduction you can provide a brief snap shot of the company and the industry they were picked from. The introduction should also explain the project and provide a brief summary of the major conclusions. In the other sections you will present all the computations and address all the questions. Please present all the detailed calculations showing clearly all the steps through the final numbers. Your report has to include the following graphs:

9. Graph of the regression line used to calculate beta for each stock.

10. Graph of the Security Market Line.

A summary and references should be presented in the end. Cite all the sources of your data.