Question

Question 3 )Use adjusted prices for CVS Health Corporation (CVS), General Dynamics (GD), JPMorgan Chase & Co. (JPM), and Kimberly-Clark Corporation (KMB), from December 31, 1987 to December 31, 2017 to answer the following questions. You can find the prices on Yahoo Finance. Assume risk-free rate to be 2.50%.

Download month-end adjusted prices of common stocks for the four companies mentioned above. (When you download month-end price from Yahoo, it is going to show first day of the month, but the price is from the last day of the month. Only paste the month-end prices of the 4 companies in the file you submit.)(10)

Find yearly returns for 30 years from 1988 to 2017. (You need to use just the year-end prices for this. For example, return for year 1988 can be found by using adjusted price of Dec 1987 and adjusted price of Dec 1988.) (6)

Find mean annual returns for the 4 stocks. (2)

Find the covariance matrix of the four stocks using yearly returns. (assume the order of stocks to be CVS, GD, JPM and KMB. You can use any method for this.) (6)

Using the same 4 companies as in question 3, answer the following. Use answers from question 3 where necessary.

PART A) Use solver to find 11 portfolios (each portfolio is a set of weights with a given return and volatility) on the mean-variance frontier. Start with a target return of 15% and then go up to 30% with 1.5% increment (the portfolios you create should have returns equal to 15%, 16.5%, 18%, 19.5%, 21%, and so on).

PART B) Draw efficient frontier (x-axis is volatility and y-axis is return) using the return and volatility for the portfolios found in part (a). (5)

PART C) Find the optimal portfolio by maximizing the Sharpe ratio of all feasible portfolios. (5)

(Show all answers in excel and upload excel file if possible or you can email the excel file to me)

Answer 1

Yahoo Finance- Stock Quote- Set Date.

- Yearly Returns and Mean of yearly returns:

Covariance Matrix = Data - Data Analysis - Covariance

Potfolio Return = W1*E1 + W2 * E2 + W3*E3 +W4*E4

Excel = MMULT(TRANSPOSE(I11:I14),J3:J6) than Ctrl+Shift+Enter as it is an array formula

Range: I11:I14 = Weights and Range J3:J6 = Expected Returns

Standard Deviation of Portfolio: Excel:

=SQRT(MMULT(TRANSPOSE(I11:I14),MMULT(N2:Q5,I11:I14)))

Range: I11:I14 = Weights, range N2:Q5 = Covariance MAtrix.

Assighn Weight and open solver: Data-Solver:

Continuing this process by changing value of in solver to taget return.

Sharpe Ratio = (Portfolio Retune - Risk Free RAte) / Standard Deviation of Portfolio.

In Solver: Max Cell where formula is incorporated:

answered by: anonymous