In: Finance
Using the CAPM, calculate the 2016 expected return on Facebook’s shares.
Assumptions:
Risk-free rate, Rf = 2.5%
E(Rm) = 7.5%
Use the SP500 index to proxy for the market portfolio.
Hints to calculate beta:
- Use daily stock returns during 2016 (start date 01-Jan-2016, end date 31-Dec-2016).
- Download stock prices for Facebook and the SP500 with ticker name FB and ^GSPC, respectively, from www.finance.yahoo.com.
- Use adjusted prices to calculate returns.
We know beta = Covariance of security wit benchmark/Variance of
security
We have downloaded the yearly data for 2016 and provided
returns:
FB | S&P | FB return | S&P return |
104.66 | 2043.939941 | ||
102.22 | 2012.660034 | -0.02331361 | -0.01530373 |
102.73 | 2016.709961 | 0.004989258 | 0.002012226 |
102.97 | 1990.26001 | 0.002336202 | -0.0131154 |
97.92 | 1943.089966 | -0.04904344 | -0.02370044 |
97.33 | 1922.030029 | -0.00602529 | -0.01083837 |
97.51 | 1923.670044 | 0.001849378 | 0.000853272 |
99.37 | 1938.680054 | 0.019074977 | 0.007802799 |
95.44 | 1890.280029 | -0.03954917 | -0.02496545 |
98.37 | 1921.839966 | 0.030699926 | 0.016695906 |
94.97 | 1880.329956 | -0.0345634 | -0.0215991 |
95.26 | 1881.329956 | 0.003053606 | 0.000531822 |
94.35 | 1859.329956 | -0.00955284 | -0.01169386 |
94.16 | 1868.98999 | -0.00201371 | 0.005195438 |
97.94 | 1906.900024 | 0.040144412 | 0.020283701 |
97.01 | 1877.079956 | -0.00949561 | -0.01563798 |
97.34 | 1903.630005 | 0.003401649 | 0.014144336 |
94.45 | 1882.949951 | -0.02968974 | -0.01086348 |
109.11 | 1893.359985 | 0.155214446 | 0.005528577 |
112.21 | 1940.23999 | 0.028411676 | 0.024760217 |
115.09 | 1939.380005 | 0.025666135 | -0.00044324 |
114.61 | 1903.030029 | -0.00417061 | -0.01874309 |
112.69 | 1912.530029 | -0.01675246 | 0.004992039 |
110.49 | 1915.449951 | -0.01952262 | 0.001526733 |
104.07 | 1880.050049 | -0.05810479 | -0.01848125 |
99.75 | 1853.439941 | -0.04151052 | -0.01415394 |
99.54 | 1852.209961 | -0.00210525 | -0.00066362 |
101 | 1851.859985 | 0.01466746 | -0.00018895 |
101.91 | 1829.079956 | 0.009009941 | -0.01230116 |
102.01 | 1864.780029 | 0.000981238 | 0.019518049 |
101.61 | 1895.579956 | -0.00392119 | 0.016516654 |
105.2 | 1926.819946 | 0.035331128 | 0.016480439 |
103.47 | 1917.829956 | -0.01644483 | -0.00466571 |
104.57 | 1917.780029 | 0.010631091 | -2.6033E-05 |
107.16 | 1945.5 | 0.024768136 | 0.014454197 |
105.46 | 1921.27002 | -0.01586417 | -0.01245437 |
106.88 | 1929.800049 | 0.013464802 | 0.004439787 |
108.07 | 1951.699951 | 0.01113401 | 0.011348275 |
107.92 | 1948.050049 | -0.00138801 | -0.00187011 |
106.92 | 1932.22998 | -0.00926612 | -0.00812098 |
109.82 | 1978.349976 | 0.027123102 | 0.023868792 |
109.95 | 1986.449951 | 0.001183728 | 0.004094308 |
109.58 | 1993.400024 | -0.00336512 | 0.003498741 |
108.39 | 1999.98999 | -0.01085967 | 0.003305892 |
105.73 | 2001.76001 | -0.02454097 | 0.000885014 |
105.93 | 1979.26001 | 0.001891582 | -0.01124011 |
107.51 | 1989.26001 | 0.014915529 | 0.005052393 |
107.32 | 1989.569946 | -0.0017673 | 0.000155805 |
109.41 | 2022.189941 | 0.019474506 | 0.0163955 |
109.89 | 2019.640015 | 0.004387122 | -0.00126097 |
110.67 | 2015.930054 | 0.007097998 | -0.00183694 |
112.18 | 2027.219971 | 0.013644186 | 0.005600352 |
111.02 | 2040.589966 | -0.01034055 | 0.006595236 |
111.45 | 2049.580078 | 0.003873176 | 0.004405644 |
111.85 | 2051.600098 | 0.003589062 | 0.000985577 |
112.25 | 2049.800049 | 0.003576236 | -0.00087739 |
112.54 | 2036.709961 | 0.002583528 | -0.00638603 |
113.05 | 2035.939941 | 0.00453174 | -0.00037807 |
113.69 | 2037.050049 | 0.005661203 | 0.000545256 |
116.14 | 2055.01001 | 0.021549802 | 0.008816652 |
114.7 | 2063.949951 | -0.01239885 | 0.004350315 |
114.1 | 2059.73999 | -0.00523103 | -0.00203976 |
116.06 | 2072.780029 | 0.017177914 | 0.006330915 |
112.55 | 2066.129883 | -0.03024294 | -0.00320832 |
112.22 | 2045.170044 | -0.00293205 | -0.01014449 |
113.71 | 2066.659912 | 0.013277473 | 0.010507619 |
113.64 | 2041.910034 | -0.0006156 | -0.01197579 |
110.63 | 2047.599976 | -0.02648717 | 0.002786578 |
108.99 | 2041.98999 | -0.01482418 | -0.00273979 |
110.61 | 2061.719971 | 0.014863777 | 0.009662134 |
110.51 | 2082.419922 | -0.00090407 | 0.010040137 |
110.84 | 2082.780029 | 0.002986101 | 0.000172927 |
109.64 | 2080.72998 | -0.01082639 | -0.00098428 |
110.45 | 2094.340088 | 0.007387796 | 0.006541026 |
112.29 | 2100.800049 | 0.016659158 | 0.003084485 |
112.42 | 2102.399902 | 0.00115769 | 0.000761545 |
113.44 | 2091.47998 | 0.009073154 | -0.00519403 |
110.56 | 2091.580078 | -0.02538791 | 4.78599E-05 |
110.1 | 2087.790039 | -0.00416064 | -0.00181205 |
108.76 | 2091.699951 | -0.01217072 | 0.001872752 |
108.89 | 2095.149902 | 0.001195265 | 0.001649353 |
116.73 | 2075.810059 | 0.071999303 | -0.00923077 |
117.58 | 2065.300049 | 0.007281753 | -0.00506309 |
118.57 | 2081.429932 | 0.008419782 | 0.007809947 |
117.43 | 2063.370117 | -0.00961457 | -0.00867664 |
118.06 | 2051.120117 | 0.005364881 | -0.00593689 |
117.81 | 2050.629883 | -0.00211757 | -0.00023901 |
119.49 | 2057.139893 | 0.01426025 | 0.003174639 |
119.24 | 2058.689941 | -0.00209223 | 0.000753497 |
120.5 | 2084.389893 | 0.010566941 | 0.012483644 |
119.52 | 2064.459961 | -0.0081328 | -0.00956152 |
120.28 | 2064.110107 | 0.006358785 | -0.00016947 |
119.81 | 2046.609985 | -0.00390756 | -0.00847829 |
118.67 | 2066.659912 | -0.00951507 | 0.009796653 |
117.35 | 2047.209961 | -0.01112328 | -0.0094113 |
117.65 | 2047.630005 | 0.002556489 | 0.000205179 |
116.81 | 2040.040039 | -0.00713986 | -0.00370671 |
117.35 | 2052.320068 | 0.004622892 | 0.006019504 |
115.97 | 2048.040039 | -0.01175967 | -0.00208546 |
117.7 | 2076.060059 | 0.014917616 | 0.013681383 |
117.89 | 2090.540039 | 0.001614291 | 0.00697474 |
119.47 | 2090.100098 | 0.013402341 | -0.00021044 |
119.38 | 2099.060059 | -0.00075336 | 0.004286857 |
118.81 | 2096.949951 | -0.00477466 | -0.00100526 |
118.78 | 2099.330078 | -0.0002525 | 0.001135042 |
118.93 | 2105.26001 | 0.001262847 | 0.002824678 |
118.47 | 2099.129883 | -0.00386781 | -0.00291181 |
118.79 | 2109.409912 | 0.002701106 | 0.004897281 |
117.76 | 2112.129883 | -0.00867076 | 0.001289446 |
118.39 | 2119.120117 | 0.005349839 | 0.003309566 |
118.56 | 2115.47998 | 0.001435924 | -0.00171776 |
116.62 | 2096.070068 | -0.01636298 | -0.00917518 |
113.95 | 2079.060059 | -0.02289492 | -0.00811519 |
114.94 | 2075.320068 | 0.008688065 | -0.00179889 |
114.6 | 2071.5 | -0.0029581 | -0.00184071 |
114.39 | 2077.98999 | -0.00183245 | 0.003132991 |
113.02 | 2071.219971 | -0.01197659 | -0.00325797 |
113.37 | 2083.25 | 0.00309685 | 0.005808185 |
114.38 | 2088.899902 | 0.008908829 | 0.002712061 |
113.91 | 2085.449951 | -0.00410905 | -0.00165156 |
115.08 | 2113.320068 | 0.010271249 | 0.013364079 |
112.08 | 2037.410034 | -0.02606882 | -0.0359198 |
108.97 | 2000.540039 | -0.02774805 | -0.0180965 |
112.7 | 2036.089966 | 0.034229567 | 0.017770165 |
114.16 | 2070.77002 | 0.01295481 | 0.017032673 |
114.28 | 2098.860107 | 0.001051112 | 0.013565044 |
114.19 | 2102.949951 | -0.00078751 | 0.001948602 |
114.2 | 2088.550049 | 8.75296E-05 | -0.00684748 |
116.7 | 2099.72998 | 0.021891419 | 0.005352963 |
115.85 | 2097.899902 | -0.00728362 | -0.00087158 |
117.24 | 2129.899902 | 0.011998274 | 0.015253349 |
117.87 | 2137.159912 | 0.005373635 | 0.003408616 |
117.93 | 2152.139893 | 0.00050901 | 0.007009293 |
116.78 | 2152.429932 | -0.00975156 | 0.000134768 |
117.29 | 2163.75 | 0.004367203 | 0.005259204 |
116.86 | 2161.73999 | -0.00366613 | -0.00092895 |
119.37 | 2166.889893 | 0.021478709 | 0.002382295 |
120.61 | 2163.780029 | 0.010387853 | -0.00143517 |
121.92 | 2173.02002 | 0.010861429 | 0.004270301 |
120.61 | 2165.169922 | -0.01074473 | -0.00361253 |
121 | 2175.030029 | 0.003233554 | 0.004553965 |
121.63 | 2168.47998 | 0.005206587 | -0.00301148 |
121.22 | 2169.179932 | -0.00337085 | 0.000322785 |
123.34 | 2166.580078 | 0.017488822 | -0.00119854 |
125 | 2170.060059 | 0.013458765 | 0.001606209 |
123.94 | 2173.600098 | -0.00847998 | 0.001631309 |
124.31 | 2170.840088 | 0.002985283 | -0.00126979 |
123.09 | 2157.030029 | -0.00981419 | -0.00636162 |
122.51 | 2163.790039 | -0.00471195 | 0.003133943 |
124.36 | 2164.25 | 0.0151008 | 0.000212572 |
125.15 | 2182.870117 | 0.006352533 | 0.008603496 |
125.26 | 2180.889893 | 0.000878945 | -0.00090717 |
125.06 | 2181.73999 | -0.00159671 | 0.000389794 |
124.88 | 2175.48999 | -0.00143932 | -0.00286469 |
124.9 | 2185.790039 | 0.000160194 | 0.004734588 |
124.88 | 2184.050049 | -0.00016017 | -0.00079605 |
123.9 | 2190.149902 | -0.00784749 | 0.002792909 |
123.3 | 2178.149902 | -0.00484261 | -0.00547908 |
124.37 | 2182.219971 | 0.008678021 | 0.00186859 |
123.91 | 2187.02002 | -0.00369863 | 0.002199617 |
123.56 | 2183.870117 | -0.00282468 | -0.00144027 |
124.15 | 2182.639893 | 0.004775041 | -0.00056332 |
124.37 | 2186.899902 | 0.001772058 | 0.001951769 |
123.48 | 2175.439941 | -0.00715607 | -0.00524028 |
123.89 | 2172.469971 | 0.003320343 | -0.00136523 |
124.96 | 2169.040039 | 0.008636694 | -0.00157882 |
126.54 | 2180.379883 | 0.012644062 | 0.005228047 |
125.84 | 2176.120117 | -0.00553189 | -0.00195368 |
126.12 | 2170.949951 | 0.002225103 | -0.00237586 |
126.17 | 2170.860107 | 0.000396408 | -4.1385E-05 |
126.51 | 2179.97998 | 0.002694809 | 0.004201041 |
129.73 | 2186.47998 | 0.025452486 | 0.002981679 |
131.05 | 2186.159912 | 0.010175033 | -0.00014639 |
130.27 | 2181.300049 | -0.00595192 | -0.00222301 |
127.1 | 2127.810059 | -0.02433412 | -0.02452207 |
128.69 | 2159.040039 | 0.012509866 | 0.014677053 |
127.21 | 2127.02002 | -0.01150053 | -0.01483067 |
127.77 | 2125.77002 | 0.004402154 | -0.00058768 |
128.35 | 2147.26001 | 0.004539477 | 0.010109273 |
129.07 | 2139.159912 | 0.005609669 | -0.00377229 |
128.65 | 2139.120117 | -0.00325415 | -1.8603E-05 |
128.64 | 2139.76001 | -7.7691E-05 | 0.000299138 |
129.94 | 2163.120117 | 0.010105745 | 0.010917162 |
130.08 | 2177.179932 | 0.00107742 | 0.006499785 |
127.96 | 2164.689941 | -0.01629769 | -0.00573677 |
127.31 | 2146.100098 | -0.00507972 | -0.00858776 |
128.69 | 2159.929932 | 0.010839714 | 0.00644417 |
129.23 | 2171.370117 | 0.004196084 | 0.005296554 |
128.09 | 2151.129883 | -0.00882148 | -0.00932141 |
128.27 | 2168.27002 | 0.001405324 | 0.007967969 |
128.77 | 2161.199951 | 0.003898027 | -0.0032607 |
128.19 | 2150.48999 | -0.00450417 | -0.00495556 |
128.47 | 2159.72998 | 0.00218425 | 0.004296691 |
128.74 | 2160.77002 | 0.002101689 | 0.00048156 |
128.99 | 2153.73999 | 0.001941898 | -0.00325348 |
130.24 | 2163.659912 | 0.009690673 | 0.004605905 |
128.88 | 2136.72998 | -0.01044226 | -0.01244647 |
129.05 | 2139.179932 | 0.001319041 | 0.001146589 |
127.82 | 2132.550049 | -0.00953121 | -0.00309926 |
127.88 | 2132.97998 | 0.000469387 | 0.000201604 |
127.54 | 2126.5 | -0.00265871 | -0.00303799 |
128.57 | 2139.600098 | 0.008075945 | 0.006160403 |
130.11 | 2144.290039 | 0.011977864 | 0.002191971 |
130 | 2141.340088 | -0.00084545 | -0.00137572 |
132.07 | 2141.159912 | 0.015923131 | -8.4142E-05 |
133.28 | 2151.330078 | 0.009161747 | 0.00474984 |
132.29 | 2143.159912 | -0.00742802 | -0.00379773 |
131.04 | 2139.429932 | -0.00944894 | -0.00174041 |
129.69 | 2133.040039 | -0.01030213 | -0.00298673 |
131.29 | 2126.409912 | 0.012337042 | -0.0031083 |
130.99 | 2126.149902 | -0.00228493 | -0.00012228 |
129.5 | 2111.719971 | -0.01137495 | -0.00678688 |
127.17 | 2097.939941 | -0.01799229 | -0.0065255 |
120 | 2088.659912 | -0.05638121 | -0.0044234 |
120.75 | 2085.179932 | 0.00625 | -0.00166613 |
122.15 | 2131.52002 | 0.011594219 | 0.022223544 |
124.22 | 2139.560059 | 0.016946369 | 0.003771974 |
123.18 | 2163.26001 | -0.00837225 | 0.011077021 |
120.8 | 2167.47998 | -0.01932129 | 0.001950746 |
119.02 | 2164.449951 | -0.01473515 | -0.00139795 |
115.08 | 2164.199951 | -0.03310364 | -0.0001155 |
117.2 | 2180.389893 | 0.018421924 | 0.007480798 |
116.34 | 2176.939941 | -0.00733789 | -0.00158226 |
117.79 | 2187.120117 | 0.012463513 | 0.00467637 |
117.02 | 2181.899902 | -0.00653709 | -0.0023868 |
121.77 | 2198.179932 | 0.040591353 | 0.007461401 |
121.47 | 2202.939941 | -0.00246363 | 0.002165432 |
120.84 | 2204.719971 | -0.00518651 | 0.000808025 |
120.38 | 2213.350098 | -0.00380668 | 0.003914387 |
120.41 | 2201.719971 | 0.000249269 | -0.00525454 |
120.87 | 2204.659912 | 0.003820272 | 0.001335293 |
118.42 | 2198.810059 | -0.02026975 | -0.0026534 |
115.1 | 2191.080078 | -0.02803581 | -0.00351553 |
115.4 | 2191.949951 | 0.002606464 | 0.000397006 |
117.43 | 2204.709961 | 0.01759097 | 0.005821305 |
117.31 | 2212.22998 | -0.0010219 | 0.003410888 |
117.95 | 2241.350098 | 0.005455622 | 0.013163242 |
118.91 | 2246.189941 | 0.008139102 | 0.002159343 |
119.68 | 2259.530029 | 0.006475452 | 0.005938985 |
117.77 | 2256.959961 | -0.01595925 | -0.00113743 |
120.31 | 2271.719971 | 0.021567471 | 0.006539775 |
120.21 | 2253.280029 | -0.00083118 | -0.00811717 |
120.57 | 2262.030029 | 0.002994768 | 0.003883228 |
119.87 | 2258.070068 | -0.00580573 | -0.00175062 |
119.24 | 2262.530029 | -0.00525574 | 0.001975121 |
119.09 | 2270.76001 | -0.00125798 | 0.003637512 |
119.04 | 2265.179932 | -0.00041981 | -0.00245736 |
117.4 | 2260.959961 | -0.01377687 | -0.00186297 |
117.27 | 2263.790039 | -0.00110737 | 0.001251715 |
118.01 | 2268.879883 | 0.006310267 | 0.002248373 |
116.92 | 2249.919922 | -0.00923654 | -0.00835653 |
116.35 | 2249.26001 | -0.00487513 | -0.0002933 |
115.05 | 2238.830078 | -0.01117314 | -0.00463705 |
Variance FB | 0.00031801 | 7.45243E-05 | |
Covariance | 7.45243E-05 | ||
Beta | 0.234345857 |
variance formula in excel: =VAR.P(H3:H254) where h3 to h 254 are
daily returns of facebook
Covariance formula excel: =COVARIANCE.P(H3:H254,I3:I254) where h3
to h 254 and i3 to i254 are daily returns of facebook and S&P
500 respectively
Now beta = 0.2343
Rf= 2.5
E(Rm)=7.5
As per CAPM: E(r) of facebook = Risk free rate
(Rf)+Beta[E(Rm)-Rf
=2.5+0.2343*(7.5-2.5)=3.67%