Seeking average free cash flow...

Research
1

Hi everyone,

I am new to portfolio123 and I have a simple request to get me going. Can you provide me with the function to provide me with the average free cash flow over the last 7 years? Free cash flow I am looking for would go something like this…

(Price) / (((Average net income over the last 7 years) + (Average depreciation and amortization over the last 7 years) - (Average capex over the last 7 years)) / Float)

Thanks for the help.

2

Instead of (((Average net income over the last 7 years) + (Average depreciation and amortization over the last 7 years) - (Average capex over the last 7 years)) , I would use FCF(0,ANN)+FCF(1,ANN)+etc…more accurate.

3

Welcome!

It looks like you will need to use line items to do the job. One line item for net income is Income Available to Common (IAC); for Depreciation and amortization: DepAmort and for CapEx: CapEx. (To find a line item enter search terms in the relevant box or look under FUNDAMENTALS, STATEMENTS AND FILINGS…)

Let’s go step by step.

The formula for FCF over the trailing twelve month period is:
IAC(0, TTM) + DepAmort(0, TTM) - CapEx(0, TTM)
FYI: TTM line items are calculated by adding up the relevant quarterly data. Quarterly data is only available for the most recent five years. To go back seven years you need to use annual data.

FCF from the annual statement seven years ago is:
IAC(6, ANN) + DepAmort(6, ANN) - CapEx(6, ANN)
(Since the most recent annual report is ‘0’, ‘6’ means the seventh year).

To use the average of all seven years you can write out the entire formula in the Avg() function or you can use the LoopSum function like this:
LoopSum(“IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN)”, 7) / 7
But because the LoopSum method converts N/As to 0’s this will mean that companies with less than seven years of history will report incorrect averages using this method.

Here is a workaround:
LoopSum(“IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN)”, 7) / LoopSum(“Eval(IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN) = NA, 0, 1)”, 7)
(Eval() is like EXCEL’s IF() function)

To get Price to 7 year average FCF you can use:
Price / Max(LoopSum(“IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN)”, 7) / LoopSum(“Eval(IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN) = NA, 0, 1)”, 7), 0)
The max function above will convert negative FCF to 0 to avoid not meaningful ratios.

I’m not sure what you mean by “float” though. Is that the number of common shares outstanding?

Chaim

4

David’s version of FCF is much simpler to use because it’s built in but it uses Operating Cash Flow - CapEx - Dividends.

5

Thanks for the replies guys. Using a formula similar to the above, why do you think I am still returning 3000+ stocks? As I change the 10 to 1, I still have the same number of stocks, so something if off.

close(1)/(LoopSum(“IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN)”, 7) / (7*FLOAT) < 10

And by FLOAT I am trying to get outstanding shares.

6

One other thing, when I was to see the "screen factors’ results, I only can see last, market cap, and float. Shouldn’t it be showing me all of the variables I put in the formula?

7

By default you won’t see any variable that is the result of a function such as FCF(0, ANN). You would see FCFANN (Since that’s not a function but a variable). To see the result of a function or formula in the screener use ShowVar. Ex:
ShowVar(@Year6FCF, FCF(6, ANN))

8

Thanks Chipper, any comments on my above post?

Thanks for the replies guys. Using a formula similar to the above, why do you think I am still returning 3000+ stocks? As I change the 10 to 1, I still have the same number of stocks, so something if off.

close(1)/(LoopSum(“IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN)”, 7) / (7*FLOAT) < 10

And by FLOAT I am trying to get outstanding shares.

9
  1. All negative FCF companies are passing this rule because they are all < 10. You need to either set negative FCF to 0 or flip the formula around to get the companies with a FCF yield above 10% (see examples below).
  2. The formula is missing a parenthesis.
  3. Use Close(0) for the most recent closing price.
  4. For shares outstanding use SharesQ instead of FLOAT. Float in the P123 vernacular means the number of shares not owned by insiders.
  5. Since NAs get converted to 0, companies with a short history will have misleadingly low FCF numbers. Is that what you want?

Ex (treat NAs as zero):
close(0) / Max(0, (LoopSum(“IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN)”, 7) / 7 / SharesQ)) < 10 // Price / 7 Year Average FCF PS < 10. Treat NAs as zeros.

Ex (Skip NAs. The following formula returns 293 companies):
close(0) / Max(0, (LoopSum(“IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN)”, 7) / LoopSum(“Eval(IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN) = NA, 0, 1)”, 7) / SharesQ)) < 10 // Price / Average FCF PS < 10

Ex 3; (flip the numerator with the denominator to get the FCF yield):
(LoopSum(“IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN)”, 7) / LoopSum(“Eval(IAC(CTR, ANN) + DepAmort(CTR, ANN) - CapEx(CTR, ANN) = NA, 0, 1)”, 7) / SharesQ) / Close(0) > 0.10 // Average FCF, PS / Price > 10%