Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS

Magic Formula Screen- Investment Strategy (Using Excel)

Written by  Shubham Shah on 
Tue May 04 2021
 about MarketXLSFundamental analysisGuru functionsStock investments
Magic Formula Screen- Investment Strategy (Using Excel) - MarketXLS

Meet The Ultimate Excel Solution for Investors

  • Live Streaming Prices Prices in your Excel
  • All historical (intraday) data in your Excel
  • Real time option greeks and analytics in your Excel
  • Leading data in Excel service for Investment Managers, RIAs, Asset Managers, Financial Analysts, and Individual Investors.
  • Easy to use with formulas and pre-made sheets
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.

Ad slot not ready
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
Welcome! I'm Ankur, the founder and CEO of MarketXLS. With more than ten years of experience, I have assisted over 2,500 customers in developing personalized investment research strategies and monitoring systems using Excel.

I invite you to book a demo with me or my team to save time, enhance your investment research, and streamline your workflows.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and templates.

MarketXLS is a complete Excel stock solution

Kevin Hsu

StockKevin.com

I have used lots of stock and option information services. This is the only one which gives me what I need inside Excel

Lloyd Lenase

Option Day Trader

MarketXLS is a data junkie’s dream. It gives me the flexibility to mine for hidden treasures.

Dave

Swing trader since 2011

I like to access historical closing prices on a particular date. That makes tracking performance easy.

Patrick Cusatis, Ph.D., CFA

Associate Professor of Finance - Penn State University

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

Get started today

🎉 Exciting news! 🎉

You are invited to join our Discord Channel.

Interact, learn, and grow with experts in the markets!

Join our Discord