Question

PROBLEM i Create a column of monthly returns for your 2 stocks and the following 3 portfolios. Organize your spreadsheet as follows: a. Date VRSN (#1) Portfolio 1 80% in A 20% in B Portfolio 2 50% in A 50% in B Portfolio 3 20% in A 80% in B MNST (#2) S&P 500 X.x X.x Xx Xx Xx b. Calculate the historical average return and standard deviation for your stocks and the portfolios. Recall you are using historical data, so you must use the appropriate statistical function - adjustments in the divisor). All calculations can be done with Excel functions Calculate the covariance and correlation coefficients (use the statistical functions provided in the spreadsheet) for your two stocks (calculate rAB) c. d. Provide a graph of the risk-return combinations calculated above for your stocks and the portfolios. Your graph should have standard deviation on the horizontal axis and return on the vertical axis. Use a scatter plot diagram that connects the data points with a line. Be sure to use scales on the axes Now, provide a time-series graph of all data points for Stock #1, Portfolio 2, and Stock #2. Be sure the portfolio is more prominently displayed than the individual stocks. Comment on how your graph illustrates the diversification benefits of creating Portfolio 2 e. f. Discuss the shape of your graph (part d) in relationship to the correlation coefficient you calculated in part (c) g. Label your "efficient frontier" on your graph (part d). What does the efficient frontier signify? h. From looking at your graph (part d), what are the (approximate) weights for the minimum risk portfolio? You do not need to calculate it, just provide an approximation. Note, also, that the minimum risk portfolio may not (exactly) be one of those you've plotted, BUT you should pick from the five portfolios you've graphed. i. Label, unambiguously, the minimum risk portfolio (point) on your graph. one of those five portfolios This does NOT necessarily need to be j. Use the WSJ to determine the current risk-free rate (use a long-term treasury for your proxy). Be sure to think about the appropriate measure to use, and be sure the return period matches your other data (month-month, annual annual, etc.). Add this to your graph (part d). When complete explain what you used and why it's appropriate k. Draw a line from the risk-free rate that intersects with your "efficient frontier". Label this line and the intersection point. Describe the significance of the line and the point. (again, the graph created in part d)

Answer 1

For this solution I have used historical data from Yahoo Finance, from 01/01/2019.

a). To get the Data of the stock, you can visit Yahoo finance to get historical prices of any stock, index or commodities. From there the required data could be downloaded in an excel file. Out of the downloaded data, we only consider the closing value of the stock.

After downloading all the data, we need to calculate the stock returns of the stock as well as the index using the closing values. To calculate the stock return, we need to find the logarithmic returns of the stock. We use logarithmic returns of the stock because the stock price are continuous that is they are always changing. to calculate the log returns of the stock, we use the "LN" function in excel. The return for the month is calculated by "=LN(current stock value divided by previous month's stock price)". Repeat this for every month and for every stock.

using the returns obtained in above, we form the table given above.

b). The Formula to be applied are, For average return, " =Average( select the data)" , For Standard deviation use function "=stdev.s( select the data)" we are selecting the standard deviation of the sample here as these are a sample of the total data of the stocks. The returns and standard deviations are all monthly.

c). For Correlation, We use the function, "=CORREL(Stock 1 data array, Stock 2 data array)"

For Co-variance,  We use the function, "=COVARIANCE.S(Stock 1 data array, Stock 2 data array)" (again we use the sample formula)

d). In b, we have already calculated the average return and and the Standard deviation ( risk) of the stock and the portfolio. using the information, form the given table. For the scatter plot diagram, go to insert tab on excel, insert scatter plot graph. then go to design tab and select data. In the box, select legend entries, then select the required X and Y axis.

Note:- Standard deviations are used as a proxy in determining the risk associated in the stocks. In popular theory the risks associated with the stocks is the volatility of the stocks

The Excel Solution to the Question can be found in the : https://drive.google.com/open?id=1xaEt2giglgAvMpJpjcVO9aXAglHwuywY