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?