EXCEL Formulas for Making Calculations on Your Own (Rule1)

Rule #1 Resources

EXCEL Formulas for Making Calculations on Your Own

     
 
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. 

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.

 

 
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.

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.

    
                       
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 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:    

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. 

Calculating Future Earnings per Share