Part 1 and 2 please (see attached)!!

Note: For Part 2, you can install the data analysis tool pack under Excel Options, Add-Ins, AnalysisToolPak. It's the same place that has the Solver Add-in. Attached is a helpful hint document.

Business Finance Investments Fall 2015 Assignment #2 Portfolio Choice Due Date: In class October 13, 2015 The focus of this assignment is to construct portfolios. The topics correspond to Lectures 4 to 6. You will need to use a spreadsheet posted on Blackboard called hw2data.xls. This dataset contains the historical monthly returns of six stocks: Coca-Cola (KO), Eastman Kodak (EK), General Electric (GE), Dow Chemical (DOW), Johnson & Johnson (JNJ), and 3M (MMM). You can complete the assignment individually or in groups. Each group should hand in one copy of their answers. Please show your work and provide explanations where relevant. However, you do not need to print out entire spreadsheets. Your answers should be summarized in a write-up with relevant details of calculations, tables and charts (if applicable), and explanations. The assignment can be turned in during class as a printed o Word document or as a PDF le. In particular, please do not e-mail an Excel spreadsheet - if you feel that the Excel spreadsheet is relevant to providing details of your work, please copy and paste the relevant portions into your Word document. Part 1: Preferences and the Equity Premium Puzzle Suppose that you use a quadratic utility function, U = E(r) 1 A 2 , to make your nancial 2 decisions. The average historical return for small US stocks is 15.12% with a standard deviation of 33.21%. Suppose that you also use this for your estimates of E(r) and . 1. Suppose that in choosing a portfolio consisting of a risk-free asset (where rf = 3%) and small US stocks, you invest 40% of your money in small US stocks (and the rest in the risk-free asset). What does this imply about your risk aversion coecient, A? 2. If your preferences are consistent (i.e. you use the same utility function, including the same A as above), which would you prefer? (a) an asset which has E(r) = 5% and = 0 (b) an asset with E(r) = 10% and = 20% Assume that you are only investing in (a) or (b) and not mixing the two assets into a portfolio. 3. Suppose that we interview a group of investors who chose to invest 40% of their portfolio in small US stocks and 60% in the risk-free asset. We then ask them which asset from (2) that they prefer. Most answer that they prefer (b). If we believe that the investors in the group are consistent in their choices, what does this imply about the quadratic utility function? If we believe that the quadratic utility function is the correct utility function, what does this imply about the consistency of investors' preferences? 1 Part 2: Markowitz Model and Optimal Portfolios For this part of the assignment, use the data in hw2data.xls. 1. Generating some summary statistics: (a) Report the mean and standard deviation of monthly returns for each of the six stocks. (b) Report the covariance matrix of returns (6 by 6). Are the covariances between returns on these stocks generally positive or negative? Are the signs of the covariances surprising? 2. Solving the Markowitz Problem: Take the means above as the expected returns and the estimated covariance matrix above as your best estimate of the covariance between the returns of the six stocks. (a) Suppose that the target return is 0.8%. What are the portfolio weights for a portfolio with this return and the minimum possible variance? (b) Repeat for target returns of 0.9%, 1%, 1.1%, all the way to 1.8%. Report a table of portfolio weights, expected returns, and volatilities. Plot a graph of the expected return (y-axis) versus the volatility (x-axis) of the optimal portfolios. (c) Would an investor who likes higher expected returns and dislikes volatility ever invest in the portfolio constructed in (a)? Why or why not? 3. Now suppose that the risk-free rate is 0.4167%. (a) What is the optimal risky portfolio? (b) What is the expected return if we invest 50% in the optimal risky portfolio and 50% in the risk-free asset? What about 150% in the optimal risky portfolio and -50% in the risk-free asset? 2 (c) For an investor with a utility function of E(r) 1 Ar and a coecient of risk 2 aversion of 4, what is the optimal asset allocation? Part 3: Diversication Start with asset A which has an expected return of 10% and a volatility of 30%. 1. Suppose that we introduce asset B with an expected return of 10% and a volatility of 30%. The correlation between the two asset returns is 0.9. What is the optimal combination of A and B? What is the volatility of this portfolio? [Hint: The expected return of any combination is 10%, so you want to minimize the portfolio volatility.] 2. Now suppose that we introduce asset C with an expected return of 10% and a volatility of 30%. The returns of asset C are uncorrelated with both the returns of asset A and of asset B. What is the optimal combination of A, B, and C? What is the volatility of this portfolio? 3. Did the introduction of B or C have a greater eect in decreasing the portfolio volatility? Why is this the case? 2 Calculating Mean and Std Dev of Monthly Returns Company KO EK GE DOW JNJ MMM Mean Retu 0.012705 0.005222 0.010517 0.009976 0.01385 0.009573 Std Dev 0.062477 0.073712 0.065087 0.074219 0.062907 0.06113 Calculating the Covariance Matrix KO EK GE DOW JNJ MMM KO 0.003903 0.001683 0.001749 0.001682 0.001882 0.001715 EK 0.001683 0.005433 0.002041 0.002217 0.001586 0.002142 GE 0.001749 0.002041 0.004236 0.002208 0.001748 0.001855 DOW 0.001682 0.002217 0.002208 0.005509 0.001432 0.002346 JNJ 0.001882 0.001586 0.001748 0.001432 0.003957 0.001609 MMM 0.001715 0.002142 0.001855 0.002346 0.001609 0.003737