A Fama French 3 Factor Model

The Fama French three-factor model is an asset pricing model developed in 1992 that expands on the capital asset pricing model by adding size risk and value risk factors to the market risk factor in CAPM. By including these factors, the model adjusts for outperforming tendencies in value and small-cap equities. 

The formula goes like this: 

                                            Ri-Rf = R ai + bi(RM-Rf) + siSMB + hiHM    

Where... 

               Ri = total return of a stock or portfolio i at time t 

               Rf = risk-free rate of return at time t 

               R ai = alpha of the portfolio i at time t 

               bi,si,hi = Betas 

               RM-Rf = Return of market - risk free rate 

               SMB = size premium 

               HM = value premium 

This can be used in absolute valuation models as the cost of equity, replacing the CAPM method with the FF3M as the FF3M is more accurate ( this is proven in my model as the linear regression analysis showed a higher R^2 when using the FF3M (98.66%) vs CAPM (97.76%) when analyzing SPY). 

To use my model, you have to get the data from Kenneth French's website, where they release the data for Mkt-Rf, SMB, HML, and the T bill 1Mo rate. 

Once you download the data in excel, 

                1. Copy and paste the French data on the first sheet 

                2. Download the historical data of a stock of your choice from any source (yahoo is good) 

                3. Copy and paste the data in the same format in one of the 6 sheets 

                4. Copy and paste the formula in monthly returns and monthly excess returns 

                5. Using Anova in excel, perform a linear regression analysis 

                6. Set Y: monthly excess returns 

                7. Set X: Mkt-Rf, SMB,HML, Tbill(1Mo) 

                8.Copy and paste this analysis in the order matching sheet (ex/ CAH FF3F) 

                9. Find the current 10yr treasury bill yield online, enter it in the cell labeled

After you complete all these steps, you will be given the required return, input this into your absolute valuation model of choice, and find that intrinsic value. 

https://docs.google.com/spreadsheets/d/1Q8GEPy-S6078cyk5xUm0ucbqFWJMjaq0wozfWt5skuY/edit?usp=sharing 

**Again, open this in excel.