Hello ftstrades, I believe the first to last point growth rates work as follows:
100$ at @ 10% for 2 yrs = = 100 * 1.10 = = 110 * 1.10 = = 121 In Ami 100*1.10^2 = = 121 To find the growth factor 100*gf^2 = = 121 so in Ami gf = = 121/100^(1/2) = = 1.1 Mathematicians are now invited to pelt me with rotten fruit if required. This is *point to point* calculation. You might be interested in the work of Prof Dr Price (it's a conincidence that he is an Aussie, honest!) http://www.conscious-investor.com/index.html He uses a best fit growth curve for EPS, which reduces error due to wild outliers at the first or last point in a series. I don't think Ami has a built in function for this. Maybe Fred's polynomial code will do that job, I'm not sure not having read it as yet (where are you when we need you Fred?). By the way, Prof Price all provides an audited statement of 10 year returns cf the S&P, 2000-2005. BrianB2. --- In [email protected], "ftstrades" <[EMAIL PROTECTED]> wrote: > > These are the parameters for Rule # 1 > > Rule #1 Resources > > EXCEL Formulas for Making Calculations on Your Own > If you want to obtain accurate solutions to all the Rule #1 > calculations, and don't want to resort to my online calculators, you > can simply open up an Excel document and perform all the calculations > there using straightforward formulas. > > If you're familiar with Excel, this'll be easy for you to understand > right off the bat. And if you've never used a program like Excel, > don't be intimidated. You can learn how to use Excel much in the same > way as you've learned any word processor. For purposes of performing > Rule #1 calculations, Excel is straightforward and simple. You'll > soon see just how powerful such a program can be. In fact, the > magical "formulas" are already built into the program. We're dealing > with basic rate calculations and mathematical solutions people use > every day, and for which this program is designed. You don't even > have to know exactly what's going on behind the scenes of these > formulas (but you can certainly review chapters in the book that > explains these details). > > > Calculating Growth Rates: > Whether you're calculating an Equity, EPS, Sales or Cash growth rate, > the process (and formula) is the same. > > 1. First, find the data on these four categories for each year > back as far as you can—10 years is best. You'll do each of these four > critical numbers separately. I usually start with Equity or Book > Value Per Share, so that's what I'll show you here. Repeat for all > the other growth rates. > > 2. In Excel, type "=RATE(" and you'll see this formula appear > (the bolded abbreviation, "nper," means put in number for nper): > > > > 3. nper = the number of years. Let's do a 10-year average growth > rate. So in this case, add up the number of years of data you have, > 10, subtract 1 and put in "9" for nper and then type a comma. Notice > that when you do that, the next item goes to bold. > > > > > 4. pmt = the payments each year. Since we're not doing payments, > don't put anything. Just type a comma. > > > > 5. pv = the number you want to start with. Let's say the oldest > Equity number you have from 10 years ago is $463,000,000. Input minus > 463 ("-463"). The minus sign is a convention to make the formula work > right. It's saying to the formula, "I paid out this amount." > Afterwards, input a comma. > > > > > > 6. [fv] = the number you end with. Let's say the most recent > Equity number you have is from last year, and it's $1,683,000,000. > Input "1683." This says to the formula that this is the amount you're > taking out. Ignore [type] and [guess]. Input a close > parenthesis. > > > > 7. Hit "Enter." Excel will immediately calculate the rate of > Equity growth for the last nine years. In this case it's 15% and > looks like this: > > > > As mentioned, all growth rate calculations work exactly the same way. > If you wanted the growth rate for Equity for the last five years, > just copy the completed formula into another cell and change the 9 to > a 5, and change the -463 to minus whatever the Equity number was six > years ago. Hit Enter and you'll get the 5-year Equity growth rate. > Copy, change it again to "3," insert the new starting point four > years back, and hit Enter. Repeat for 1-year and you're done. You > have just calculated the 9-year, 5-year, 3-year and 1-year Equity > growth rates. Now you can look to see if the growth is consistently > up or down, or all over the map. Now repeat the process for EPS, > Sales and Free Cash. > > > Calculating Future Earnings per Share > 1. Determine the growth rate you wish to use to make a projection > of future Earnings per Share. > > 2. In Excel, type "=FV(" and you'll see this formula appear: > > > > 3. rate = the growth rate you determined written as a percentage > and type a comma: > > > > > 4. nper: input "10," which is the number of years into the future > for this estimate, comma: > > > 5. pmt: skip it and put a comma: > > > > 6. pv = the number you want to start with. Input it as a negative > number. Let's say that in our example, the current EPS is$1.43 per > share, which is entered as -1.43. Then close the parenthesis with > a ")." > > > > 7. Hit Enter. Excel will immediately calculate the EPS 10 years > into the future. In this case 10 years from now we're estimating the > earnings in this business will be at least $5.79 per share. It looks > like this: > > > > All future value (FV) calculations work the same way. Be very careful > about inserting commas. If you fail to input a comma in the right > place (or, likewise, fail to use a minus sign in front of certain > values) you won't get the right result. > > > Calculating Future Stock Price > 1. The future stock price is the estimated (future) EPS multiplied > by a PE of your choice. See Chapter 9 for a complete explanation on > how to arrive at a PE. > > 2. In Excel, type "=" and click on the future EPS number, in this > case $5.79. > > > 3. Type *30 (or whatever the PE is that you've chosen). > > > 4. Hit Enter. Excel will immediately calculate the stock price 10 > years into the future. In this case, 10 years from now we're > estimating the stock price of this business will be about per share. > It looks like this: > > > > > Calculating Sticker Prices > 1. Determine your minimum acceptable rate of return. For Rule #1 > investors it's 15 percent per year. > > 2. In Excel, type "=PV(" and you'll see this formula appear: > > > > 3. rate: the minimum acceptable rate of return: 15%. Input 15% > with a comma: > > 4. nper: the number of years from the future back to today. In > this case, 10. Input "10" and a comma: > > > > 5. pmt: skip it. Input a comma: > > > > 6. [fv]: here [fv] means the future stock price. In this example > the future stock price is $173.55. Type a minus sign first and either > input 173.55 or click on the cell which contains that value, and then > close the parenthesis: > > > > 7. Hit Enter. Excel immediately calculates the Sticker Price. In > this case the Sticker Price is $42.90. It looks like > this: > > > > > Calculating the Margin of Safety Price > > > 1. The Margin of Safety or MOS Price is half of the Sticker Price. > 2. In Excel, type "=" and click on the Sticker Price: > > > > 3. Type *50%: > > > > > 4. Hit Enter. Excel will immediately calculate the MOS Price. In > this case the MOS Price is $21.45. It looks like > this: > > > How hard was that?! Once you get used to working in Excel, these > calculations soon become elementary. >
