BasicsFundamental analysisGuru functionsStock investments

Magic Formula Screen- Investment Strategy (Using Excel)

Written by Vanshika Kothari
Tue May 04 2021
Magic Formula Screen
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Magic Formula Screen
 

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
Download Option Templates

Top 100 Gainers Today

Top 100 losers Today

Real gdpReal personal consumption expReal private investmentReal govt expenditureReal net exportsReal exportsReal importsFederal receiptsFederal outlaysFederal surplus or deficitFederal debtReal private investment nonresidentialReal private investment residentialReal potential gdpReal personal incomeReal personal consumption exp monthlyRpce durable goodsRpce nondurable goodsRpce servicesPersonal savings rateMonetary baseCurrency in circulationBank reservesMoney supply m1Money supply m2Sp500DjiaWilshire indexVixFinancial stress indexCorporate bond index aaCorporate bond index bbbFederal funds rateTreasury rate 3mTreasury rate 1yTreasury rate 5yTreasury rate 10yTips 5yTips 10yBond yield aaaBond yield baaMortgage rate 15yMortgage rate 30yUs dollar weighted averageUsdollar to euroUsdollar to poundYuan to usdollarCanadiandollar to usdollarYen to usdollarCpiCpi wo food energyCpi foodCpi energyChain price indexChain price index wo food energyGdp price deflatorPpi final demandPpi finished goodsPpi materialPpi crude goodsPpi final demand wo food energyPpi finished goods wo food energyHouse price indexHouse price index 20cityCrude oil priceGasoline priceNatural gas priceIndustrial productionCapacity utilisationInventoriesSales retail foodVehicle sales light weightManufacture orders durablesManufacture orders capital goodsLoansConsumer credit outstandingCorporate profitsHousing startsBuilding permitsResidential constructionEmployees nonfarmEmployees privateEmployees goods producingEmployees service providingEmployees governmentUnemployment rateInitial cliamsAverage weeks unemployedJob openingsHiresSeparationsQuitsLayoffs dischargesHours of productionHourly earningsReal outputPopulationLabor forceLabor force participation rate
Search for a stock

Top MarketXLS Rank stocks

Rush Enterprises Inc. Class B Common Stock logo

Rush Enterprises Inc. Class B Common Stock

51.75
 
USD
 
0.34
 
(0.66%)
86
Rank
Optionable: No
Market Cap: 2,846 M
Industry: Auto & Truck Dealerships
52 week range    
35.64   
   57.55
Amdocs Limited logo

Amdocs Limited

83.41
 
USD
 
0.37
 
(0.45%)
82
Rank
Optionable: Yes
Market Cap: 10,201 M
Industry: Software - Infrastructure
52 week range    
67.36   
   87.76
Booz Allen Hamilton Holding Corporation logo

Booz Allen Hamilton Holding Corporation

91.91
 
USD
 
1.79
 
(1.99%)
82
Rank
Optionable: Yes
Market Cap: 11,928 M
Industry: Business Services
52 week range    
69.32   
   93.45
ManTech International Corporation logo

ManTech International Corporation

95.40
 
USD
 
-0.26
 
(-0.27%)
79
Rank
Optionable: Yes
Market Cap: 3,917 M
Industry: Software - Application
52 week range    
66.09   
   95.87
Agree Realty Corporation logo

Agree Realty Corporation

73.11
 
USD
 
0.41
 
(0.56%)
79
Rank
Optionable: Yes
Market Cap: 5,465 M
Industry: REIT - Retail
52 week range    
60.78   
   74.67
CatchMark Timber Trust Inc. Class A logo

CatchMark Timber Trust Inc. Class A

10.19
 
USD
 
-0.04
 
(-0.39%)
78
Rank
Optionable: Yes
Market Cap: 504 M
Industry: REIT - Diversified
52 week range    
7.05   
   12.39
Cowen Inc. logo

Cowen Inc.

29.10
 
USD
 
-1.83
 
(-5.92%)
78
Rank
Optionable: Yes
Market Cap: 859 M
Industry: Capital Markets
52 week range    
21.35   
   41.05
National Research Corporation logo

National Research Corporation

37.25
 
USD
 
-1.36
 
(-3.52%)
77
Rank
Optionable: No
Market Cap: 973 M
Industry: Diagnostics & Research
52 week range    
30.74   
   54.69
NBT Bancorp Inc. logo

NBT Bancorp Inc.

37.94
 
USD
 
-0.36
 
(-0.94%)
76
Rank
Optionable: Yes
Market Cap: 1,640 M
Industry: Banks - Regional - US
52 week range    
31.68   
   41.70
General Mills Inc. logo

General Mills Inc.

76.22
 
USD
 
1.07
 
(1.42%)
76
Rank
Optionable: Yes
Market Cap: 44,876 M
Industry: Packaged Foods
52 week range    
55.38   
   76.09

More Features

Stand with Ukraine

As the situation in Ukraine escalates, many of us in MarketXLS are left with emotions too overwhelming to name. If you’d like to show your support, but aren’t sure how to, we want to help make it easier for you to act.

For any amount donated, we’ll extend your MarketXLS subscription for double of the donated amount. Please send proof of your payment to support@marketxls.com to avail the extention

From all of us at MarketXLS, thank you!