Unfortunately, the resulting time series is not smooth: it jumps abruptly from one fiscal year’s EPS estimate to the next as soon as a quarter is completed.
Would it be possible to implement a function that measures quarter completion on a daily (or at least weekly) basis instead of only updating when the quarter is fully complete? This would produce a much smoother time series as the weighting gradually shifts from the current FY estimate to the next FY estimate over the course of the quarter.
Thank you very much for considering this improvement!
Ah. Have you tried modifying your formula by using WeeksToQ and/or WeeksIntoQ? I think you could probably figure out something using those two factors to smooth the curve. Just a suggestion . . .
It is working fine. However, it raises a new question: Why ConsEstMedian(#EPSNTM, 0) is different from CurFYEPSMedian * (1 - 0.25 * Mod(QtrComplete, 4)) + NextFYEPSMedian * 0.25 * Mod(QtrComplete, 4)
Let's say there are only two analysts for a stock. Here are their EPS estimates for the next four quarters. Analyst 1: 0.02, 0.05, 0.09, 0.13. Analyst 2: 0.02, 0.04, 0.02, -0.02. The median NTM is 0.175. Now let's say the fiscal year is half over, and the EPS actuals for the previous two quarters are 0.10 and 0.05. So the current fiscal year EPS median/average is 0.215. And let's say the analyst's estimates for the next two quarters after the NTM quarters are: analyst 1: 0.15, 0.20; analyst 2: 0.00, -0.05. So the next fiscal year EPS median/average is 0.26. You can see how enormous the discrepancy can be.
“Let's say there are only two analysts for a stock. Here are their EPS estimates for the next four quarters. Analyst 1: 0.02, 0.05, 0.09, 0.13. Analyst 2: 0.02, 0.04, 0.02, -0.02. The median NTM is 0.175” => Ok I understand that
”Now let's say the fiscal year is half over, and the EPS actuals for the previous two quarters are 0.10 and 0.05. So the current fiscal year EPS median/average is 0.215”
=> I don’t get that one, maybe a typo? the current fiscal year EPS average is 0.26 → = 0.1+0.05 + 0.50*(0.09+.13)+0.50*(0.02-0.02)
”And let's say the analyst's estimates for the next two quarters after the NTM quarters are: analyst 1: 0.15, 0.20; analyst 2: 0.00, -0.05. So the next fiscal year EPS median/average is 0.26.”
=> Yes ok
I don’t understand your point. I guess my question is how ConsEstMean(#EPSNTM, 0) is computed. I just don’t understand how it can be different from the formula I initially shared CurrFYEPSMean * (1 - 0.25 * Mod(QtrComplete, 4)) + NextFYEPSMean * 0.25 * Mod(QtrComplete, 4) unless we are dealing with different datasets (?).
I am a particularly confuse by the jump on DIS in November 2021 (see my previous message). Thank you.
Ok I found the issue. For Disney some earnings are before the date expected by WeeksIntoQ.
I created the below chart with 4 formula.
1/ Purple color: ConsEstMean(#EPSNTM, 0)
2/ Blue: CurFYEPSMean with Quarterly jump
the formula is CurFYEPSMean * (1 - 0.25 * Mod(FHist("QtrComplete", -1),4)) + NextFYEPSMean * 0.25 * Mod(FHist("QtrComplete", -1),4)
There is no big jump, but not sure why it doesnt have the exact same valued as ConsEstMean(#EPSNTM, 0)
3/ Green: EPS NTM with weekly jump
the formula is CurFYEPSMean * (1 - 0.25 * ((Mod(QtrComplete,4))+ (WeeksIntoQ / (WeeksIntoQ + WeeksToQ)))) + NextFYEPSMean * 0.25 *((Mod(QtrComplete,4))+ (WeeksIntoQ / (WeeksIntoQ + WeeksToQ)))
Working fine except on november earnings
4/ Red: EPS NTM with weekly jump but shift -1
The formula is: CurFYEPSMean * (1 - 0.25 * ((Mod(FHist("QtrComplete", -1),4))+ (FHist("WeeksIntoQ",-1) / (FHist("WeeksIntoQ",-1) + FHist("WeeksToQ",-1))))) + NextFYEPSMean * 0.25 *((Mod(FHist("QtrComplete", -1),4))+ (FHist("WeeksIntoQ",-1) / (FHist("WeeksIntoQ",-1) + FHist("WeeksToQ",-1))))
"Why ConsEstMedian(#EPSNTM, 0) is different from CurFYEPSMedian"
Another factor is that the NTM is based on quarterly estimates and CurrFYEPSMean is based on annual estimates. There can be analysts that only give a quarterly estimate or only an annual estimate. So if analyst A only gave an annual estimate and his estimate is far from the mean, then CurrFYEPSMean would be higher then the NTM since analyst A did not give any quarterly estimates.
The real issue is that NTM estimates are the sum of the next four quarterly estimates, period. CurFYEPSMean and NextFYMean are the sums of the estimates for each fiscal year.
Let's decompose this. Let's call E1, E2, E3, and E4 the estimates for each quarter of the current fiscal year and E5, E6, E7, and E8 the estimates for each quarter of the next fiscal year. At any one point in time, NTM estimates consist of the sum of exactly four of those estimates and disregard the other four. If the other four are wildly different from the four that are being considered, then using CurFY and NextFY are going to get you something entirely different from NTM. For example, if you're halfway through the fiscal year, the NTM estimate will be the sum of E3, E4, E5, and E6. E1, E2, E7, and E8 are irrelevant. But if you're using half CurFY and half NextFY, you're going to use the sum of all eight estimates divided by two. If E1, E2, E7, and E8 are wildly lower or higher than the middle four, you're going to get entirely different numbers. There's simply no way to use CurFY and NextFY to equal NTM.
Most analysts provide estimates for every quarter and then sum those for the fiscal year estimates. There aren't many who provide estimates for only the fiscal year without breaking it down by quarter. That's why NTM is rarely going to be exactly x times CurFY + y times NextFY (x+y=1).
If there’s a problem with that formula, please let me know! Using the next four quarters seemed okay but I noticed on Yahoo finance there tend to be more annual estimates than future quarterly estimates (sometimes there were only annual estimates) so I rolled with this.
Back to my original request, if I want to have a smooth weekly EPS NTM, I would need to have access to current quarter EPS (CurQEPSMean), next quarter (NextQEPSMean), but also the 3 next quarters (not available as of today). Then I could build a function to roll smoothly from current quarter to the 5th one.
You do have access to those. Next quarter is ConsEstMean(#EPSQ,1). The quarter after that is ConsEstMean(#EPSQ,2). The quarter after that is ConsEstMean(#EPSQ,3). The quarter after that is ConsEstMean(#EPSNTM) minus the three quarters I just listed.
No, I just meant that most analysts make the current year the sum of the four estimates and the next year the sum of the next four estimates, which enables one to get NTM. There are some, as you point out, that only provide annual estimates, but 552 out of 4,709 isn't that many. Only 159 have current year but no current quarter. The reason for the larger number of NTM N/As is that often a single quarter's estimate is missing from the data for one reason or another. In addition, 276 companies have current year estimates but report semiannually.
I would need ConsEstMean(#EPSQ,4) in order to smoothly average into it, while averaging out of ConsEstMean(#EPSQ,0). But from what I understand ConsEstMean(#EPSQ,4) might not even be available. I’m used to bloomberg and their nice smooth EPS NTM. It would have been nice to have it here too.
