finish the request

User Generated

Yvafra

Business Finance

Description

Stock Project – Fall 2018

(You can work individually or in groups of up to 3)

Part A: Due in class exactly one week before Final Exam

_________________________________________________________________________

PART A: (10% of course grade)

Analysis of risk and return, portfolio diversification

Here you will apply what you have learned about portfolio theory. Use the monthly-adjusted closing prices for IBM, MSFT, And the S&P500 during the five-year period from January 2013 – December 2017 in the file “Stock Project Stock Prices” posted on Canvas. Calculate returns for each month for each of these three assets (Stock 1; Stock 2; S&P 500).

Exercise 1:

Calculate the following for each asset (in Excel, using the statistical functions given in parentheses): average return (AVERAGE), standard deviation of returns (STDEV.S), and variance of returns (VAR.S). What is the covariance (COVAR.S) and correlation (CORREL) between the returns of stock 1 and stock 2?

Exercise 2:

Calculate the return and standard deviation of a portfolio that holds these two stocks in the following weights: 0%-100%; 10%-90%; 20%-80%; 30%-70%; 40%-60%; 50%-50%; 60%-40%; 70%-30%; 80%-20%; 90%-10%, 100%-0%. Plot these portfolio return / standard deviation combinations. Make sure return is on the vertical axis and standard deviation is on the horizontal axis. (Important: use a scatterplot) (You may use excel for this part)

Exercise 3

1. Which specific combination would deliver the least amount of risk? Use the formula for the minimum variance portfolio (show your work) to get the exact weights, calculate its return, standard deviation and Sharpe ratio (show your work), and mark it by hand on your plot printout.The monthly risk free rate is 0.15%.

2. Draw in the CAL (by hand) that gives you the best risk-return combinations, given that the monthly risk free rate is 0.15%. Mark the optimal risky portfolio. Calculate the optimal risky portfolio’s weights (show your work) in the two stocks (using the book’s formula 6.10). For this optimal portfolio, calculate the average return, standard deviation, and Sharpe ratio (show your work).

3. Mark the spot on your return / standard deviation plot where the market index (i.e. S&P 500) falls.

4. For a moment, assume the correlation between the two stocks equals exactly 1. Graph the investment opportunity set. (Hint: This does not require any additional excel work or calculations)

5. Now assume the correlation between the two stocks equals exactly –1. Again, graph the investment opportunity set. Make sure to be precise, and provide your calculations. If you would like, you can perform steps 4 and 5 on the same graph.

What to hand in: A hard copy of your plot, results, and detailed analysis --- this means you should clearly show all of your work! Write out all equations that you use, with the exception of those that are functions in excel such as AVG, COVAR, VAR, etc. For example, you need to write out the equations for portfolio return, portfolio standard deviation, and show all your calculations in determining the MVP and ORP.

Unformatted Attachment Preview

Date 1/1/2013 2/1/2013 3/1/2013 4/1/2013 5/1/2013 6/1/2013 7/1/2013 8/1/2013 9/1/2013 ######## ######## ######## 1/1/2014 2/1/2014 3/1/2014 4/1/2014 5/1/2014 6/1/2014 7/1/2014 8/1/2014 9/1/2014 ######## ######## ######## 1/1/2015 2/1/2015 3/1/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/1/2015 9/1/2015 ######## ######## ######## 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 IBM Adj Close 166,326 164,4913 175,4404 166,5902 171,0975 157,9257 161,1733 150,6207 153,7904 148,8324 149,2227 156,6118 147,5192 154,6079 161,6008 164,9422 154,7755 153,0674 161,8493 162,3813 161,2436 139,6431 137,7489 137,2121 131,1144 138,495 138,2263 147,519 146,1065 142,2341 141,6482 129,3188 127,8178 123,5064 122,9245 122,4756 111,0575 116,6108 136,1604 131,2067 138,2193 139,0961 147,1974 145,6028 146,8326 MSFT S&P500 Adj Close Adj Close 23,72848 1498,11 24,03102 1514,68 24,93597 1569,19 28,84938 1597,57 30,41822 1630,74 30,31555 1606,28 27,94578 1685,73 29,31498 1632,97 29,41548 1681,55 31,29815 1756,54 33,70231 1805,81 33,31668 1848,36 33,69962 1782,59 34,11821 1859,45 36,77871 1872,34 36,24931 1883,95 36,73384 1923,57 37,67971 1960,23 38,99896 1930,67 41,0501 2003,37 42,15208 1972,29 42,68853 2018,05 43,47047 2067,56 42,50029 2058,9 36,96473 1994,99 40,12137 2104,5 37,46738 2067,89 44,82079 2085,51 43,18055 2107,39 40,94774 2063,11 43,31279 2103,84 40,36343 1972,18 41,32045 1920,03 49,1439 2079,36 50,74033 2080,41 52,1444 2043,94 51,77785 1940,24 47,82096 1932,23 52,28214 2059,74 47,20822 2065,3 50,17115 2096,95 48,77763 2098,86 54,03002 2173,6 54,77355 2170,95 55,24923 2168,27 ######## ######## ######## 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 9/1/2017 ######## ######## ######## 142,0629 149,9476 154,8244 162,7806 167,7241 163,7104 150,6899 143,4887 146,0296 137,334 135,7772 139,1784 147,7931 143,054 148,6502 57,47454 57,80066 60,00659 62,43042 61,78342 63,98444 66,5104 67,8511 67,35087 71,0345 73,05708 73,17127 81,70744 82,67991 84,44764 2126,15 2198,81 2238,83 2278,87 2363,64 2362,72 2384,2 2411,8 2423,41 2470,3 2471,65 2519,36 2575,26 2584,84 2673,61 Date 1/1/2013 2/1/2013 3/1/2013 4/1/2013 5/1/2013 6/1/2013 7/1/2013 8/1/2013 9/1/2013 ######## ######## ######## 1/1/2014 2/1/2014 3/1/2014 4/1/2014 5/1/2014 6/1/2014 7/1/2014 8/1/2014 9/1/2014 ######## ######## ######## 1/1/2015 2/1/2015 3/1/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/1/2015 9/1/2015 ######## ######## ######## 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 IBM Adj Close 166,326 164,4913 175,4404 166,5902 171,0975 157,9257 161,1733 150,6207 153,7904 148,8324 149,2227 156,6118 147,5192 154,6079 161,6008 164,9422 154,7755 153,0674 161,8493 162,3813 161,2436 139,6431 137,7489 137,2121 131,1144 138,495 138,2263 147,519 146,1065 142,2341 141,6482 129,3188 127,8178 123,5064 122,9245 122,4756 111,0575 116,6108 136,1604 131,2067 138,2193 139,0961 147,1974 145,6028 146,8326 MSFT S&P500 Adj Close Adj Close 23,72848 1498,11 24,03102 1514,68 24,93597 1569,19 28,84938 1597,57 30,41822 1630,74 30,31555 1606,28 27,94578 1685,73 29,31498 1632,97 29,41548 1681,55 31,29815 1756,54 33,70231 1805,81 33,31668 1848,36 33,69962 1782,59 34,11821 1859,45 36,77871 1872,34 36,24931 1883,95 36,73384 1923,57 37,67971 1960,23 38,99896 1930,67 41,0501 2003,37 42,15208 1972,29 42,68853 2018,05 43,47047 2067,56 42,50029 2058,9 36,96473 1994,99 40,12137 2104,5 37,46738 2067,89 44,82079 2085,51 43,18055 2107,39 40,94774 2063,11 43,31279 2103,84 40,36343 1972,18 41,32045 1920,03 49,1439 2079,36 50,74033 2080,41 52,1444 2043,94 51,77785 1940,24 47,82096 1932,23 52,28214 2059,74 47,20822 2065,3 50,17115 2096,95 48,77763 2098,86 54,03002 2173,6 54,77355 2170,95 55,24923 2168,27 ######## ######## ######## 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 9/1/2017 ######## ######## ######## 142,0629 149,9476 154,8244 162,7806 167,7241 163,7104 150,6899 143,4887 146,0296 137,334 135,7772 139,1784 147,7931 143,054 148,6502 57,47454 57,80066 60,00659 62,43042 61,78342 63,98444 66,5104 67,8511 67,35087 71,0345 73,05708 73,17127 81,70744 82,67991 84,44764 2126,15 2198,81 2238,83 2278,87 2363,64 2362,72 2384,2 2411,8 2423,41 2470,3 2471,65 2519,36 2575,26 2584,84 2673,61
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hello, I have attached docx and p...


Anonymous
I was struggling with this subject, and this helped me a ton!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags