use EXCEL to solve the whole problem
Use index return on the S&P 500 (^GSPC on Yahoo finance) as the proxy for the market return. Assume risk-free rate to be 2.0% per year.
b. Calculate returns for both JNJ and S&P 500 for 120 months ending in Feb 2021. To calculate returns for 120 months, prices of 121 months would be required. Cannot calculate the returns for the first value.
c. What is the compound annual return over the 10 years for both JNJ and S&P 500?
d. What is the variance of returns of the S&P 500 and covariance between the returns of S&P500 and the returns of JNJ? Use 10 years of monthly returns ending with the returns of Feb 2021 for this.
e. What is the beta of JNJ with respect to the S&P 500? Use the Capital Asset Pricing Model to calculate the cost of equity for Johnson and Johnson (JNJ). Use 10 years of monthly returns ending with the returns of Feb 2021 for this.
f. Use cost of equity from part (e) and dividend growth rate from question 1(c) to calculate the price of JNJ at the end of Feb 2021 using the Gordon growth model.
Date Open High Low Close Adj Close Volume 1/1/2011 62.63 63.54 59.69 59.77 43.75845 257253400 2/1/2011 59.7 61.45 59.36 61.44 44.98109 199932400 3/1/2011 61.25 61.5 57.5 59.25 43.77004 254534800 4/1/2011 59.63 65.97 59.06 65.72 48.54968 270819500 5/1/2011 65.92 67.37 64.8 67.291 49.70948 270892200 6/1/2011 67.02 67.36 64.69 66.52 49.56685 231770300 7/1/2011 66.48 68.05 64.13 64.79 48.27775 202614600 8/1/2011 65.28 66.23 59.08 65.8 49.03036 491488700 9/1/2011 65.66 66.14 61 63.69 47.88092 264862000 10/1/2011 63.65 65.86 60.83 64.39 48.40715 251590300 11/1/2011 63.68 65.49 61.05 64.72 48.65524 236914800 12/1/2011 64.68 66.32 62.85 65.58 49.75932 210285200 1/1/2012 65.59 66 64.34 65.91 50.00972 212136100 2/1/2012 65.97 66.32 64.02 65.08 49.37993 192370000 3/1/2012 65.11 66.18 64.12 65.96 50.48973 208163900 4/1/2012 66.04 66.3 62.76 65.1 49.83143 229665900 5/1/2012 64.91 65.49 62 62.43 47.78764 278010100 6/1/2012 62.04 67.7 61.71 67.56 52.21789 451142100 7/1/2012 67.42 69.7 67 69.22 53.50092 273347200 8/1/2012 69.48 69.75 66.85 67.43 52.11741 224101500 9/1/2012 67.49 69.64 66.97 68.91 53.74532 200224200 10/1/2012 68.92 72.74 67.8 70.82 55.235 267146700 11/1/2012 71.1 71.9 68.51 69.73 54.38484 211662500 12/1/2012 69.8 71.62 69.18 70.1 55.1569 232603000 1/1/2013 71.02 74.8 70.3 73.92 58.16261 209813800 2/1/2013 74.14 77.02 73.56 76.11 59.88577 177978400 3/1/2013 75.79 81.59 75.73 81.53 64.66351 193396300 4/1/2013 81.39 85.89 80.31 85.23 67.59811 218722300 5/1/2013 84.5 89.99 83.88 84.18 66.76531 220724300 6/1/2013 84.28 87.82 82.12 85.86 68.60968 210552200 7/1/2013 85.72 94.42 85.5 93.5 74.71468 200315600 8/1/2013 9/1/2013 10/1/2013 11/1/2013 12/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 10/1/2014 11/1/2014 12/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 10/1/2015 11/1/2015 12/1/2015 1/1/2016 2/1/2016 3/1/2016 94.16 86.89 86.59 92.38 94.41 91.14 88.75 91.13 98.31 100.77 101.54 105.15 99.76 103.3 105.98 107.83 107.89 105.05 100.49 102.51 100.46 99.62 100.28 98.3 100 92.29 93.43 101.18 101.73 101.71 103.61 105.9 94.4 90.72 93.45 95.99 94.79 95.38 92.99 98.6 101.98 101.54 106 106.74 104.42 108.77 108.18 109.49 108.99 106.5 103.33 103.41 102.14 104.48 100.84 101.36 101 96.19 102.12 103 105.49 104.75 106.92 109.56 85.85 86 85.5 92.28 89.77 88.15 86.09 90.83 96.05 98.88 101.01 100.05 98.8 103.02 95.1 106.6 102.5 100.09 97.15 98.22 98.04 98.66 97.01 96.64 81.79 89.9 91.76 99.74 100.31 94.28 99.78 105.44 86.41 69.04915 86.69 69.79481 92.61 74.56106 94.66 76.21155 91.59 74.25467 88.47 71.72519 92.12 74.68433 98.23 80.21094 101.29 82.70963 101.46 82.84846 104.62 86.02416 100.09 82.29936 103.73 85.29236 106.59 8.23682 107.78 89.22192 108.25 89.61099 104.57 87.12843 100.14 83.4373 102.51 85.412 100.6 84.40731 99.2 83.23267 100.14 84.02139 97.46 82.36906 100.21 84.69323 93.98 79.4279 93.35 79.49901 101.03 86.03946 101.24 86.21829 102.72 88.12357 104.44 89.59917 105.21 90.25975 108.2 93.49757 204260800 158876500 173024000 131026200 151086000 192804300 162234700 181324400 180242400 124656300 123927200 148795100 120163600 130571800 232630100 125669200 175446100 195025900 191367400 177464600 171578200 143779800 170023700 151835800 194654400 202039800 191690900 126172600 162039600 211278000 201552000 172970400 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 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 10/1/2017 11/1/2017 12/1/2017 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018 10/1/2018 11/1/2018 12/1/2018 108 112.22 112.69 121.3 125.31 119.19 118 114.76 111.36 115.78 112.48 122.49 124.73 123.4 128.32 132.79 133.17 132.6 130.16 139.83 139.57 139.66 137.53 129.11 127.82 126.32 120.38 121.34 132.39 134.69 138.26 140.07 145.57 114.19 115 121.41 126.07 125.9 119.97 120.2 122.5 117.3 117 122.88 129 125.81 128.8 137 137.08 134.97 135.79 144.35 141.87 143.8 148.32 140.67 135.7 132.88 127.61 124.85 132.64 137.43 143.13 141.43 148.75 148.99 107.69 111.7 112.07 120.79 118.33 117.04 112.99 111.3 109.32 110.76 112.47 122.39 120.95 122.34 128.12 129.57 130.9 129.05 130.02 136.6 138.6 138.1 122.15 124.9 123.54 118.62 119.97 120.11 128.93 133.44 132.23 139 121 112.08 96.85036 112.69 97.37747 121.3 105.5659 125.23 108.9861 119.34 103.8601 118.13 103.4929 115.99 101.6181 111.3 97.5092 115.21 101.6322 113.25 99.90319 122.21 107.8072 124.55 110.5985 123.47 109.6395 128.25 113.884 132.29 118.2505 132.72 118.6349 132.37 118.322 130.01 116.9517 139.41 125.4076 139.33 125.3356 139.72 126.4561 138.19 125.0714 129.88 117.5503 128.15 116.7272 126.49 115.2151 119.62 108.9575 121.34 111.3439 132.52 121.6029 134.69 123.5941 138.17 127.6324 139.99 129.3136 146.9 135.6966 129.05 119.967 158186600 141096900 167571400 131996000 138570700 130378400 140054500 171310900 138044800 143312500 133814500 166544900 106205200 112541500 145558900 107885800 107061100 116205400 123841100 93742200 114023200 170954200 177033700 151837800 144094200 139533300 138683200 145735900 123996800 122168800 167964600 152746600 289169400 1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 6/1/2019 7/1/2019 8/1/2019 9/1/2019 10/1/2019 11/1/2019 12/1/2019 1/1/2020 2/1/2020 3/1/2020 4/1/2020 5/1/2020 6/1/2020 7/1/2020 8/1/2020 9/1/2020 10/1/2020 11/1/2020 12/1/2020 1/1/2021 2/1/2021 3/1/2021 4/1/2021 5/1/2021 6/1/2021 7/1/2021 8/1/2021 9/1/2021 128.13 134.02 137.22 139.99 140.95 131.5 140.2 130.26 127.99 130.02 132.05 137.72 145.87 149.42 134.78 127.7 149.62 147.29 140.69 146.39 153.87 149.31 138.98 146.29 157.24 165.31 161.45 162.6 163.6 170.15 164.74 172.47 1729 135.19 137.95 140 141.45 142.35 144.98 142.47 134.1 132.78 137.49 138.63 147.84 151.19 154.5 143.64 157 153.62 150.03 151.67 154.4 155.47 153.14 151.3 157.66 173.65 167.94 167.03 167.79 172.74 170.2 173.38 179.92 175 22 125 131.26 135.74 134.42 128.52 131.04 127.84 126.63 126.34 126.1 129.68 136.16 141.38 130.82 109.16 125.5 143.01 137.02 140.06 145.82 142.96 133.65 137.49 145.86 154.13 157.97 151.47 156.53 163.12 161.79 164.63 171.3 16217 133.08 123.7134 136.64 127.0228 139.79 130.813 141.2 132.1324 131.15 122.7279 139.28 131.2276 130.22 122.6914 128.36 120.9389 129.38 122.8134 132.04 125.3383 137.49 130.5117 145.87 139.4257 148.87 142.2932 134.48 128.5389 131.13 126.1362 150.04 144.326 148.75 143.0851 140.63 136.2121 145.76 141.1809 153.41 148.5906 148.88 145.1627 137.11 133.6866 144.68 141.0676 157.38 154.5168 163.13 160.1622 158.46 155.5771 164.35 162.3662 162.73 160.7657 169.25 167.207 164.74 163.7668 172.2 171.1828 173.13 172.1073 164 36 164 36 174392900 119453700 137354500 124329800 159034100 150883000 159847900 176400700 122925900 206654300 127753600 127761600 150462900 147288300 355927400 234380900 136206100 174066600 137880400 109956200 138799800 137284700 153635100 158925000 184488700 147681200 175073000 162648500 133901100 145670800 133724800 114782400 106543200
