Assig Beta

BWFF2033 FINANCIAL MANAGEMENT ASSIGNMENT 2 QUESTION 2 1) Go to finance.yahoo.com and download the ending monthly stock prices for ColgatePalmolive for the last 60months. Use the adjusted closing price, which adjusts for dividend payments and stock splits. Next, download the ending value of the S&P 500 index over the same period. For the historical risk-free rate, go to the St. Louis Federal Reserve website (www.stlouisfed.org) and find the three-month Treasury bill secondary market rate. Download this file. What are the monthly returns, average monthly returns, and standard deviations for Colgate-Palmolive stock, the three-month Treasury bill, and the S&P 500 for this period?

Date 4/30/2012 5/1/2012 6/1/2012 7/2/2012 8/1/2012 9/4/2012 10/1/2012 11/1/2012 12/3/2012 1/2/2013 2/1/2013 3/1/2013 4/1/2013 5/1/2013 6/3/2013 7/1/2013 8/1/2013 9/3/2013 10/1/2013 11/1/2013 12/2/2013 1/2/2014 2/3/2014 3/3/2014 4/1/2014 5/1/2014 6/2/2014 7/1/2014 8/1/2014 9/2/2014

Monthl Monthly y Adj Close Return Adj Close Return (S&P 500) (S&P 500) （CL） (CL) 44.268944 1397.910034 43.982586 -0.01 1310.329956 -0.0626507 46.57769 0.06 1362.160034 0.039555 48.319962 0.04 1379.319946 0.0125976 47.847382 -0.01 1406.579956 0.0197634 48.256954 0.01 1440.670044 0.0242362 47.506042 -0.02 1412.160034 -0.0197894 49.108288 0.03 1416.180054 0.0028467 47.315948 -0.04 1426.189941 0.0070682 48.874817 0.03 1498.109985 0.0504281 52.088528 0.07 1514.680054 0.0110606 53.727245 0.03 1569.189941 0.0359877 54.668953 0.02 1597.569946 0.0180858 52.961262 -0.03 1630.73999 0.0207628 52.457653 -0.01 1606.280029 -0.0149993 55.138859 0.05 1685.72998 0.0494621 53.204811 -0.04 1632.969971 -0.031298 54.613903 0.03 1681.550049 0.0297495 59.936188 0.10 1756.540039 0.0445958 60.936199 0.02 1805.810059 0.0280495 60.380634 -0.01 1848.359985 0.0235628 56.994709 -0.06 1782.589966 -0.0355829 58.484039 0.03 1859.449951 0.043117 60.382935 0.03 1872.339966 0.0069322 62.982357 0.04 1883.949951 0.0062008 64.011787 0.02 1923.569946 0.0210303 63.805897 0.00 1960.22998 0.0190583 59.640781 -0.07 1930.670044 -0.0150798 60.891922 0.02 2003.369995 0.0376553 61.352867 0.01 1972.290039 -0.0155138

Date

4/1/2012 5/1/2012 6/1/2012 7/1/2012 8/1/2012 9/1/2012 10/1/2012 11/1/2012 12/1/2012 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 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

Monthly TB3MS Return (TB3MS) 0.08 0.09 0.125 0.09 0 0.10 0.111111 0.10 0 0.11 0.1 0.10 -0.090909 0.09 -0.1 0.07 -0.222222 0.07 0 0.10 0.428571 0.09 -0.1 0.06 -0.333333 0.04 -0.333333 0.05 0.25 0.04 -0.2 0.04 0 0.02 -0.5 0.05 1.5 0.07 0.4 0.07 0 0.04 -0.428571 0.05 0.25 0.05 0 0.03 -0.4 0.03 0 0.04 0.333333 0.03 -0.25 0.03 0

10/1/2014 11/3/2014 12/1/2014 1/2/2015 2/2/2015 3/2/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/3/2015 9/1/2015 10/1/2015 11/2/2015 12/1/2015 1/4/2016 2/1/2016 3/1/2016 4/1/2016 5/2/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/3/2016 11/1/2016 12/1/2016 1/3/2017 2/1/2017 3/1/2017 4/3/2017

63.268055 65.831696 65.4533 64.208687 67.346848 65.93943 64.337578 63.869007 62.549362 65.411827 60.401604 61.026676 64.169479 63.521503 64.430611 65.710541 63.871464 68.746483 69.386833 68.887863 71.617538 73.203865 73.115341 72.91864 70.568283 64.506294 64.713959 64.232658 72.587479 72.796349 73.300003

0.03 0.04 -0.01 -0.02 0.05 -0.02 -0.02 -0.01 -0.02 0.05 -0.08 0.01 0.05 -0.01 0.01 0.02 -0.03 0.08 0.01 -0.01 0.04 0.02 0.00 0.00 -0.03 -0.09 0.00 -0.01 0.13 0.00 0.01

2018.050049 0.0232015 2067.560059 0.0245336 2058.899902 -0.0041886 1994.98999 -0.0310408 2104.5 0.0548925 2067.889893 -0.0173961 2085.51001 0.0085208 2107.389893 0.0104914 2063.110107 -0.0210117 2103.840088 0.019742 1972.180054 -0.0625808 1920.030029 -0.0264428 2079.360107 0.0829831 2080.409912 0.0005049 2043.939941 -0.0175302 1940.23999 -0.0507353 1932.22998 -0.0041284 2059.73999 0.0659911 2065.300049 0.0026994 2096.949951 0.0153246 2098.860107 0.0009109 2173.600098 0.0356098 2170.949951 -0.0012192 2168.27002 -0.0012345 2126.149902 -0.0194257 2198.810059 0.0341745 2238.830078 0.0182008 2278.870117 0.0178844 2363.639893 0.0371982 2362.719971 -0.0003892 2388.77002 0.0110254

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 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

0.02 -0.333333 0.02 0 0.02 0 0.03 0.5 0.03 0 0.02 -0.333333 0.03 0.5 0.02 -0.333333 0.02 0 0.02 0 0.03 0.5 0.07 1.333333 0.02 -0.714286 0.02 0 0.12 5 0.23 0.916667 0.26 0.130435 0.31 0.192308 0.29 -0.064516 0.23 -0.206897 0.27 0.173913 0.27 0 0.30 0.111111 0.30 0 0.29 -0.033333 0.33 0.137931 0.45 0.363636 0.51 0.133333 0.51 0 0.52 0.019608 0.74 0.423077

Average monthly returns

Variance

Standard deviations

Colgate-Palmolive (CL)

0.01

0.0014883589

0.0385792544

S&P 500

0.0093909528

0.0008449246

0.0290675865

TBTMS

0.1517960485

0.545915084

0.7388606662

2) Beta is often estimated by linear regression. A model commonly used is called the market model, which is: Rt - Rft = αi + betai [Rmt - R ft] + Et In this regression, Rt is the return on the stock and Rft is the risk-free rate for the same period. RMt is the return on a stock market index such as the S&P 500 index. αi is the regression intercept, and βi is the slope (and the stock’s estimated beta). εi represents the residuals for the regression. What do you think is the motivation for this particular regression? The intercept, αi, is often called Jensen’s alpha. What does it measure? If an asset has a positive Jensen’s alpha, where would it plot with respect to the SML? What is the financial interpretation of the residuals in the regression? ANSWER The motivation for this regression is to relate the returns from the stock to the returns from market. This helps in understanding and predicting the stock returns from the benchmark returns .In a regression, we realize that there is some indeterminate error, so we need to formally recorgnize this in the equation by adding epsilon which represents the residuals for the regression εi. Since there is no intercept in the equation, the intercept is zero, we can add an intercept term which called alpha. Jensen's alpha is represents the "excess" return. If CAPM holds exactly, intercept should zero. If a stock has positive jensen’s alpha, it would plot above the SML.If a stock has negative jensen’s alpha, it would plot below the SML.The residuals are error factors, unexplainable movement in the stock price due to stock specific issues. 3) Use the market model to estimate the beta for Colgate-Palmolive using the last 36 months of returns (the regression procedure in Excel is one easy way to do this). Plot the monthly returns on Colgate-Palmolive against the index and also show the fitted line. ANSWER Beta =

0.736127716

SUMMARY OUTPUT Regression Statistics Multiple R 0.546966964 R Square 0.29917286 Adjusted R S 0.278560297 Standard Err 0.0341985 Observations 36

ANOVA df Regression Residual Total

SS MS F Significance F 1 0.01697 0.0169747863 14.5141029 0.000556649 34 0.03976 0.0011695374 35 0.05674

Coefficients Standard Error t Stat P-value Lower 95% Intercept -0.00018643 0.00586 -0.031812249 0.9748078 -0.01209572 S&P 500 0.736127716 0.19322 3.8097379048 0.00055665 0.343452013 Upper 95%Lower 95.0%Upper 95.0% Intercept 0.011722868 -0.0121 0.0117228681 S&P 500 1.128803419 0.34345 1.1288034194 RESIDUAL OUTPUT Observation 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

Predicted Y Residuals 0.015294547 0.00105 0.013842941 -0.01706 -0.01128711 -0.05399 0.027532682 -0.00655 -0.01160659 0.01918 0.016892813 0.01432 0.01787343 0.02265 -0.00326976 -0.00248 -0.02303642 0.00402 0.040221474 0.00865 -0.01299218 -0.00791 0.006085986 -0.03038 0.007536572 -0.01482 -0.0156537 -0.00501 0.01434623 0.03142 -0.0462539 -0.03034 -0.01965173 0.03 0.060899748 -0.0094 0.000185223 -0.01028 -0.01309088 0.0274 -0.0375341 0.0574 -0.00322543 -0.02476 0.048391463 0.02793 0.001800677 0.00751 0.011094439 -0.01829 0.000484129 0.03914 0.026026936 -0.00388

-0.00108394 -0.00013 -0.00109514 -0.0016 -0.01448621 -0.01775 0.024970388 -0.11087 0.01321166 -0.00999 0.012978747 -0.02042 0.027196172 0.10288 -0.00047292 0.00335 0.007929714 -0.00101

C olgate-Palmolive

28 29 30 31 32 33 34 35 36

BETA FOR COLGATE-PALMOLIVE FOR LAST 36 MONTHLY

0.15 0.10 0.05 - 0.0001864251 f(x) = 0.7361277164x -0.08

-0.06

-0.04

0.00 -0.02 0 -0.05

0.02

0.04

0.06

-0.10

S&P 500 for last 36 monthly

，。 M

0.08

0.1

Regression Statistics Multiple R 0.572024082 R Square 0.327211551 Adjusted R Squa 0.31561175 Standard Error 0.03218509 Observations 60

Beta =

0.7592051932

ANOVA df Regression Residual Total

1 58 59

SS MS F Significance F 0.0292204929 0.0292205 28.2083765825 1.8013616E-06 0.0600810395 0.0010359 0.0893015324

Coefficients Standard Error t Stat P-value Lower 95% 0.002045325 0.0043665417 0.4684085 0.6412481427 -0.0066952576 0.759205193 0.1429453801 5.3111559 1.8013616E-06 0.4730689267

Intercept X Variable 1

Upper 95% 0.0107859081 1.0453414598

Lower 95.0% Upper 95.0% -0.0066952576 0.010785908 0.4730689267 1.04534146

RESIDUAL OUTPUT

1 2 3 4

Predicted Y -0.04551943 0.032075673 0.011609469 0.017049778

Residuals 0.0390508331 0.0269273151 0.0257962504 -0.026830001

BETA FOR COLGATE-PALMOLIVE FOR LAST 60 MONTHLY 0.15 0.10

live

Observation

BETA FOR COLGATE-PALMOLIVE FOR LAST 60 MONTHLY 0.15

0.020445539 -0.0129789 0.004206568 0.007411562 0.040330598 0.010442628 0.029367392 0.015776134 0.01780856 -0.00934222 0.039597193 -0.02171629 0.024631318 0.035902652 0.02334063 0.019934319 -0.0249694 0.034779998 0.007308261 0.006752996 0.018011623 0.01651451 -0.00940336 0.030633421 -0.00973286 0.019659995 0.020671353 -0.00113467 -0.02152102 0.043720005 -0.01116189 0.008514376

-0.0118855722 -0.0025818028 0.0295206371 -0.0439092714 -0.007384645 0.0553112964 0.0020928368 0.001751435 -0.0490455033 -0.0001667843 0.0115146247 -0.0133596591 0.001852979 0.0615502803 -0.0066560351 -0.0290514777 -0.0311069391 -0.0086489762 0.025160358 0.0362959544 -0.001666887 -0.0197309485 -0.0558745558 -0.0096554765 0.0173027481 0.0115559548 0.0198489536 -0.0046132578 0.0025057331 0.0051543945 -0.0097361635 -0.0328071548

0.10

Colgate-Palmolive

5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

f(x) = 0.7592051932x + 0.0020453252 0.05

-0.08

-0.06

-0.04

0.00 -0.02 0

0.02

0.04

-0.05 -0.10

S&P 500 for last 60 monthly

0.06

0.08

0.1

37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

0.010010437 -0.01390685 0.017033577 -0.04546636 -0.01803021 0.065046539 0.002428625 -0.01126368 -0.03647319 -0.00108895 0.052146122 0.004094723 0.013679843 0.002736901 0.029080471 0.001119669 0.001108124 -0.01270275 0.0279908 0.015863438 0.015623223 0.030286362 0.001749845 0.010415903

-0.0172934437 -0.0067548973 0.0287297198 -0.0311287059 0.0283788092 -0.0135476995 -0.0125265093 0.0255754974 0.0563384417 -0.0268986017 0.0241793399 0.0052199358 -0.0208709767 0.0368880033 -0.0069304916 -0.0023289499 -0.0037984073 -0.0195298437 -0.1138932584 -0.0126441401 -0.0230605827 0.0997848709 0.0011276488 -0.0034972323

5  Compare your beta for Colgate-Palmolive to the beta you find on  finance.yahoo.com. How similar are they? Why might they be different?

ANSWER