Excel Function: Looking for Alphas

Alpha measures the excess of returns in investments against some benchmark. For Example, it means if an investor is expected to earn X% in Apple, she probably would get 2% extra. This abnormal returns are usually measured using a similar CAPM approach. One way to do it is running a linear regression between the asset return and the market return (SP&500) both adjusted by a fixed return (Treasury Bill). I used 60 months (5 years) of historical data to do the estimation. I calculated the Alphas for the first 20 companies with the biggest market capitalization. In my calculations only Philip Morris International (consumer staples industry) showed an Alpha statistically significant at the 5% level. Its Alpha return was around 1.2% (14% annually). In the same direction, Apple showed the highest Alpha in the sample of around 2% (24% annually), but the alpha coefficient was not statistically significant at the 5% level.

Technical details: I used the Stock Market Functions add-in to get automatically the data for the end of the month (link). The Excel functions that I used were:

• Linest(Range Y,Range X,TRUE, TRUE): Get the main statistics of a regression in array format
• Index(Array, Row, Column): Extract from an array format (Linest) specific indicators. In particular, I used to get the std error from Beta and Alpha
• Slope(Range Y,Range X): Get directly the slope coefficient from a regression
• Intercept(Range Y,Range X): Get directly the intercept from a regression

The excel file with the calculations is here. If you found this helpful, please share.

Alpha Calculations 

(Monthly returns)