<FOOLISH FOUR PORTFOLIO>

More Spreadsheet Fun
 And a better rule of 72

by Ann Coleman (TMF [email protected])

Reston, VA (March 16, 1999) -- Today we continue to plumb the mysteries of spreadsheets, but first, alert reader David Ayers sent me a note observing that the rule of 72 can be improved. David wrote:

"The Rule of 72 you talk about is incorrect when dealing with annualized investment rate of returns. It should be The Rule of 76. Why? Because The Rule of 72 was originally created by bankers years ago to easily describe the compounding effects of bank accounts based on monthly compounding. This rule has been passed along for years and no one ever questions its accuracy."

Well, I have to say that I did test it before tossing it out to you all, and found it... well, roughly accurate. I tested the "rule of 76" as well and found that it really does work better, but only for higher growth rates, possibly because the difference between compounding annually vs. monthly is more significant at higher rates. So here's a revised rule of 72. For interest rates of 10% and under, divide the interest rate into 72 to estimate of the number years it takes to double an investment, but for growth rates over 10%, dividing into 76 gives a better approximation. Thanks, David!

Of course, the rule of 72/76 is only a quick and dirty way to estimate investment performance. Yesterday we started looking at the real McCoy--spreadsheets. We walked through the process of setting up a simple spreadsheet that would give you the percentage increases for individual stocks. Today we will add a few refinements to make your spreadsheet more accurate and useful and help you -- even if you decide to use a commercial program to track your investments, like our own PortTrak or one of the many investment management programs on the market.

First, what about dividends?

Since the Foolish Four invests only in high yielding stocks, dividends are an important part of our return. In our official portfolio spreadsheet, we account for them as a lump sum (space is precious!), but a better way would be to account for them with each individual stock.

If you remember, our formula for the percentage return was Current Value/$Invested minus 1. To include dividends, you create another (yes, another!) column. Call it "Dividends" until you think of something better. This time, though, it would be best to Insert a new column rather than sticking it at the end. Highlight column H and click on the Insert menu to add a column between Current Value and %Change. That's your Dividends column.

You can simply enter the amount of dividends you have received in here, or you can create a special area of your spreadsheet (off to the right would be best). Like so:


                  

Symbol   #Shares  Quarter 1  Quarter 2  Quarter 3  Quarter 4   Total

CAT         14       .56       .56
Under Total you put a formula =SUM(range of dividend-containing cells)* #shares. It will look something like this =SUM(M2:P2)*L2. Then, back in your main spreadsheet, you put =Q2 (where Q is the Totals column) in the Dividend column and copy it down for all 4 stocks. Now all you have to do is enter the quarterly dividend each quarter, and the spreadsheet will calculate the total received so far and transfer it to your main calculation. Of course, you could just get the number off your brokerage statement and enter it, but that's not as much fun and, actually, more prone to error.

However you do it, your new formula for %Change becomes (Current Value + Dividends)/$Invested. For your first stock (row 2), it would be =((G2+H2)/F2)-1. Just change the formula once and you can copy it to the cells below, and it will automatically change the row numbers to calculate the percentage change for each stock.

Now, the top row of our spreadsheet looks like this (Please expand your window to see it all).


 A             B            C              D             E               F             G           H         I     

Date Bought  No. Shares  Stock Symbol  Purchase price  Current Price  $Invested  Current Value  Dividends  %Change.
Now you know why they call them "spread" sheets!

That's enough horizontal spread. Now we get to the good stuff -- comparing your portfolio performance with the Standard & Poor's 500 Index. The easiest way to do that is to pretend you purchased a Spider, or S&P Depositary Receipts <% if gsSubBrand = "aolsnapshot" then Response.Write("(AMEX: SPY)") else Response.Write("(AMEX: SPY)") end if %>. Set up a row for it, exactly like your stock rows, several lines below your Foolish Four stocks. You can get a historical quote for SPY at www.bigcharts.com for the day you started your portfolio and get the current value when you get your quotes for the Foolish Four stocks, or simply divide the S&P 500 value for the day by 10 since SPYs track the S&P.

To get the percentage increase for your portfolio as a whole, create totals for columns F, G, and H (=SUM(F2:F5, etc.) and copy your %Change formula into the totals row. (If your initial investments in each stock were exactly equal, you could just average the %Change column, but that's highly unlikely).

What if you have a bit of cash in your account? Simple, just enter the cash amount above the totals row for both the $invested and Current Values columns and include those cells in your totals formulas (=SUM(F2:F6, etc.). You can account for interest received the same way you account for dividends. The %Change in your Totals line gives you the total growth for your portfolio.

Congratulations. You have a working spreadsheet that will calculate the return for each Foolish Four stock and your portfolio as a whole and also let you compare both your portfolio and your individual stocks with the S&P over the same time period. But what do you do at the end of the year? This is a long-term strategy and we should be planning to track it for years and years, right?

Well, it gets tougher here. We will have to tackle multiple-year tracking tomorrow. Another topic that several people inquired about is how to account for additions or subtractions of cash during the course of a year. Obviously, if you increased the amount of cash after the start of the portfolio, it would increase your investment returns whether your stocks went up or not.

Our super-simple model will only handle accounts that don't have cash added or subtracted. If you want to tackle that, it's not hard, although unless you are really enjoying the spreadsheet experience, this may be a good time to look to a commercial program. If you want to try it yourself, see Portfolio Performance.

Fool on and prosper!

Today's Stock Lists | 1998 Dow Returns

03/16/99 Close
Stock  Change   Last
--------------------
CAT  -1  7/16  44.00
JPM  -1  3/4   123.63
MMM  -1  1/8   78.56
IP   +   5/16  43.63





                   Day   Month    Year   History
        FOOL-4   -1.37%   4.17%   5.23%   6.79%
        DJIA     -0.28%   6.70%   8.54%   8.11%
        S&P 500  -0.07%   5.49%   6.59%   6.85%
        NASDAQ   +0.32%   6.61%  11.25%  12.77%

    Rec'd   #  Security     In At       Now    Change

 12/24/98    9 JP Morgan    105.51    123.63    17.17%
 12/24/98   14 3M            73.57     78.56     6.79%
 12/24/98   24 Caterpillar   43.08     44.00     2.14%
 12/24/98   22 Int'l Paper   43.55     43.63     0.17%


    Rec'd   #  Security     In At     Value    Change

 12/24/98    9 JP Morgan    949.62   1112.63   $163.01
 12/24/98   14 3M          1030.00   1099.88    $69.88
 12/24/98   24 Caterpillar 1034.00   1056.00    $22.00
 12/24/98   22 Int'l Paper  958.12    959.75     $1.63

              Dividends Received      $15.04
                             Cash     $28.26
                            TOTAL   $4271.55

</FOOLISH FOUR PORTFOLIO>