80.2K

Verified Solution

Link Copied!

1. Using the Open and Close price data for each month (the date given is the first trading day for that month: i.e. the open and close for the month) to calculate the monthly percentage holding period returns for each stock and index where each months Open price is the beginning of the month price and each months Close price is the end of the month price. You can get rid of the other data provided for each month. In addition, you will need to include dividend payments for each stock to calculate monthly returns for the months that they made a dividend payment (the index doesnt have dividend payments). For example, Septembers monthly return is (Sept Close Sept. Open + any Sept Div)/Sept Open. You should have 60 monthly returns for each stock and index from Mar. 1, 2017 to Feb. 28, 2022. Helpful Hint: I strongly urge creating a table of the appropriate opening & closing prices and dividends for each stock along with opening and closing prices for the Wilshire 5000 Index on a separate worksheet and then do your calculations on this fresh worksheet. Also, Netflix hasnt paid dividends.
2. What is the average monthly return for each stock and the Wilshire 5000 index over the entire time period? Convert the average monthly return for each stock and index into an APR (annualized rate) by multiplying your monthly average for each stock by 12. Please convert your answers to percentages with 2 decimal places.
3. Calculate the (population) standard deviation of monthly returns for each stock and the index using Excel.
4. Now calculate the beta for each stock using Excels Slope function to use estimate the slope of the linear regression line, which is beta. Use the Wilshire 5000 index as the market portfolio in finding beta.
5. Now use the CAPM/SML Equation to estimate each stocks required return. Use 9.5% as the required market return and the recent 10-year Treasury rate of 2.3% as the risk-free rate. Would you recommend buying IBM and/or Netflix if your APR average for each stock from #2 is your expected return? Explain your answer.

A B C D E Vouw NP 1 Date Open Close Dividend 2 3/1/17 142.839996 147.809998 NA 3 4/1/17 146.699997 152.199997 NA 4 5/1/17 151.910004 163.070007 NA 6/1/17 163.520004 149.410004 NA 6 7/1/17 149.800003 181.660004 NA 7 8/1/17 182.490005 174.710007 NA 8 9/1/17 175.550003 181.350006 NA 9 10/1/17 182.110001 196.429993 NA 10 11/1/17 197.240005 187.580002 NA 11 12/1/17 186.990005 191.960007 NA 12 1/1/18 196.100006 270.299988 NA 13 2/1/18 266.410004 291.380005 NA 14 3/1/18 292.75 295.350006 NA 15 4/1/18 291.940002 312.459991 NA 16 5/1/18 310.359985 351.600006 NA 17 6/1/18 353.880005 391.429993 NA 18 7/1/18 385.450012 337.450012 NA 19 8/1/18 335.869995 367.679993 NA 20 9/1/18 366.470001 374.130005 NA 21 10/1/18 375.850006 301.779999 NA 22 11/1/18 304.589996 286.130005 NA 23 12/1/18 293.190002 267.660004 NA 24 1/1/19 259.279999 339.5 NA 25 2/1/19 337.179993 358.100006 NA 26 3/1/19 362.26001 356.559998 NA 27 4/1/19 359 370.540009 NA 28 5/1/19 374 343.279999 NA 29 6/1/19 343.559998 367.320007 NA 30 7/1/19 373.5 322.98999 NA 31 8/1/19 324.25 293.75 NA 32 9/1/19 290.820007 267.619995 NA NFLX IBM AW5000 + A B C D .PL 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 8/1/19 324.25 293.75 NA 9/1/19 290.820007 267.619995 NA 10/1/19 267.350006 287.410004 NA 11/1/19 288.700012 314.660004 NA 12/1/19 314.390015 323.570007 NA 1/1/20 326.100006 345.089996 NA 2/1/20 347.23999 369.029999 NA 3/1/20 373.109985 375.5 NA 4/1/20 376.049988 419.850006 NA 5/1/20 415.100006 419.730011 NA 6/1/20 418.829987 455.040009 NA 7/1/20 454 488.880005 NA 8/1/20 490.859985 529.559998 NA 9/1/20 532.599976 500.029999 NA 10/1/20 506.029999 475.73999 NA 11/1/20 478.869995 490.700012 NA 12/1/20 492.339996 540.72998 NA 1/1/21 539 532.390015 NA 2/1/21 536.789978 538.849976 NA 3/1/21 545.570007 521.659973 NA 4/1/21 529.929993 513.469971 NA 5/1/21 512.650024 502.809998 NA 6/1/21 504.01001 528.210022 NA 7/1/21 525.719971 517.570007 NA 8/1/21 519 569.190002 NA 9/1/21 569 610.340027 NA 10/1/21 604.23999 690.309998 NA 11/1/21 689.059998 641.900024 NA 12/1/21 649.47998 602.440002 NA 1/1/22 605.609985 427.140015 NA 2/1/22 432.959991 394.519989 NA 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 NFLX IBM AW5000 + A B D E F Dividends 1 2 3 OU WN 4 5 1.5 1.5 1.5 1.5 1.57 1.57 1.57 1.57 6 7 8 9 10 11 12 13 Date 5/8/17 8/8/17 11/9/17 2/8/18 5/9/18 8/9/18 11/8/18 2/7/19 5/9/19 8/8/19 11/7/19 2/7/20 5/7/20 8/7/20 11/9/20 2/9/21 5/7/21 8/9/21 11/9/21 2/10/22 1.62 1.62 1.62 1.62 1.63 1.63 1.63 1.63 1.64 1.64 Date Open Close 3/1/17 172.543015 166.481842 4/1/17 166.175903 153.240921 5/1/17 153.011475 145.917786 6/1/17 146.080307 147.065002 7/1/17 146.826004 138.307846 8/1/17 138.623322 136.73996 9/1/17 136.692154 138.699814 10/1/17 138.957932 147.284897 11/1/17 147.323135 147.198853 12/1/17 147.60994 146.673035 1/1/18 147.705551 156.500961 2/1/18 156.013382 148.977051 3/1/18 148.690247 146.682602 4/1/18 146.596558 138.585083 5/1/18 138.288712 135.095596 6/1/18 136.166351 133.556412 7/1/18 132.198853 138.556412 8/1/18 138.393875 140.038239 9/1/18 139.560226 144.560226 10/1/18 145.038239 110.353729 11/1/18 110.420647 118.80497 12/1/18 120.143402 108.671127 1/1/19 107.084129 128.508606 2/1/19 129.034424 132.05545 3/1/19 133.183563 134.894836 4/1/19 135.286804 134.101334 5/1/19 134.369019 121.405357 6/1/19 121.510513 131.835571 7/1/19 133.4608 141.72084 8/1/19 142.351822 129.569794 9/1/19 128.919693 139.024857 10/1/19 139.187378 127.848946 11/1/19 128.585083 128.537292 12/1/19 128.537292 128.145309 1/1/20 129.063095 137.40918 2/1/20 137.906311 124.426384 3/1/20 125 106.051628 14 15 16 17 18 19 20 21 22 23 24 25 26 1.64 1.64 27 28 29 30 31 32 33 34 35 36 37 38 NFLX IBM W5000 + D E 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 A B 1/1/19 107.084129 128.508606 2/1/19 129.034424 132.05545 3/1/19 133.183563 134.894836 4/1/19 135.286804 134.101334 5/1/19 134.369019 121.405357 6/1/19 121.510513 131.835571 7/1/19 133.4608 141.72084 8/1/19 142.351822 129.569794 9/1/19 128.919693 139.024857 10/1/19 139.187378 127.848946 11/1/19 128.585083 128.537292 12/1/19 128.537292 128.145309 1/1/20 129.063095 137.40918 2/1/20 137.906311 124.426384 3/1/20 125 106.051628 4/1/20 101.682602 120.038239 5/1/20 117.772469 119.407265 6/1/20 119.158699 115.458893 7/1/20 114.980881 117.533463 8/1/20 118.068832 117.887192 9/1/20 117.447418 116.319313 10/1/20 116.978966 106.749519 11/1/20 107.695984 118.087952 12/1/20 118.451241 120.34417 1/1/21 120.315491 113.871895 2/1/21 114.627151 113.699806 3/1/21 115.057358 127.39962 4/1/21 127.877632 135.640533 5/1/21 137.485657 137.418732 6/1/21 138.623322 140.143402 7/1/21 140.497131 134.761002 8/1/21 135.229446 134.168259 9/1/21 133.824097 132.820267 10/1/21 134.79924 119.598473 11/1/21 119.550667 117.099998 12/1/21 118.25 133.660004 1/1/22 134.070007 133.570007 2/1/22 133.759995 122.510002 58 59 60 61 62 NFLX IBM ^W5000 + A B C D E vou +wN 1 Date Open Close Dividend 2 3/1/17 24818.5703 24624.7207 NA 3 4/1/17 24648.3906 24878.4805 NA 4 5/1/17 24921.7402 25041.3398 NA 5 6/1/17 25040.3906 25230.4102 NA 6 7/1/17 25304.7891 25685.1895 NA 7 8/1/17 25773.1602 25657.9609 NA 8 9/1/17 25693.4609 26233.3398 NA 9 10/1/17 26244.7207 26760.1895 NA 10 11/1/17 26864.8203 27493.3809 NA 11 12/1/17 27482.0391 27794.1699 NA 12 1/1/18 27892.1191 29237.4805 NA 13 2/1/18 29148.1309 28117.0195 NA 14 3/1/18 28029.1992 27410.7207 NA 15 4/1/18 27307.6309 27520.9492 NA 16 5/1/18 27470.1699 28218.4609 NA 17 6/1/18 28357.2695 28394.1309 NA 18 7/1/18 28229.5293 29295.75 NA 19 8/1/18 29327.7891 30274.5293 NA 20 9/1/18 30224.2402 30259.8203 NA 21 10/1/18 30380.1504 27980.9004 NA 22 11/1/18 28043.9609 28448.8691 NA 23 12/1/18 28657.7598 25749.7207 NA 24 1/1/19 25585.3008 27992.4102 NA 25 2/1/19 27992.1504 28903.3008 NA 26 3/1/19 28974.5703 29266.9102 NA 27 4/1/19 29266.9102 30410.0195 NA 28 5/1/19 30463.7305 28369.3906 NA 29 6/1/19 28369.3906 30268.0508 NA 30 7/1/19 30410.3301 30684.8203 NA 31 8/1/19 30678.9004 29925.3496 NA 32 9/1/19 29841.3496 30351.9199 NA 33 10/1/19 30351.9199 30945.6309 NA 34 11/1/19 30945.6309 32025.8594 NA 35 12/1/19 32044.6602 32886.7383 NA 36 1/1/20 33047.5 32838.1914 NA 37 2/1/20 32939.101630125.0703 NA 38 3/1/20 30321.9102 25899.9297 NA NFLX IBM AW5000 + D 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 A B C 1/1/19 25585.3008 27992.4102 NA 2/1/19 27992.1504 28903.3008 NA 3/1/19 28974.5703 29266.9102 NA 4/1/19 29266.9102 30410.0195 NA 5/1/19 30463.7305 28369.3906 NA 6/1/19 28369.3906 30268.0508 NA 7/1/19 30410.3301 30684.8203 NA 8/1/19 30678.9004 29925.3496 NA 9/1/19 29841.3496 30351.9199 NA 10/1/19 30351.9199 30945.6309 NA 11/1/19 30945.6309 32025.8594 NA 12/1/19 32044.6602 32886.7383 NA 1/1/20 33047.5 32838.1914 NA 2/1/20 32939.1016 30125.0703 NA 3/1/20 30321.9102 25899.9297 NA 4/1/20 25120.1309 29365.4902 NA 5/1/20 28914.5898 30927.0391 NA 6/1/20 30874.8203 31630.5098 NA 7/1/20 31644.7207 33393.3906 NA 8/1/20 33546 35807.6602 NA 9/1/20 35901.6797 34480.2695 NA 10/1/20 34480.2695 33724.8086 NA 11/1/20 33724.8086 37839.6484 NA 12/1/20 37839.6484 39456.6602 NA 1/1/21 39456.6602 39396.1719 NA 2/1/21 39396.1719 40508.6797 NA 3/1/21 40508.6797 41741.8711 NA 4/1/21 41741.8711 43870.0898 NA 5/1/21 43870.0898 43966.3594 NA 6/1/21 43966.3594 45113.8594 NA 7/1/21 45113.8594 45708 NA 8/1/21 45708 46979.6289 NA 9/1/21 46979.6289 44850.0313 NA 10/1/21 44850.0313 47795.0898 NA 11/1/21 47795.0898 46936.0781 NA 12/1/21 46936.0781 48461.1602 NA 1/1/22 48461.1602 45416.4688 NA 2/1/22 45416.4688 44259.2109 NA 59 60 61 62 NFLX IBM AW5000 A B C D E Vouw NP 1 Date Open Close Dividend 2 3/1/17 142.839996 147.809998 NA 3 4/1/17 146.699997 152.199997 NA 4 5/1/17 151.910004 163.070007 NA 6/1/17 163.520004 149.410004 NA 6 7/1/17 149.800003 181.660004 NA 7 8/1/17 182.490005 174.710007 NA 8 9/1/17 175.550003 181.350006 NA 9 10/1/17 182.110001 196.429993 NA 10 11/1/17 197.240005 187.580002 NA 11 12/1/17 186.990005 191.960007 NA 12 1/1/18 196.100006 270.299988 NA 13 2/1/18 266.410004 291.380005 NA 14 3/1/18 292.75 295.350006 NA 15 4/1/18 291.940002 312.459991 NA 16 5/1/18 310.359985 351.600006 NA 17 6/1/18 353.880005 391.429993 NA 18 7/1/18 385.450012 337.450012 NA 19 8/1/18 335.869995 367.679993 NA 20 9/1/18 366.470001 374.130005 NA 21 10/1/18 375.850006 301.779999 NA 22 11/1/18 304.589996 286.130005 NA 23 12/1/18 293.190002 267.660004 NA 24 1/1/19 259.279999 339.5 NA 25 2/1/19 337.179993 358.100006 NA 26 3/1/19 362.26001 356.559998 NA 27 4/1/19 359 370.540009 NA 28 5/1/19 374 343.279999 NA 29 6/1/19 343.559998 367.320007 NA 30 7/1/19 373.5 322.98999 NA 31 8/1/19 324.25 293.75 NA 32 9/1/19 290.820007 267.619995 NA NFLX IBM AW5000 + A B C D .PL 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 8/1/19 324.25 293.75 NA 9/1/19 290.820007 267.619995 NA 10/1/19 267.350006 287.410004 NA 11/1/19 288.700012 314.660004 NA 12/1/19 314.390015 323.570007 NA 1/1/20 326.100006 345.089996 NA 2/1/20 347.23999 369.029999 NA 3/1/20 373.109985 375.5 NA 4/1/20 376.049988 419.850006 NA 5/1/20 415.100006 419.730011 NA 6/1/20 418.829987 455.040009 NA 7/1/20 454 488.880005 NA 8/1/20 490.859985 529.559998 NA 9/1/20 532.599976 500.029999 NA 10/1/20 506.029999 475.73999 NA 11/1/20 478.869995 490.700012 NA 12/1/20 492.339996 540.72998 NA 1/1/21 539 532.390015 NA 2/1/21 536.789978 538.849976 NA 3/1/21 545.570007 521.659973 NA 4/1/21 529.929993 513.469971 NA 5/1/21 512.650024 502.809998 NA 6/1/21 504.01001 528.210022 NA 7/1/21 525.719971 517.570007 NA 8/1/21 519 569.190002 NA 9/1/21 569 610.340027 NA 10/1/21 604.23999 690.309998 NA 11/1/21 689.059998 641.900024 NA 12/1/21 649.47998 602.440002 NA 1/1/22 605.609985 427.140015 NA 2/1/22 432.959991 394.519989 NA 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 NFLX IBM AW5000 + A B D E F Dividends 1 2 3 OU WN 4 5 1.5 1.5 1.5 1.5 1.57 1.57 1.57 1.57 6 7 8 9 10 11 12 13 Date 5/8/17 8/8/17 11/9/17 2/8/18 5/9/18 8/9/18 11/8/18 2/7/19 5/9/19 8/8/19 11/7/19 2/7/20 5/7/20 8/7/20 11/9/20 2/9/21 5/7/21 8/9/21 11/9/21 2/10/22 1.62 1.62 1.62 1.62 1.63 1.63 1.63 1.63 1.64 1.64 Date Open Close 3/1/17 172.543015 166.481842 4/1/17 166.175903 153.240921 5/1/17 153.011475 145.917786 6/1/17 146.080307 147.065002 7/1/17 146.826004 138.307846 8/1/17 138.623322 136.73996 9/1/17 136.692154 138.699814 10/1/17 138.957932 147.284897 11/1/17 147.323135 147.198853 12/1/17 147.60994 146.673035 1/1/18 147.705551 156.500961 2/1/18 156.013382 148.977051 3/1/18 148.690247 146.682602 4/1/18 146.596558 138.585083 5/1/18 138.288712 135.095596 6/1/18 136.166351 133.556412 7/1/18 132.198853 138.556412 8/1/18 138.393875 140.038239 9/1/18 139.560226 144.560226 10/1/18 145.038239 110.353729 11/1/18 110.420647 118.80497 12/1/18 120.143402 108.671127 1/1/19 107.084129 128.508606 2/1/19 129.034424 132.05545 3/1/19 133.183563 134.894836 4/1/19 135.286804 134.101334 5/1/19 134.369019 121.405357 6/1/19 121.510513 131.835571 7/1/19 133.4608 141.72084 8/1/19 142.351822 129.569794 9/1/19 128.919693 139.024857 10/1/19 139.187378 127.848946 11/1/19 128.585083 128.537292 12/1/19 128.537292 128.145309 1/1/20 129.063095 137.40918 2/1/20 137.906311 124.426384 3/1/20 125 106.051628 14 15 16 17 18 19 20 21 22 23 24 25 26 1.64 1.64 27 28 29 30 31 32 33 34 35 36 37 38 NFLX IBM W5000 + D E 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 A B 1/1/19 107.084129 128.508606 2/1/19 129.034424 132.05545 3/1/19 133.183563 134.894836 4/1/19 135.286804 134.101334 5/1/19 134.369019 121.405357 6/1/19 121.510513 131.835571 7/1/19 133.4608 141.72084 8/1/19 142.351822 129.569794 9/1/19 128.919693 139.024857 10/1/19 139.187378 127.848946 11/1/19 128.585083 128.537292 12/1/19 128.537292 128.145309 1/1/20 129.063095 137.40918 2/1/20 137.906311 124.426384 3/1/20 125 106.051628 4/1/20 101.682602 120.038239 5/1/20 117.772469 119.407265 6/1/20 119.158699 115.458893 7/1/20 114.980881 117.533463 8/1/20 118.068832 117.887192 9/1/20 117.447418 116.319313 10/1/20 116.978966 106.749519 11/1/20 107.695984 118.087952 12/1/20 118.451241 120.34417 1/1/21 120.315491 113.871895 2/1/21 114.627151 113.699806 3/1/21 115.057358 127.39962 4/1/21 127.877632 135.640533 5/1/21 137.485657 137.418732 6/1/21 138.623322 140.143402 7/1/21 140.497131 134.761002 8/1/21 135.229446 134.168259 9/1/21 133.824097 132.820267 10/1/21 134.79924 119.598473 11/1/21 119.550667 117.099998 12/1/21 118.25 133.660004 1/1/22 134.070007 133.570007 2/1/22 133.759995 122.510002 58 59 60 61 62 NFLX IBM ^W5000 + A B C D E vou +wN 1 Date Open Close Dividend 2 3/1/17 24818.5703 24624.7207 NA 3 4/1/17 24648.3906 24878.4805 NA 4 5/1/17 24921.7402 25041.3398 NA 5 6/1/17 25040.3906 25230.4102 NA 6 7/1/17 25304.7891 25685.1895 NA 7 8/1/17 25773.1602 25657.9609 NA 8 9/1/17 25693.4609 26233.3398 NA 9 10/1/17 26244.7207 26760.1895 NA 10 11/1/17 26864.8203 27493.3809 NA 11 12/1/17 27482.0391 27794.1699 NA 12 1/1/18 27892.1191 29237.4805 NA 13 2/1/18 29148.1309 28117.0195 NA 14 3/1/18 28029.1992 27410.7207 NA 15 4/1/18 27307.6309 27520.9492 NA 16 5/1/18 27470.1699 28218.4609 NA 17 6/1/18 28357.2695 28394.1309 NA 18 7/1/18 28229.5293 29295.75 NA 19 8/1/18 29327.7891 30274.5293 NA 20 9/1/18 30224.2402 30259.8203 NA 21 10/1/18 30380.1504 27980.9004 NA 22 11/1/18 28043.9609 28448.8691 NA 23 12/1/18 28657.7598 25749.7207 NA 24 1/1/19 25585.3008 27992.4102 NA 25 2/1/19 27992.1504 28903.3008 NA 26 3/1/19 28974.5703 29266.9102 NA 27 4/1/19 29266.9102 30410.0195 NA 28 5/1/19 30463.7305 28369.3906 NA 29 6/1/19 28369.3906 30268.0508 NA 30 7/1/19 30410.3301 30684.8203 NA 31 8/1/19 30678.9004 29925.3496 NA 32 9/1/19 29841.3496 30351.9199 NA 33 10/1/19 30351.9199 30945.6309 NA 34 11/1/19 30945.6309 32025.8594 NA 35 12/1/19 32044.6602 32886.7383 NA 36 1/1/20 33047.5 32838.1914 NA 37 2/1/20 32939.101630125.0703 NA 38 3/1/20 30321.9102 25899.9297 NA NFLX IBM AW5000 + D 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 A B C 1/1/19 25585.3008 27992.4102 NA 2/1/19 27992.1504 28903.3008 NA 3/1/19 28974.5703 29266.9102 NA 4/1/19 29266.9102 30410.0195 NA 5/1/19 30463.7305 28369.3906 NA 6/1/19 28369.3906 30268.0508 NA 7/1/19 30410.3301 30684.8203 NA 8/1/19 30678.9004 29925.3496 NA 9/1/19 29841.3496 30351.9199 NA 10/1/19 30351.9199 30945.6309 NA 11/1/19 30945.6309 32025.8594 NA 12/1/19 32044.6602 32886.7383 NA 1/1/20 33047.5 32838.1914 NA 2/1/20 32939.1016 30125.0703 NA 3/1/20 30321.9102 25899.9297 NA 4/1/20 25120.1309 29365.4902 NA 5/1/20 28914.5898 30927.0391 NA 6/1/20 30874.8203 31630.5098 NA 7/1/20 31644.7207 33393.3906 NA 8/1/20 33546 35807.6602 NA 9/1/20 35901.6797 34480.2695 NA 10/1/20 34480.2695 33724.8086 NA 11/1/20 33724.8086 37839.6484 NA 12/1/20 37839.6484 39456.6602 NA 1/1/21 39456.6602 39396.1719 NA 2/1/21 39396.1719 40508.6797 NA 3/1/21 40508.6797 41741.8711 NA 4/1/21 41741.8711 43870.0898 NA 5/1/21 43870.0898 43966.3594 NA 6/1/21 43966.3594 45113.8594 NA 7/1/21 45113.8594 45708 NA 8/1/21 45708 46979.6289 NA 9/1/21 46979.6289 44850.0313 NA 10/1/21 44850.0313 47795.0898 NA 11/1/21 47795.0898 46936.0781 NA 12/1/21 46936.0781 48461.1602 NA 1/1/22 48461.1602 45416.4688 NA 2/1/22 45416.4688 44259.2109 NA 59 60 61 62 NFLX IBM AW5000

Answer & Explanation Solved by verified expert

See Answer