Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
Fundamental analysisGuru StrategiesScreeners

Magic Formula Investing Screener In Excel

Written by admin
Wed May 31 2017
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
 

Magic formula investing is a well-known investment technique popularized by Joel Greenblatt in his book “The Little Book That Still Beats the Market”. This is a value investing approach that outlines how investors can systematically apply a formula to buy stocks in good companies at affordable prices. In magic formula investing, we rank companies by their return on capital and earnings yield, and then buy the stocks with the best combined rank.

Put another way, the magic formula invests in companies through a ranking system. The higher the return on capital and higher the earnings yield for a company, the higher the rank this company enjoys.

Joel Greenblatt has praised the magic formula in his book ‘The Little Book that Beats the Market’. He cites that it does in fact beat the S&P 500 96% of the time, and has averaged a 17-year annual return of 30.8%. (Source: https://www.gurufocus.com/news/802)

The step-by-step magic formula is described below:

Magic Formula Investing

Step 1 

Establish a minimum market capitalization (such as $50 million) to get a list of all stocks that meet the criteria.

Step 2

Exclude utility and financial stocks

Step 3

Exclude foreign companies (American Depository Receipts)

Step 4

Determine Earnings Yield of the companies

Greenblatt uses the concept of earnings yield in order to find out how much a business earns relative to the purchase price of the business.

Magic Formula Investing

Enterprise value (EV) is the sum of market value of equity (including preferred equity) and net interest bearing debt.

Step 5

Determine Return on Capital of companies

Greenblatt defines return on capital by measuring the ratio of the 12 month trailing EBIT (or operating earnings before interest and taxes) to tangible capital employed.

Using EBIT allows investors to compare the operating earnings of different companies without the distortions resulting from differences in tax rates and debt levels.

Tangible capital employed is measured as the sum of net working capital and net fixed assets.

Magic Formula Investing

Step 6

Finally, rank all the stocks by highest earnings yield and highest return on capital

Step 7

Invest in 20-30 highest ranked companies. Do so by accumulating 2-3 positions per month over a 12-month period.

Magic Formula Investing also recommends that you re-balance portfolio once per year. While rebalancing, sell losers one week before the year-mark and winners one week after the year mark. Furthermore, you should sell close to the intrinsic value. You should also sell if something even cheaper is found. Overall you need to stay invested for 3-5 years.

Greenblatt believes that magic formula investing provides returns far superior above the market averages, and more significantly, it achieves those returns on much lower risk than the overall market.

Magic Formula Investing Screener in Excel

MarketXLS will allows to easily get the list of stocks ranked by magic formula with just a few clicks.

We will update the instructions shortly when the screen is complete.

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONSGet started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

Dick's Sporting Goods Inc logo

Dick's Sporting Goods Inc

110.57
 
USD
 
8.33
 
(8.15%)
83
Rank
Optionable: Yes
Market Cap: 8,841 M
Industry: Specialty Retail
52 week range    
63.45   
   142.78
Academy Sports and Outdoors Inc logo

Academy Sports and Outdoors Inc

45.32
 
USD
 
2.41
 
(5.62%)
79
Rank
Optionable: Yes
Market Cap: 3,614 M
Industry: Beer, Wine, and Liquor Stores
52 week range    
25.10   
   51.51
Annaly Capital Management Inc logo

Annaly Capital Management Inc

19.40
 
USD
 
-1.20
 
(-5.83%)
77
Rank
Optionable: Yes
Market Cap: 8,363 M
Industry: REIT - Diversified
52 week range    
5.45   
   22.78
STORE Capital Corporation logo

STORE Capital Corporation

31.63
 
USD
 
0.13
 
(0.41%)
75
Rank
Optionable: Yes
Market Cap: 8,941 M
Industry: REIT - Diversified
52 week range    
24.48   
   36.13
Waste Connections Inc. logo

Waste Connections Inc.

137.68
 
USD
 
-1.75
 
(-1.26%)
73
Rank
Optionable: Yes
Market Cap: 37,037 M
Industry: Waste Management
52 week range    
113.50   
   148.20
Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

100.84
 
USD
 
4.30
 
(4.45%)
73
Rank
Optionable: Yes
Market Cap: 7,684 M
Industry: Auto & Truck Dealerships
52 week range    
88.58   
   126.93
General Mills Inc. logo

General Mills Inc.

77.97
 
USD
 
-0.69
 
(-0.88%)
72
Rank
Optionable: Yes
Market Cap: 44,923 M
Industry: Packaged Foods
52 week range    
59.14   
   81.24
Addus HomeCare Corporation logo

Addus HomeCare Corporation

93.29
 
USD
 
1.34
 
(1.46%)
72
Rank
Optionable: Yes
Market Cap: 1,500 M
Industry: Medical Care
52 week range    
68.57   
   108.11
National Research Corporation logo

National Research Corporation

39.70
 
USD
 
0.18
 
(0.46%)
71
Rank
Optionable: No
Market Cap: 948 M
Industry: Diagnostics & Research
52 week range    
30.93   
   52.36
Williams-Sonoma Inc. (DE) logo

Williams-Sonoma Inc. (DE)

123.31
 
USD
 
2.12
 
(1.75%)
69
Rank
Optionable: Yes
Market Cap: 8,226 M
Industry: Specialty Retail
52 week range    
101.58   
   223.32

More Features