Answered step by step
Verified Expert Solution
Link Copied!
Question
1 Approved Answer

As an assistant to Rachel, you are asked to collect the monthly prices of each stock from Yahoo Finance from 11/23/2010 to 11/2/2015 (market booming

As an assistant to Rachel, you are asked to collect the monthly prices of each stock from Yahoo Finance from 11/23/2010 to 11/2/2015 (market booming time period), and then finish the following tasks: (Johnson and Johnson, IBM, Boeing) Please show the formula and/or work

1. Calculate monthly returns for each stock

2. 3. Calculate the variance and average return for each stock

4. Construct the covariance matrix of these stocks

5. Use Excel Solver function to build the efficient frontier (with and without short sale)

6. Determine the optimal risk portfolio

7. Draw a graph showing the efficient frontier and the CAL line (one graph for the case with short sale and one graph without short sale)

Boeing

Date Open Close
11/2/2015 148.380005 148.399994
10/1/2015 131.320007 148.070007
9/1/2015 128.160004 130.949997
8/3/2015 144.440002 130.679993
7/1/2015 140.479996 144.169998
6/1/2015 141.449997 138.720001
5/1/2015 144.410004 140.520004
4/1/2015 149.970001 143.339996
3/2/2015 150.850006 150.080002
2/2/2015 143.720001 150.850006
1/2/2015 131.070007 145.369995
12/1/2014 134.309998 129.979996
11/3/2014 125.349998 134.360001
10/1/2014 127.370003 124.910004
9/2/2014 125.620003 127.379997
8/1/2014 120.449997 126.800003
7/1/2014 127.660004 120.480003
6/2/2014 135.5 127.230003
5/1/2014 128.740005 135.25
4/1/2014 126.239998 129.020004
3/3/2014 127.18 125.489998
2/3/2014 124.239998 128.919998
1/2/2014 136.009995 125.260002
12/2/2013 134.990005 136.490005
11/1/2013 130.899994 134.25
10/1/2013 117.970001 130.5
9/3/2013 105.129997 117.5
8/1/2013 105.989998 103.919998
7/1/2013 102.93 105.099998
6/3/2013 99.360001 102.440002
5/1/2013 91.080002 99.019997
4/1/2013 85.599998 91.410004
3/1/2013 76.699997 85.849998
2/1/2013 74.489998 76.900002
1/2/2013 76.550003 73.870003
12/3/2012 74.410004 75.360001
11/1/2012 70.589996 74.279999
10/1/2012 69.860001 70.440002
9/4/2012 71.5 69.599998
8/1/2012 74.32 71.400002
7/2/2012 74.209999 73.910004
6/1/2012 68.669998 74.300003
5/1/2012 76.589996 69.610001
4/2/2012 74 76.800003
3/1/2012 74.959999 74.370003
2/1/2012 74.959999 74.949997
1/3/2012 74.699997 74.18
12/1/2011 68.699997 73.349998
11/1/2011 64.230003 68.690002
10/3/2011 60.099998 65.790001
9/1/2011 66.959999 60.509998
8/1/2011 71.610001 66.860001
7/1/2011 73.809998 70.470001
6/1/2011 77.949997 73.93
5/2/2011 80.349998 78.029999
4/1/2011 74.290001 79.779999
3/1/2011 71.93 73.93
2/1/2011 70 72.010002
1/3/2011 66.150002 69.480003
12/1/2010 64.660004 65.260002
11/23/2010 63.43 63.77

IBM

Date Open Close
11/2/2015 140.5 140.369995
10/1/2015 145.309998 140.080002
9/1/2015 144.910004 144.970001
8/3/2015 161.699997 147.889999
7/1/2015 163.970001 161.990005
6/1/2015 170.210007 162.660004
5/1/2015 173.199997 169.649994
4/1/2015 160.229996 171.289993
3/2/2015 161.940002 160.5
2/2/2015 154 161.940002
1/2/2015 161.309998 153.309998
12/1/2014 161.639999 160.440002
11/3/2014 164.25 162.169998
10/1/2014 189.910004 164.399994
9/2/2014 192.679993 189.830002
8/1/2014 190.5 192.300003
7/1/2014 181.699997 191.669998
6/2/2014 184.759995 181.270004
5/1/2014 196.309998 184.360001
4/1/2014 193.119995 196.470001
3/3/2014 183.330002 192.490005
2/3/2014 176.020004 185.169998
1/2/2014 187.210007 176.679993
12/2/2013 179.460007 187.570007
11/1/2013 179.809998 179.679993
10/1/2013 185.339996 179.210007
9/3/2013 183.630005 185.179993
8/1/2013 196.649994 182.270004
7/1/2013 192.149994 195.039993
6/3/2013 208.25 191.110001
5/1/2013 201.869995 208.020004
4/1/2013 212.800003 202.539993
3/1/2013 200.649994 213.300003
2/1/2013 204.649994 200.830002
1/2/2013 194.089996 203.070007
12/3/2012 190.759995 191.550003
11/1/2012 194.679993 190.070007
10/1/2012 208.009995 194.529999
9/4/2012 196.610001 207.449997
8/1/2012 196.960007 194.850006
7/2/2012 196.360001 195.979996
6/1/2012 190.119995 195.580002
5/1/2012 207.179993 192.899994
4/2/2012 208.960007 207.080002
3/1/2012 197.229996 208.649994
2/1/2012 193.210007 196.729996
1/3/2012 186.729996 192.600006
12/1/2011 187.009995 183.880005
11/1/2011 181.550003 188
10/3/2011 174.360001 184.630005
9/1/2011 172.710007 174.869995
8/1/2011 182.600006 171.910004
7/1/2011 171.610001 181.850006
6/1/2011 168.899994 171.550003
5/2/2011 172.110001 168.929993
4/1/2011 163.699997 170.580002
3/1/2011 163.149994 163.070007
2/1/2011 162.110001 161.880005
1/3/2011 147.210007 162
12/1/2010 143.610001 146.759995
11/23/2010 144.240005 141.460007

Johnson and Johnson

Date Open Close
11/2/2015 101.18 102.169998
10/1/2015 93.43 101.029999
9/1/2015 92.290001 93.349998
8/3/2015 100 93.980003
7/1/2015 98.300003 100.209999
6/1/2015 100.279999 97.459999
5/1/2015 99.620003 100.139999
4/1/2015 100.459999 99.199997
3/2/2015 102.510002 100.599998
2/2/2015 100.489998 102.510002
1/2/2015 105.050003 100.139999
12/1/2014 107.889999 104.57
11/3/2014 107.830002 108.25
10/1/2014 105.980003 107.779999
9/2/2014 103.300003 106.589996
8/1/2014 99.760002 103.730003
7/1/2014 105.150002 100.089996
6/2/2014 101.540001 104.620003
5/1/2014 100.769997 101.459999
4/1/2014 98.309998 101.290001
3/3/2014 91.129997 98.230003
2/3/2014 88.75 92.120003
1/2/2014 91.139999 88.470001
12/2/2013 94.410004 91.589996
11/1/2013 92.379997 94.660004
10/1/2013 86.589996 92.610001
9/3/2013 86.889999 86.690002
8/1/2013 94.160004 86.410004
7/1/2013 85.720001 93.5
6/3/2013 84.279999 85.860001
5/1/2013 84.5 84.18
4/1/2013 81.389999 85.230003
3/1/2013 75.790001 81.529999
2/1/2013 74.139999 76.110001
1/2/2013 71.019997 73.919998
12/3/2012 69.800003 70.099998
11/1/2012 71.099998 69.730003
10/1/2012 68.919998 70.82
9/4/2012 67.489998 68.910004
8/1/2012 69.480003 67.43
7/2/2012 67.419998 69.220001
6/1/2012 62.040001 67.559998
5/1/2012 64.910004 62.43
4/2/2012 66.040001 65.099998
3/1/2012 65.110001 65.959999
2/1/2012 65.970001 65.080002
1/3/2012 65.589996 65.910004
12/1/2011 64.68 65.580002
11/1/2011 63.68 64.720001
10/3/2011 63.650002 64.389999
9/1/2011 65.660004 63.689999
8/1/2011 65.279999 65.800003
7/1/2011 66.480003 64.790001
6/1/2011 67.019997 66.519997
5/2/2011 65.919998 67.290001
4/1/2011 59.630001 65.720001
3/1/2011 61.25 59.25
2/1/2011 59.700001 61.439999
1/3/2011 62.630001 59.77
12/1/2010 62.18 61.849998
11/23/2010 62.990002 61.549999

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image
Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Sport Funding And Finance

Authors: Bob Stewart

2nd Edition

041583984X, 978-0415839846

More Books

Students also viewed these Finance questions

Question

Evaluate each sum if it exists. 3 i=1 (4) 2

Answered: 1 week ago