Calculating the Beta

The goal of this case study posting is to analyze the numbers for Colgate-Palmolive stocks over time, and to assess the validity of the beta value provided by an outside vendor. This posting details how information is gathered and then proceeds to detail the analytical process from regression, to beta, the SML, Jensen’s Alpha, residual, and finally interpretation of the beta.

Finding the Numbers

To find this information, the first thing to do is to go to (Yahoo Finance. Next, find the Get Quotes field and type in colgate, which brings up a list including stock ticket CL, Colgate-Palmolive Co., listed as an equity on the NYSE. The resulting URL helps to retrieve information in the following sequence:

  1. Firstly, find CL adjusted closing prices at the start of each month from Apr 1, 2008 through to Apr 1, 2013 (the date sorted order is reversed between the website download and the embedded spreadsheet in point 4 below). Note that adjusted closing prices for stock ticker CL are listed on as Adj Close*, and appear to be very different to the prices listed on the given spreadsheet (for this case study). The numbers on are being used given the assumption of one or more stock splits that the adjusted closing price takes into account, given that the most recent stock split for CL was in March 2013 (
  1. Next find the ending values of the S&P 500 for the same date range, using the ticket symbol ^GSPC, as described in the given spreadsheet for this case study (Adj Close S&P 500 (^GSPC)); again not forgetting to sort into reverse date order.
  1. Assessing risk requires the risk free rate that can be found at the St. Louis Federal Reserve website at The given case study spreadsheet appears to use a ticker symbol called WTB3MS; the closest symbol that can be found is TB3MS, which appears to be correct. (Federal Reserve Bank of St. Louis. 3-Month Treasury Bill: Secondary Market Rate (TB3MS) – see federal reserve site has data in the required order from oldest to newest dates so the order does not have to be changed. Comparing numbers between the assigned spread sheet and the numbers from this federal reserve website, the numbers are close but not exactly the same.
  1. Figure 1 shows a spreadsheet that contains adjusted figures and calculations, as updated using a more recent period of Apr 1, 2008 through Apr 1, 2013. in addition to updated adjusted closing values on CL, ^GSPC and the risk free treasury bill rates. Note that the adjusted closing values for CL (Colgate-Palmolive NYSE) are about half of those in the given spreadsheet for this case study, possibly because of recent adjustment downwards for a recent stock split.

Figure 1. Colgate-Palmolive Historical Stock Prices

Figure 2 shows a graphic duplication directly from the embedded spreadsheet, using the updated adjusted closing figures as extracted from Yahoo Finance.

Figure 2.Average returns and standard deviations of the updated/embedded spreadsheet

Figure 2 and the spreadsheet in Figure 1 show the following interesting issues:

  • The average monthly return (calculated by a simple percentage increase in adjusted close from month to month), is slightly higher over the last three years as compared to the last five years. This means the stock has done better in the last three years, which can probably be attributed to the 2008-2009 recession.
  • The average market returns based on the S&P 500 average monthly change, shows a better response in the last three years again. The recession of 2008-2009 can clearly be seen in the embedded spreadsheet showing a drastic descent starting around June 2008, showing signs of recovery around August 2009.
  • The treasury bill rates (Monthly Risk Free Rate or MRFR), is better over five years than it is over the last three years. This again highlights the 2008-2009 recession when investors tend to retreat to the safety of government bonds. Month by month it is difficult to see obvious changes in the MRFR because the numbers are so small. However, multiplying that rate by 1000 there is a clear pattern of descent from approximately the fall of 2008, and then remains at 0 during the recession because of safety investments in bonds, and also the rate remaining at 0 through today because the treasury is keeping the base interest rate down to help kick-start the United States economy.
  • Standard deviation can be used as a measure of market-wide and even economy-wide systemic stability, because a standard deviation is a measure of divergence from the average. So when standard deviations increase it generally means that the economy is not doing well because confidence is down. This can also be seen in Figure 2, showing lower standard deviation values in the last three years than the last five years, probably again caused by higher values during the 2008-2009 recession. Another clear indicator of market volatility is the ^VIX or the volatility index. The ^VIX is somewhat different to something like the S&P 500 PE ratio, which sometimes shows high to very high spikes at the high of some kind of economic bubble.

What is clear in both the embedded spreadsheet and in the snapshot in Figure 2 is the obvious market slump between summer 2008 and fall 2009; oh for a crystal ball. The pattern is clearly the same for all the indicators (CL, TB3MS and ^GSPC).


A regression is a term used to describe a straight line drawn as a slope to match plotted points between two axes, usually called the X and Y axis or a horizontal and a vertical axis; a graph. The idea is to draw a line matching the points as closely as possible in order to display some kind of meaningful and useful pattern.

The Beta Coefficient

A beta coefficient assesses the systemic risk of an asset like a stock in relation to an average asset with a beta of 1.0. A stock having a beta=0.5 has half the risk and beta=2.0 having twice the risk. As is often the case with economics and finance, sometimes a dictionary is a good place to help with understanding. According to, a mathematical coefficient is used to apply a constant multiplier to different numbers, effectively applying a consistent weighting to calculations. So stocks can have a beta calculated for them where a lower risk stock like Coca-Cola has a beta of 0.52, and a higher risk like Google has a beta of 2.6. This means that Coca-Cola is a safer bet and with less risk but less potential gain (or loss). Google on the other hand has higher risk but higher potential gain or loss; Google is more volatile because it is in a more volatile sector.

The SML (Security Market Line)

A regression or linear regression is a mathematical curve (straight line), which is drawn through the most likely values from the plotted points. In the case of the plotted points in the embedded spreadsheet, one plot is over five years and the other plotted set of points is over three years. Financially this curve is known as an SML or Security Market Line regression curve, which measures the performance of a stock against a systemic measure, such as a market index like the S&P 500.

Jensen’s Alpha

Clearly the slope of the line over five years is steeper than the line over three years (the beta is higher over five years). However, the two axes show much greater range over five years, even though the plotted graphs seem closer together in the five years plot. In other words if both plots were drawn to the same scale, then the three year plot would look much tighter with points closer together and closer to the regression intercept. The regression intercept is where the regression curve crosses the 0% return for CL and the S&P (Jensen’s Alpha). Also note that over five years that the regression intercept is above 0%, which indicates volatility but also perhaps the skew in the data as the stock rose rapidly post 2008-2009 recession. One might expect a value below 0% for the recession of 2008-2009 but that could be cancelled out by the doubling of stocks since the low point at the height of the Great Recession.

Jensen’s Alpha is described very well on (Jensen’s Measure. Retrieved from in this description:

“Jensen’s measure is one of the ways to help determine if a portfolio is earning the proper return for its level of risk. If the value is positive, then the portfolio is earning excess returns. In other words, a positive value for Jensen’s alpha means a fund manager has “beat the market” with his or her stock picking skills.”

In other words, if a stock is earning the return it should be then the regression intercept should be at both the 0% points of both the stock and market return lines, as shown by the three year regression curve in Figure 3.

Figure 3.CL vs. S&P SML Regression Curve over the last three years

In Figure 4 it is obvious that the regression intercept occurs to the right of the 0% ruler on the market return, implying that over the five year period that CL did better than the S&P average, which is backed by higher return percentages and risk free rate across the five year period.

Figure 4.CL vs. S&P SML Regression Curve over the last five years

Additionally, the regression curves in Figure 3 and Figure 4 have been graphically resized to get their relative scales looking the same (covering the same space). This shows the obvious high volatility for the five year period, including the higher beta value (again shown by a steeper curve), a higher intercept, and a generally far more scattered plot with more outliers; the five year period shows much less stability but with higher overall return for the CL stock.

Interpreting the Beta

Figure 5 shows the returns, deviations and beta values for CL vs. the S&P over five years and over three years.

Figure 5.CL vs. S&P for five years and three years

In general a more accurate calculation of some type of average measure will result when there are more values included in the calculation. However, in the case the of five year data there was a 50% dip in the market over a one year period, and now the market has again climbed higher than its level before the 2008-2009 Great Recession. So the higher a beta is then the higher the risk of an investment. In this situation, as shown in Figure 5, the beta value is almost twice as high over the five year period that includes the recession. As the market has climbed over the last three years the beta value has stabilized again.

When examining the beta value of CL on Yahoo of 0.32, as of May 2013, the last three years value of 0.33 looks far more realistic than the value from the last five years. The five year value of 4.7 (almost 5) is probably skewed upwards disproportionately by the 2008-2009 Great Recession and the subsequent rapid rise in the market after that recession. Copying the beta calculation within the embedded spreadsheet (covariance / variance) does give some rather high beta values during the recessionary period, some even as high as 1.0, However, as the range of values decreases the mathematical results from this calculation become less useful.


The information and analysis presented in this case study matters because it demonstrates how the beta for the CL (Colgate-Palmolive) stocks are not only volatile through the Great Recession of 2008-2009, but also how comparing and shorter and longer period of three and five years respectively, can effectively smooth out volatility; it shows that CL stock is relatively stable over a number of years, even when a very deep trough recession is present within that period. Given that beta values presented by the outside vendor are unknown, it is also unknown how well the calculated beta values in the embedded spreadsheet match beta values provided by the vendor.