Question
1. Go to Yahoo! finance, and download monthly return series for the five years (Jan 2014-Jan 2019) for the following three stocks: Oracle (ORCL), General
1. Go to Yahoo! finance, and download monthly return series for the five years (Jan 2014-Jan 2019) for the following three stocks: Oracle (ORCL), General Mills (GIS), and Conoco Phillips (COP). For each stock, calculate the mean monthly return for each stock, standard deviation, and list the highest two and lowest two month returns. In a couple of sentences, briefly describe (just intuitively) which stock looks the safest, and which looks the riskiest. (Hint: remember that you're downloading stock prices, but what you want to work with is stock returns.)
2. For each pair of stocks (i.e., ORCL-GIS, GIS-COP, and ORCL-COP), calculate the correlation coefficient (Excel function CORREL()) for each pair of stocks. Which two stocks have the highest correlation? Which two are least correlated?
3. For each pair of stocks, calculate the expected return and standard deviation of a portfolio with weights that vary between 0% and 100%, in 10% intervals. For example, take ORCL-GIS. Your first portfolio will be 0% in ORCL and 100% in GIS. The second portfolio will be 10% in ORCL, and 90% in GIS. Your third portfolio will be 20% in ORCL, and 80% in GIS, and so on, all the way until your final portfolio, which is 100% in ORCL, and 0% in GIS. Perform this same calculation for the other two pairs of stocks. For each portfolio, calculate the expected return and standard deviation using your estimates from question (2) above. HINT: You're going to conduct calculations for 11 portfolios, for three different pairs of stocks, for a total of 33 return-standard deviation pairs.
4. Using EXCEL or a piece of graph paper (drawing by hand), make a plot with the quantities in part (3) above. The x-axis will be standard deviation, and the y-axis will be expected return. Connect the dots within each of your three portfolios.
5. Identify with a red line/pen the set of efficient portfolios in your graph. The definition of an efficient portfolio is one that, for a given standard deviation, offer the maximum possible expected return. In other words, efficient portfolios are those for which there are no other possible portfolios that are "North" and/or "West" of them on the meanvolatility graph. HINT: The result will be a funny looking curve that connects different parts of the parabolas you graphed in part (4). What statement can you make about the set of possible portfolios that lie either underneath and/or to the right of the set of efficient portfolios?
6. Call portfolio A the 50%-50% portfolio between COP and GIS, and call portfolio B the 50%-50% portfolio between ORCL and GIS. Conduct the same exercise (0-100, 10-90, 20-80, etc.) between portfolios A and B as you did in part (3) using individual stocks. Here, the 0-100 portfolio will be portfolio A, the 10-90 will be 10% portfolio A and 90% portfolio B, etc. For each of these mixes between portfolios A and B, calculate the expected return and standard deviation. HINT: You'll want to start by calculating the monthly returns of portfolios A and B, which will give you two timeseries. Think about the returns of these portfolios just like you would think about the returns of individual stocks. For example, you have an estimate of the mean return of portfolio A, and an estimate of the volatility of portfolio A. Same for B. You can also calculate a sample covariance between the returns of portfolio A and portfolio B. You will use these quantities to trace out the curve required for this question.
7. Using a purple line, trace out the set of efficient portfolios, taking into account the new portfolios you calculated in part (6). How has this set changed, if at all, from the previous set of efficient portfolios? What is the intuition behind this change - i.e., has it improved the efficient frontier? Why does this make sense?
EXTRA CREDIT - THIS WILL BE CHALLENGING. Using solver in EXCEL, identify the set of efficient portfolios for all possible combinations of the three stocks given above. You'll want to type in the formula for portfolio standard deviation, as a function of possible weights on each of the three stocks. Then, for a given expected return, you'll want EXCEL to search (using SOLVER) for the set of weights that minimizes the portfolio standard deviation. The resulting pair of (expected return, standard deviation) is a point on the efficient frontier. Go in small increments of expected return, and trace out the new efficient frontier. How does the new efficient frontier compare to the one you drew in part (6)?
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started