<FOOLISH FOUR PORTFOLIO>

Fun with Spreadsheets
 Tracking your investments the accurate way

by Ann Coleman (TMF [email protected])

Reston, VA (March 15, 1999) -- Tracking your investment performance is a critical component of Foolish Investing. Sooooooo, are you doing that? Hummmm? My guess is maybe one-third of you are. The rest are feeling guilty. Good.

There are lots of ways to track your investments -- from the back-of-the-envelope Rule of 72 to very complex software packages -- but one of the easiest and most accurate ways is to use a spreadsheet.

Yes, I did say easy. Spreadsheets may be a bit intimidating at first, but they do for calculators what word processing did for typewriters. They make it fun -- not to mention extremely efficient, less error-prone, and much more easily saved. And when it's your investments you are playing with, it's even more fun.

Math whizzes and accountants, you are excused. Click somewhere else or you risk being bored to tears.

Today I am writing to all those Foolish investors who have been thinking, "I really, really need to start tracking my investments!" Yes, you do, and you can start very simply, today.

In many ways a spreadsheet is easier than setting up a portfolio in a software program like Quicken or our own PortTrak, and in all ways it is more educational. Even if you decide to switch to a commercial program, I promise you that you will understand it better if you run through a few simple exercises first.

Of course, you have to have a spreadsheet program. Even if you've never used it,
there's a good chance you have one on your computer. I am using Excel, which comes with Microsoft Office. I'll try to keep the instructions generic, but you might have to run a quick tutorial on simple things like how a formula is identified if your spreadsheet gets picky.

The first thing you want to do is set up some columns so that you will know what goes where. Your spreadsheet will have a row across the top, usually labeled A, B, C, etc., to identify columns, and a column down the side numbered 1,2,3, to identify rows. Each cell is identified by its column and row number -- so the first little square is A1, and the square three cells to its right is D1.

Label the columns by entering the following in the top row (cells A1-- E1). Just type in the cell and don't worry if it runs over.

        A         B           C            D             E          

Date Bought  No. Shares  Stock Symbol Purchase price  Current Price

When you are finished, you can widen the columns by clicking on the letter and then dragging the edge of the letter cell wider, like you do a window, or through the Format menu.

Now you simply fill in the next 4 rows with the information from your Foolish Four portfolio and get the current price from your quote server of choice. I recommend entering all prices as dollars, rather than fractions, but with Excel you can enter the fraction and then format the cell as currency and it will convert it for you. (If you have a dollar symbol on your icon bar, just highlight the cells with the fractions and click the dollar sign.)

Now the fun starts. Add three more columns, F-H: and label them $Invested, Current Value, %Change.

In the first cell under $Invested, type = B2*D2
In the cell under Current Value, type = B2*E2
In the cell under %Change, type = (G2/F2)-1

The equal sign tells Excel that this is a formula. (If you are using another spreadsheet, it may need a different symbol. Check your Help files under Formulas.)

In the first cell, under $Invested, the spreadsheet will multiply the number of shares you bought by the purchase price and tell you how much you invested. In the second cell, it multiplies the shares by the current price and tells you what the investment is worth now, and in the third cell it tells you how much you have made on your investment.

Now, simply copy those three formulas into the cells below to do the calculation for each stock: highlight the cell you want to copy, then type control C. Now click in the cell below it and type control V. (There are easier and faster ways, but they vary from spreadsheet to spreadsheet. See "Copying" and "Filling" in your help files.)

You can see exactly how much each stock has gone up or down, in percentage terms, since you bought it.

This is where spreadsheets get really powerful. Any time you want to know how you are doing, all you have to do is change the current prices and the spreadsheet will automatically recalculate the Current Value and %Change. Realize you made a mistake when you put in your original price? No problem. Change the price and all the other values change, too.

Want to include your commissions? No problem. Go to the $Invested column and change the formula like so: =B2*D2+12. (Instead of 12, use your commission rate.) As soon as you enter it, you will notice that your %Change number drops a bit. If you paid the same fixed commission for every transaction, just copy the new formula all the way down the column.

Let's make it look good. Select the cells with dollar values in them and click on Format/Number to format the money cells as "Currency." Then highlight the %Change cells and format as Percentage. If you have a dollar sign and percent sign on your tool bar, just highlight the cells and click it.

Now, save your spreadsheet, because tomorrow we will look at your portfolio as a whole and compare it to the Standard & Poor's 500 Index.

Fool on and prosper!

Today's Stock Lists | 1998 Dow Returns

03/15/99 Close
Stock  Change   Last
--------------------
CAT  +   3/4   45.44
JPM  +3  3/8   125.38
MMM  -  11/16  79.69
IP   +1  7/16  43.31





                   Day   Month    Year   History
        FOOL-4   +1.65%   5.61%   6.68%   8.27%
        DJIA     +0.83%   7.01%   8.85%   8.42%
        S&P 500  +0.98%   5.57%   6.67%   6.92%
        NASDAQ   +2.10%   6.27%  10.89%  12.41%

    Rec'd   #  Security     In At       Now    Change

 12/24/98    9 JP Morgan    105.51    125.38    18.83%
 12/24/98   14 3M            73.57     79.69     8.32%
 12/24/98   24 Caterpillar   43.08     45.44     5.47%
 12/24/98   22 Int'l Paper   43.55     43.31    -0.55%


    Rec'd   #  Security     In At     Value    Change

 12/24/98    9 JP Morgan    949.62   1128.38   $178.76
 12/24/98   14 3M          1030.00   1115.63    $85.63
 12/24/98   24 Caterpillar 1034.00   1090.50    $56.50
 12/24/98   22 Int'l Paper  958.12    952.88    -$5.25

              Dividends Received      $15.04
                             Cash     $28.26
                            TOTAL   $4330.68

</FOOLISH FOUR PORTFOLIO>