Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS
BasicsFundamental analysisGuru functionsStock investments

Magic Formula Screen- Investment Strategy (Using Excel)

Written by Vanshika Kothari
Tue May 04 2021
Magic Formula Screen- Investment Strategy (Using Excel) - MarketXLS
See how MarketXLS helps you take advantage in the markets.
Download Option Template →
Magic Formula Screen- Investment Strategy (Using Excel) - MarketXLS

Magic formula investing is intended to beat the benchmark Index returns with the help of value investing. It mainly relies on various quantitative screens that are relatively simple and usually follows a rule based disciplined strategy.

The screen simply ranks stocks based on their price and returns, however it only factors large cap stock and doesn’t include any small or micro cap stocks. The magic formula strategy was first described in the 1980 best-selling book The Little Book That Beats the Market and in the 2010 follow-up, The Little Book That Still Beats the Market by investor Joel Greenblatt. Greenblatt, founder and former fund manager at Gotham Asset Management, is a graduate of the Wharton School at the University of Pennsylvania.

The two main criteria highlighted in his book for stock investing are: Stock Price and Company’s Cost of Capital. Companies are ranked based on their stock’s earnings (EBIT), yield (EPS) and return on capital.

Screening Parameters:

  • Establish a minimum market capitalization (usually greater than $50 million).
  • Exclude utility and financial stocks.
  • Exclude foreign companies (American Depositary Receipts).
  • Determine company’s earnings yield = EBIT / enterprise value.
  • Determine company’s return on capital = EBIT / (net fixed assets + working capital).
  • Rank all companies above chosen market capitalization by highest earnings yield and highest return on capital (ranked as percentages).
  • Invest in 20–30 highest ranked companies, accumulating 2–3 positions per month over a 12-month period.
  • Rebalance portfolio once per year, selling losers one week before the year-mark and winners one week after the year mark.
  • Continue over a long-term (5–10+ year) period.

Screening stocks as per the Magic Formula Screen on Excel using MarketXLS 

MarketXLS provides a variety of functions which makes the screening process easier. Investors can get the data of any company by just a click. It is possible to screen markets with 10 years of key fundamental data. It also enables to build complex filter criteria with an unlimited number of filter conditions and comparisons.  

Excel Screener

Building The Stock Portfolio Step by Step

The magic formula is intended for anyone who wants to adopt the value investing strategy and is looking for a simple way to beat the market over the long run. The Magic Formula ranks stocks in order and groups them together. Let’s look at it step by step:

STEP 1: 

Add the tickers of the Company in the cell which you have selected for screening (Make sure to choose only the ones with a minimum market capitalization of $50 million). With the help of the name function in MarketXLS, get the names of the companies as well.

Stock Ticker and Name

 

 

 

STEP 2:

Using the functions in MarketXLS check for the market capitalization and sectors of the company to ensure they match the screening criteria.

Market Capitalization

STEP 3:

Similarly establish the other parameters namely, EBIT/EV, EBIT/WC+FC and its performance to rank them. Using the Sort and Filter rank these companies.

Rank Companies

STEP 4:

Now that you have ranked the companies, select 5-7 of them and start investing only 20 to 33 percent of the money you intend to invest during the first year. Keep repeating this 4 every two to three months until you have invested all of the money you have chosen to allocate to your magic formula portfolio. 

STEP 5:

Sell each stock after holding it for one year. For taxable accounts, sell winners after holding them a little more than one year and sell losers after holding them a few days less than one year. Use the proceeds from any sale and any additional investment money to replace the sold companies with an equal number of new magic formula selections (Step 4).

STEP 6:

Keep continuing this process for quite a few years. Remember, you must be committed to continuing this process for a minimum of three to five years, regardless of results otherwise, you will most likely quit before the magic formula has a chance to work, suggests Joel Greenblatt in his book.

The Bottom Line

Stock-picking can be subjective and depend upon the individual’s understanding of the company/sector and his risk profile. Investors should carefully assess the other factors such as profitability and debt to equity ratio, whether the company has sufficient moat and management strength too before investing in stocks. However the Magic Formula provides a good head start to new investors who want to begin their investment journey.

Disclaimer

None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person.
The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.
The article is written to help users collect the required information from various sources deemed to be an authority in their content. The trademarks, if any, are the property of their owners, and no representations are made. All trademarks referenced are the property of their respective owners. Other trademarks and trade names may be used in this document to refer to either the entity claiming the marks and names or their products. MarketXLS disclaims any proprietary interest in trademarks and trade names other than its own or affiliation with the trademark owner.

References: 

The Little Book that still beats the Market – by Joel Greenblatt.

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
I agree to the MarketXLS Terms and Conditions
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.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and starter sheets.
Get 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

Williams-Sonoma Inc. (DE) logo

Williams-Sonoma Inc. (DE)

139.46
 
USD
 
4.52
 
(3.35%)
89
Rank
Optionable: Yes
Market Cap: 8,983 M
Industry: Specialty Retail
52 week range    
101.58   
   176.89
Rush Enterprises Inc. Class A Common Stock logo

Rush Enterprises Inc. Class A Common Stock

55.74
 
USD
 
1.93
 
(3.59%)
88
Rank
Optionable: Yes
Market Cap: 2,929 M
Industry: Auto & Truck Dealerships
52 week range    
42.72   
   56.01
Haverty Furniture Companies Inc. logo

Haverty Furniture Companies Inc.

35.01
 
USD
 
0.09
 
(0.26%)
88
Rank
Optionable: Yes
Market Cap: 564 M
Industry: Home Improvement Stores
52 week range    
22.77   
   35.40
PulteGroup Inc. logo

PulteGroup Inc.

58.44
 
USD
 
1.55
 
(2.72%)
87
Rank
Optionable: Yes
Market Cap: 12,961 M
Industry: Residential Construction
52 week range    
35.03   
   58.65
Toll Brothers Inc. logo

Toll Brothers Inc.

60.26
 
USD
 
0.77
 
(1.29%)
86
Rank
Optionable: Yes
Market Cap: 6,578 M
Industry: Residential Construction
52 week range    
39.53   
   63.19
Weis Markets Inc. logo

Weis Markets Inc.

86.71
 
USD
 
0.37
 
(0.43%)
85
Rank
Optionable: Yes
Market Cap: 2,322 M
Industry: Grocery Stores
52 week range    
58.40   
   95.57
Victory Capital Holdings Inc. logo

Victory Capital Holdings Inc.

30.34
 
USD
 
0.70
 
(2.36%)
85
Rank
Optionable: Yes
Market Cap: 2,030 M
Industry: Asset Management
52 week range    
22.22   
   36.90
Regional Management Corp. logo

Regional Management Corp.

35.98
 
USD
 
1.49
 
(4.32%)
85
Rank
Optionable: Yes
Market Cap: 331 M
Industry: Credit Services
52 week range    
26.52   
   56.95
Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

130.72
 
USD
 
2.90
 
(2.27%)
85
Rank
Optionable: Yes
Market Cap: 9,114 M
Industry: Auto & Truck Dealerships
52 week range    
88.58   
   131.67
El Pollo Loco Holdings Inc. logo

El Pollo Loco Holdings Inc.

12.22
 
USD
 
-0.05
 
(-0.41%)
85
Rank
Optionable: Yes
Market Cap: 455 M
Industry: Restaurants
52 week range    
8.15   
   13.97

More Features