He comes up with a term called "payback time", which sounds like the name of an action flick, but it is really about how long it takes for a company to give you back your entire initial investment in earnings. Phil uses the current EPS and extrapolates it into the future and sums up all of the earnings. At some point in the future, the earnings per share will sum up to the share price. That is the payback time. For one to start accumulating a stock, the payback time should be 10 years or less.
So, I've updated the spreadsheet to include the calculation of payback time. If you've seen the previous version, you'll notice that there are 2 more tabs. The first is the "Stock Price" tab, where you simply enter in manually the current price of the stock. The second is the "Payback Time" tab that shows what the payback time is. Piece of cake!
If you have any questions, leave a comment or email me. You can find my contact info in the About Me page.
Update February 17, 2011: I've updated the sheet. Go to the Investing Resources to download the latest sheet.
Add The Catholic Investor on Google+!
Hi Felix, I am very impressed with your excel work. I was having trouble computing the payback time in my excel sheet until I stumbled along your website. I am not sure if you have every downloaded the Payback Time spread sheet off of Phil Town's website but that spread sheet also has a calculation that provides you with the payback time price (rather then years). I was wondering if you can produce this formula as I have been banging my head against the wall trying to figure it out. I need your help!!
ReplyDeleteHi Joe,
DeleteThanks for your compliments! Have you seen my latest spreadsheet (http://catholicinvestor.blogspot.ca/2012/01/rule-1-analysis-spreadsheet-minor.html) in Google Speadsheet format? It's a little better in terms of pulling data from the web.
For the formula you're trying to figure out, you want to find out what the stock price needs to go to in order for the payback time to be less than 10 years? Please confirm. Thanks.
Felix
Yes, that is correct, exceot I would provide any payback time period I wish(preferably between 5-10 years)and according to Phil Town's spread sheet:
ReplyDelete"using Using Earnings per Share (EPS) growing at the Earnings Growth Rate (EGR), the PayBack Time Price (PBT Price) is the amount one can pay for a particular business to potentially recoup their initial investment within 8 years using the earnings of the company."
Hope this helps. You can download the spreadsheet I am refering to here: http://www.paybacktime.com/mypaybacktime/calculators.aspx
Thanks,
Joe
This is the formula:
DeletePrice = eps*(1-(1+growth_rate)^(PB_Time-1+1))/(-growth_rate)
If you recall 12th grade math, this is derived from a geometric series! And we thought we'd never use 12th grade math in real life! Hope this helps!
EPS is EPS of year 1
growth_rate is percentage estimated growth (e.g. 15% per year)
PB_Time is # of years to pay back
Thanks Felix, its funny I often think about my high school math teachers preaching the same thing you said in your post!
ReplyDeleteI plugged in your formula, however, the answer I am getting seems to be a little different from Phil Town's. The numbers I am using is as follows:
EPS: 42.55
Growth Rate: 12%
Payback Time: 10 years
His spreadsheet produces a price of $836.30 and the formula you provided produces a price of $878.85. Any idea as to why there is a difference?
P.S. I removed the -1 in the (PB_Time-1+1) portion of the formula as the -1+1 cancel each other out, and that got me closer to the answer on Phil Town's spreadsheet.
Hi Joe,
DeleteI see where Phil and I diverged. We are actually using the same formula. Let's look at this in a more simplistic way. EPS starts at year 0 at $42.55 and increases as shown below.
Year EPS
0 42.55
1 47.66
2 53.37
3 59.78
4 66.95
5 74.99
6 83.99
7 94.06
8 105.35
9 117.99
10 132.15
My payback time uses the sum of the EPS from years 0 to 9, where as Phil Town uses the sum of the EPS from years 1 to 10. I arrive at the price of $746.70 and Phil arrives at $836.30 (Leave the PB_Time-1+1 in that formula). In retrospect, I think he is right. The current EPS is already what we have.
If we want to adopt Phil's calculation, the formula becomes:
Price =eps*(1-(1+growth_rate)^(pb_time+1))/(-growth_rate)-eps
Hi Felix,
ReplyDeleteWell you solved it, you definitely have some serious math skills!! I appreciate your help with this.
However, now that you solved this problem, I believe I may have found the same difference between your calculation to determine the number of years to payback compared to Phil Town's. On page 119 of his book he provides an example of the number of years of payback time with these numbers:
Market Cap (stock price): 17,680
TTM Earnings (EPS): 2,115
Estimated Growth Rate: 12%
Your calculation (per your spreadsheet) produces a payback time of 5.79 years while Phil Town produces a payback time of 5.5 years. I think this is the same problem where Phil uses the sum of years 1 forward and not year 0.
Please let me know your thoughts on this, and what adjustment you would make to the formula. Thanks agian for your help!
Joe, are you using the spreadsheets found here? I've updated the spreadsheet with the correction.
Deletehttp://catholicinvestor.blogspot.ca/2012/01/rule-1-analysis-spreadsheet-minor.html
I used the numbers we used above (e.g. $42.55, 12%, $836.3) and got payback time of 10 years exact. So, I think everything works. Even if they were slightly off, we're talking about 5% difference in an already pretty rough estimate! I wouldn't really worry that much.
I now used the updated spreadsheet and the numbers are closer, thanks!
DeleteYour spreadsheet is very impressive, I like how it pulls all the information for you from the website. I am working on a similar spreadsheet, however, I am trying to streamline it all onto one tab for ease of use. If you would like I can send you a finished product.
FYI - I noticed you take a year over year percentage difference for all the big 5 numbers and then you average those percentages over 1,5,10 years to obtain your growth rate. According to Phil Town and Warren Buffett you should be using a calculation that takes into account time factor. I use the rate formula on excel to obtain my growth rates
Joe, sure, send me a copy once you're done. The About Me page should have my email.
DeleteFor the big 5 numbers, yes, I show the YOY growth in each of the growth tables. On the summary table where I show 1, 5, 9 years, I do use the rate formula, where Current_Value = Initial_Value * (1 + growth rate) ^ no_of_years.
Hello Felix,
DeleteI'm curious to know if you can re-engineer this formula to account for the new Payback Time Calculator.
Formula: Price =eps*(1-(1+growth_rate)^(pb_time+1))/(-growth_rate)-eps
New Payback Time Calculator: https://www.ruleoneinvesting.com/payback-time-calculator/
In other words, I would like input the EPS, Growth Rate, and Stock Price to output years.
Thanks,
Interesing post
ReplyDelete