Toronto Team  

  • T416.203.0067
  • 1.866.242.0203
  • F416.203.0544
  • 8 Wellington Street East
    3rd Floor
  • Toronto, Ontario M5E 1C5

How to Run a 3-Factor Regression Analysis

November 6, 2013 - 12 comments

In his recent blog, Dan Bortolotti, discusses the basics of factor analysis.  If you are one of the few who are actually interested in running your own 3-factor regressions, pull up your sleeves and let’s get started.

I’ve attached the 2008-2012 monthly Canadian regression factors from Andrea Frazzini’s Data Library (along with various ETF and fund returns that Dan Bortolotti will be discussing in his upcoming posts.  The data is all in US dollars (so don’t input any monthly fund returns in Canadian dollars). 

You may also notice a 4th factor, UMD (up minus down) – this is the Canadian “momentum” factor.  Dan will be sure to explain this factor in more detail when he discusses the Morningstar Canada Value and Momentum indices.

Please feel free to try it out for yourself and send me a quick email with any questions you may have.

Step 1:  Copy and paste the monthly returns of the desired fund into column B (starting in cell B2).

In the example below, we’ve chosen to analyze the monthly returns of the Beutel Goodman Canadian Equity Fund Class D (BTG770) from January 2008 to December 2012.  BTG770 had a 5-year annualized return of 2.6% as of December 2008, while its benchmark index, the S&P/TSX Composite, returned only 0.8% annualized over the same period. 

Step 2:  Select the Data tab and then click on Data Analysis

If you do not see the Data Analysis option at the far right, please continue to Step 3.  If this option is visible, please skip ahead to Step 6.

Step 3:  Select the File tab in the top left of the screen and click Options

Step 4:  Click Add-Ins and then click Go

Step 5:  Select the Analysis ToolPak check-box, and then click OK (return to Step 2)

Step 6:  Scroll down until you find the Regression tool - highlight it and then click OK

Step 7:  Fill in the regression inputs

Input Y Range:  Select all the cells with data in column D (including the Fund-TBill labels).

Input X Range:  Select all the cells with data in columns E through G (including the Mkt-TBill, SMB and HML labels)

Labels:  Select the Labels check-box

New Worksheet Ply:  Give a name to your new worksheet – in the example below, we’ve used the fund code BTG770.  Click OK

Summary Output

My colleague, Dan Bortolotti, did an excellent job explaining the main outputs of a regression analysis in his recent blog Going on a Factor-Finding Mission.  We’ve summarized our results below for the Beutel Goodman Canadian Equity Fund Class D (BTG770):

Adjusted R Square:  This tells you how well the data fit the model. In this case, a figure of +0.9644 indicates the three factors we’ve analyzed explain 96.44% of the monthly performance of BTG770. It’s a fairly tight fit (a value of +1 would be even better).

t Stat:  This value tells you whether or not the results are significant.  An absolute value of 2 or more (i.e. more than +2 or less than -2) means that you should probably pay attention to the results.

Intercept:  You can think of this as a fund manager’s “alpha” (it can be either positive or negative).  As this is a monthly regression, the alpha is also a monthly value (so multiply the result by 12 to get an approximate annual alpha value).  In our example, the annual alpha is about +0.07% (0.005730188 × 12), but the results are not significant (with a t Stat of only 0.32).

Mkt-TBill:  This is referred to in finance as “beta”.  A fund with a beta of more than +1 is more equity-like (relative to the market index) while a fund with a beta less than 1 is less equity-like than the market index.  Our fund has a beta of +0.85 (with a t Stat of +36.65), so it is less equity-like than the index.

SMB:  This is the small cap “slope co-efficient” - it measures the portfolio’s sensitivity to the small cap risk factor.  Since this value is -0.21 (with a t Stat of -2.94), we are probably dealing with a large cap fund. 

HML:  This is the value “slope co-efficient” – it measures the portfolio’s sensitivity to the value risk factor.  Since this value is +0.28 (with a t Stat of +6.20), we are probably dealing with a value fund.

So what does all of this factor-based analysis tell us?  In this example, it shows investors that the impressive outperformance of BTG770 over that 5-year period was almost entirely due to its exposure to known risk factors (which they could’ve gained exposure to through lower-cost index funds).  Its true alpha plummets from 1.8% per year, to a measly 0.07% per year.  As William Bernstein so eloquently put it in his Efficient Frontier blog:

“Factor analysis is to active money managers, what a light switch is to cockroaches.”

By: Justin Bender with 12 comments.
  18/03/2016 8:49:24 PM
Justin Bender
@Cro-magnon - AQR has likely updated their data over time, so it may not be identical. I tend to keep the AQR data in US dollars, and convert the CAD index or fund data into US dollars when running the regressions.
  17/03/2016 7:18:28 PM
Thanks for the thorough post Justin. I tried to implement this by downloading the longer data series from AQR and for some reason the 2008-2012 data that you use here and that I noticed in the set I downloaded didn't match up exactly. Could this be due to the exchange rate? Is the data you use CDN denominate? if so, would you have access to a longer series?
thank you.
  06/12/2013 9:23:35 AM
Justin Bender
@Jon - I wouldn't say using a combination of VBR + VTV would be a mistake, but it would be expected to have relatively high tracking error to something like VTI (whether this tracking error is positive or negative will depend on the returns to the value and small factors, as well as other product differences - such as fees or sector weightings). If you are comfortable with the risks, I don't see issues with this type of arrangement.
  05/12/2013 7:31:05 PM
Hello Justin, that's a great point. My decision to tilt has more to do with my portfolio size than what's going on in the markets. I've written in my IPS to add these components 2 yrs from now and I'm just trying to learn more about it at this time.

On a similar note, does it only matter your degree of exposure to 3 factors? ie: if your US exposure was only comprised of a small value ETF like VBR and a large value ETF like VTV, would that be a big mistake? I gather you would have low to no exposure to growth and I'd have to look at specific sector exposure in each individual ETF, but if returns are determined by the 3 factors does it matter?

  04/12/2013 3:31:13 PM
Justin Bender
@Jon - I can tell you that when Canadian stocks were outperforming U.S. and International stocks years ago, most clients were asking me if they should have more Canadian stocks in their portfolio. Now that U.S. and International stocks are outperforming Canadian stocks, clients are wondering if they should have less Canadian stocks. This is a common behavioural trait for investors - we always want to buy what has been doing well and sell what has been doing poorly.

If you already had a well thought out plan, why would you want to suddenly change it?

  04/12/2013 2:31:21 PM
I'm trying to add small and value tilts in my US and developed markets holdings. I haven't done this for Canadian holdings because I think the market is small and there aren't fantastic options (maybe a poor assumption). I've tried to keep my Canadian holdings of similar size to the other assets because of preferential tax treatment, but am finding the pie getting cut quite thin. Do you ever suggest your Canadian clients reduce their exposure to Canadian equities or would the tax treatment of these equities far outweigh the benefits from tilting to US or international equities?

  08/11/2013 7:09:14 AM
Justin Bender
@Troy - you may have neglected to select the "Labels" check-box in Step 7 - once you do this, you shouldn't receive the error.

I don't know of any sources for USD Canadian equity returns - I normally just convert them using the Dimensional Returns program. If you have the monthly returns for a specific fund that you would like converted, just send it along to me and I'll see what I can do.
  07/11/2013 5:28:10 PM
Troy Redick
Hi Justin!
Great walk-through for those of us interested in the factor analysis, but just learning the math and excel tools! As a note, when selecting the Y data range, when including the non-numerical data it creates an error.

Do you know of a free-to-use, intuitevely easy to acess database of Canadian fund returns in USD?

  07/11/2013 3:23:01 PM
Justin Bender
@Jake - you're probably looking in the right spot - but Frazzini did not organize the Canadian data in a way that makes it easy to run a 3-factor regression. I've done this for the 2008-2012 years, but his data goes back even further to the 1980s.

If you're interested in more historical data, please send me a quick email request and I'll send it to your attention.

  07/11/2013 3:18:43 PM
Jacob Caldwell
Does Andrea Frazzani have info dating back past 2008 for Canada? If so, how far back, and where can it be found? Thanks!
  07/11/2013 12:12:55 PM
Thanks so much for the detailed explanation!
  07/11/2013 12:09:32 PM
Jacob Caldwell
This is great! I've been reading about regressions for years and have always wondered about thanks! I do have one question though (for now). I can't seem to find the excel spreadsheet you're using (the first image) from Frazzini's Data it a smaller part of a larger spreadsheet or am I just not looking in the right spot? Jake

 Security code