# Magic Formula Ranking Using Portfolio123

In my last post, I discussed the features of Portfolio123.  In this post, I’m going to detail how I used the Ranking feature of Portfolio123 to recreate the two major factors in the Magic Formula.  These two factors are return on capital and earnings yield.  I discussed there factors back in January in my post titled, “Exploring Greenblatt’s Magic Formula“.

Let me start with return on capital first.  The Joel Greenblatt’s Magic Formula makes several adjust when determining return on capital (ROC).  The basic formula is:

ROC = EBIT / (Net Working Capital + Net Fixed assets)

where EBIT is earnings before interest and taxes.  Going through the Factors and Functions reference for Portfolio123 it is pretty easy to spot that EBITTTM is the variable we should use for EBIT.  By the way, The Little Book that Beats the Market indicates that trailing twelve month numbers from the income statement are used or the latest quarterly numbers for balance sheet figures.

Net Working Capital is a bit more complicated and less direct.  From the Magic Formula Investing website, net working capital is “generally, current assets less excess cash less non-interest bearing payables.”  Current assets are represented by the variable CurAstQ in Portfolio123.

Excess cash is a bit of a mystery, since Joel Greenblatt has not answered any questions regarding how this is calculated.  My guess is that excess cash is determined by taking cash and subtracting it from some average ratio of cash over some income sheet metric by industry.  After some google searching, it appears that cash to sales ratio seems to be the most common measurement of “needed” cash.  The ratio seems to vary from 3 percent to 10 percent of sales depending on what industry is involved.  An average of about 5 percent of sales seems to be commonly used over all industries.  My guess is that Greenblatt uses a better measure of excess cash, but for now total cash minus 5 percent of sales appears to be the best metric to use to estimate excess cash.  Only cash per share (CashPSQ) is available in Portfolio123 so I multiple CashPSQ time the number of shares (ShsOutMR) to get total cash (including short term investments that are included in CashPSQ).  I subtract this from sales times 5 percent (SalesTTM * 0.05).  Noting that sometimes a company doesn’t have excess cash, I make sure that the lowest amount that excess cash can be is zero by using a maximize function.  The final equation for excess cash is:

Excess Cash = Max(CashPSQ * ShsOutMR – SalesTTM * 0.05, 0)

The last part of net working capital is non-interest bearing payables.  Portfolio123 does not have a variable for non-interest bearing payables (i.e., accounts payable), but this can be estimated by deduction.  If you take current liabilities and subtract out interest bearing payables (i.e., short-term debt) then the result is or is very close to non-interest bearing payables.  Since Portfolio123 does not have a variable for short-term debt, I had to take total debt (DbtTotQ) and subtract long term debt (DbtLTQ) to get at short term debt.  The final equation for non-interest bearing payables is:

Non-interest bearing payables = CurLiabQ-(DbtTotQ-DbtLTQ)

The final equation for net working capital is:

Net Working Capital = Max(CurAstQ-Max(CashPSQ * ShsOutMR – SalesTTM * 0.05, 0)-CurLiabQ-(DbtTotQ-DbtLTQ),0)

Note that I added another maximization function to this equation and a comma zero at the end.  This is to ensure that net working capital is never below zero, since Joel Greenblatt has hinted that uses zero when net working capital is negative.

The final factor is Net Fixed Assets. Net fixed assets is basically property, plant and equipment after depreciation.  Portfolio123 does not have a property, plant and equipment variable, so I had to estimate one.  I took total assets (AstTotQ) and subtracted current assets (CurAstQ) to get at long term assets where property, plant and equipment resides.  I then remove intangibles and goodwill, as mentioned in the book, by subtracting tangible book value (TanBV\$Q) from book value (BV\$Q).  The final equation is:

Net Fixed Assets = AstTotQ-CurAstQ-(BV\$Q-TanBV\$Q)

Taking this all together, I built the following equation to estimate ROC in a very similar manner as Joel Greenblatt.

ROC = EBITTTM/(Max(CurAstQ-Max(CashPSQ * ShsOutMR – SalesTTM * 0.05, 0)-CurLiabQ-(DbtTotQ-DbtLTQ),0)+(AstTotQ-CurAstQ-(BV\$Q-TanBV\$Q)))

The next factor used in the Magic Formula ranking is Earnings Yield (EY).The equation for earnings yield is:

EY = EBIT/Enterprise Value

I’ve already discussed EBIT, so I will focus on enterprise value.  According to the Magic Formula Investing website, enterprise value takes market capitalization (MktCap), debt, and “subtracts excess cash and includes any preferred stock”.  The new factor here is the preferred stock.  I used total equity (EqTotQ) minus book value (BV\$Q) to determine preferred stock.  My final equation for earnings yield is:

EY = EBITTTM/(MktCap + (EqTotQ – BV\$Q) + DbtTotQ – Max(CashPSQ * ShsOutMR – SalesTTM * 0.05, 0))

So now you have the two major ranking factors.  I equally weigh the two ranking factors as described in Greenblatt’s book.  In addition to the ranking factors, the Magic Formula also screens out some stocks.  I’ll go over the screen in my next post.  For now, I would love feedback on the ranking system I’ve created that tries to closely match the Magic Formula.  Can it be improved under the Portfolio123 system?  Am I missing anything or have gotten something wrong?  Does anyone know Greenblatt’s secret excess cash formula?

### 11 thoughts on “Magic Formula Ranking Using Portfolio123”

• April 6, 2006 at 8:39 pm

what is your % correlation with the MFI website?

• April 6, 2006 at 9:34 pm

I’ll be posting my correlation with the MFI website in my next post. This post was getting rather long, so I decided to split up the discussion over several posts.

• April 7, 2006 at 10:49 am

Very nice piece of work. I was going to try to do something similar in using the AAII Stock Investor Pro Software. From what you have done, I am sure you realize you will need to take the two rank ordered lists of stocks, one list for each factor, and mesh them together using (likely) a macro from Excel. This is easy to do. Once you get the correlation with the Magic Formula, it opens the door to expand the list to up to say 300 stocks or so. There could be value in doing this, at which point one could do some subfiltering to see if any improvements are possible.

• April 7, 2006 at 1:12 pm

Ric –
The Portfolio123 website automatically meshes together the two rank ordered lists. I set the two ranks to be equal weight and the website does the rest. There is no need for Excel. If I was using a standard screening tool on a free site, I would have to do it in Excel but this is not needed in Portfolio123.

• April 11, 2006 at 2:42 am

I think you are making this too complex. Here’s how I calculate ROC and all that other stuff.

Capital Base = (Assets -(Cash+Acc Payable)) :: This comes from the accounting identity that Assets == Liabilities + Owners Equity. Cash on hand isn’t an asset involved in operations, and unpaid bills arent really capital that has been contributed to the company.

NOPAT = Income from Operations – Tax paid = Net Operating Profits after tax:: This represents all the Profits that can be distributed to providers of capital (I.e shareholders and lenders).

NOPAT/Capital Base == Return on Capital == Income available for capital providers/Assets used in operations

Enterprise Value == Book Value of Debt + Mkt Value of Equity – Cash :: This is what it would cost you own the company with other claims on it. NOPAT/EV == Economic P/E

Approximate Cost of Capital == Interest Expense + Book value of Equity*(5 Year
AA Bond Yield*SQRT(3))

NOPAT/(Approximate Cost of Capital) should be > 1.

• July 25, 2008 at 6:45 pm