This is freeware: no copyright, no 
fees or donations; just a "giveback" 
to our many generous "uploaders."

This is the documentation for using 
either Stock1.wks or Stock2.wks (both 
in the Stock.wks file) to track and 
evaluate a portfolio of securities.  
Both are worksheet files with 
equations in many of the cells to 
make the task easier for you.  Stock1 
is longer and is designed to have you 
input not only the current price of 
your securities, but also the actual 
income you receive from each issuer; 
Stock2 uses only the estimated income 
which you can obtain from the 
financial pages of your newspaper or 
your broker.

To give you a feel for the time 
involved: the portfolio I have on 
Stock1 (32 securities) takes 30 
minutes to update; 15 minutes for the 
one on Stock2 (19 securities).

This is a long file (six 60-line, 80-character per line pages), so I 
suggest you print it out to use for 
reference as we fill out your 
worksheet.

History ("go get 'em" types can skip 
this section): I'm Ted Baynes 
(72730,1163), for 26 years a 
commercial banker who never worked on 
the investment side of the house.  
However, as a volunteer, I chair the 
Finance Committee of a non-profit 
which has the good fortune to have a 
healthy reserve fund invested in 
common stocks, corporate bonds, 
treasuries and a money-market fund.  
In order to  do that job, I've taught 
myself a few things about investing.  
The more I learned, the more I became 
frustrated with the information our 
brokerages provided monthly.  I 
wanted to see the capital gain (or 
loss) and yield based on cost every 
month.  The problem is that the 
brokerage house would have to have in 
its files our basis (the IRS term for 
how much we paid for each security) 
and they showed no interest in 
preparing a statement which met my 
needs.  Also, for a time, we had 
accounts at two brokerages so we got 
two statements, each with its own 
format.  So, my frustration level was 
high when I acquired my first and 
only computer, this Portfolio.  That 
happy convergence of circumstances 
allowed me to learn to drive a 
spreadsheet while creating a report 
which met my needs.  There is nothing 
original in these files, but no 
commercially prepared statement that 
I've seen (including the [$] services 
on CIS) does the job as well as "my 
way" for me.  Maybe you will find it 
useful also.  Your comments. 
suggestions and questions are 
welcome.  

Now, let's get on with it.

Print both .wks files.  They are wide 
(Stock1, 19 columns; Stock2, 14), so 
you may have to print them in 
sections using the Files-Print-Range 
command.  This allows you to "paint" 
the portion you want to print.

You will see that there are "dummy" 
entries in each security section.  
The information is real, but the 
names have been changed.  The "dummy" 
rows show you the information your 
finished product will provide.  If 
you think it looks promising, let's 
keep on.  If not...

You will also see lots of "ERR"s.  
These cells have formulas which need 
data from other cells which are now 
empty.  Since computers refuse to 
divide by zero, they return ERR when 
a zero value is found.

Now, you may want to load one of the 
.wks files and just wander around for 
a while.  Up to you.  As we fill in 
your data you'll learn more, perhaps, 
than you ever wanted to know about 
its contents.

Now, go find the basis (how much it 
cost you to acquire it) for each 
security you own.  I'll wait for you.

                                      
-2-


Welcome back.  Now, count and record 
the number of years and months you 
have owned each security.

Then, look at your most recent 
brokerage statement, newspaper or 
whatever to determine the current 
price of each.  Fractions should be 
converted to decimals, e.g. 1/8 = 
.125, etc.

Now, load Stock1.wks or, if you've 
decided that you prefer the shorter 
version, Stock2.wks.  If you start 
with Stock1 and later change your 
mind, no problem.  The instructions 
are the the same until we get to the 
part where the two differ, and then 
I'll let you know what to do if you 
want to "shrink" Stock1 into the 
shorter Stock2.

Next, (PLEASE, PLEASE do NOT skip 
this step) use the Files-Save command 
to give this file (which is a copy of 
the file still on your "disk") a name 
of your choice.  In this way, should 
there be a problem, you have the 
original intact and ready for another 
day.

Now, you may want to fill in the 
information called for in row 1.  The 
first line is all in cell A1.  You 
can be as wordy as you want, but I 
wouldn't go beyond Column H which is 
the furthest I can go when I print 
the first section on 8 1/2 by 11 
paper.  (That's 72 spaces with the 
default column width of 9.)

I list our securities in alphabetical 
order within each section.  Suit 
yourself.

Now, move the cursor to the cell 
which now contains "ABC Corp".  Use 
the Worksheet-Titles command to turn 
"Titles" on.  Take it from a guy who 
learned the hard way, it's easy to 
get lost in a big spreadsheet with 
only a 40 x 8 display.  The Titles 
function allows you to keep your row 
and column headings in view as you go 
deeper into your spreadsheet.  
Speaking of that, you'll find it 
easier to work with this (or any 
spreadsheet on the Port) spreadsheet 
if you "zoom".  All that means is 
that you use Function 5 (hold the Fn 
key down while pressing the 5 key).  
This removes the border and gives you 
more workspace.

Next, pick your first security.  Give 
it a short name (9 characters) or use 
the full name (which requires using 
the Range-Width command to increase 
column A's width [up to 30 
characters]).  I don't recommend the 
latter, but it's your spreadsheet, 
not mine.

Now, type the short name of your 
first security where "ABC Corp" is 
now.

The next column is "DateAcq"(Date 
Acquired).  Put the appropriate date 
where "May 85" is now.

"NrShs" stands for Number of Shares.  
Fill in this cell.

"Basis" is where you put what it cost 
you to acquire this stock.  This 
column is formatted for 0 decimal 
places to save space ($ signs are 
also omitted throughout both 
spreadsheets; again, to save space).  
You can, of course, key in the cents 
and it will be stored, but not 
displayed within the cell.

                                     
 -3-


Next, key in the current price, using 
decimal fractions.  The column is 
formatted to display 3 decimal 
places.  Here again you can store, 
but not display, many more.

Now you will see your efforts 
rewarded.  Voila! There's the current 
value ("CurVal") and the capital gain 
or loss ("CapGain") "magically" 
displayed for you.

"Gain#Yrs" (Gain Number of Years) 
asks for the number of years you have 
held this security, to the nearest 
month.  To save you the calculation, 
one month is .08333 in decimal form.  
Thus, 2 yrs, 1 month is entered and 
displayed as 2.083(333).  BUT, don't 
enter anything yet.

First, peek into that cell (H7) and 
you will see "5.58+A54".  Being a 
lazy fella, I got tired of 
incrementing each of these cells by 
.08333 every month.  There may be a 
better way to "automate" this, but 
the best I could come up with was to 
add the number of years and months we 
had owned that security when I built 
this file (5 years and 7 months or 
for these purposes 5.58) to a 
"constant" which I would only have to 
put in once.  Thus, by changing the 
value in A54 once each month I can 
"automatically" increment each 
security's "Gain#Yrs" cell.  I 
suggest you begin by leaving A54 as 
is for now (the value is 0).  Next 
month, enter "1/12" (.08333 will be 
displayed); the next month, "2/12" 
and so on.

After you enter your value, you will 
see the ANNUALIZED percentage gain 
appear in the "Ann%Gain column.  
Because I prefer an "apples-to-apples" comparison, I chose to 
annualize rather than to calculate 
the "raw" gain ("raw" meaning the 
percentage gain over the life of a 
security, e.g.,  I found it hard to 
compare a 29% gain over three years 
[9.67% annualized] to a 15% gain over 
18 months [10% annualized]).  If you 
want it "raw", change the formula in 
that cell from "(G7/D7)/H7" to 
"G7/D7".  Then copy the contents of 
that cell to the rest of the cells in 
that section using the Range-Copy 
command.  HOWEVER, before you make 
that change, be warned that the 
calculation which gives the best "one 
number" score "AnnTotRet" (see below) 
will be affected and made practically 
meaningless for comparative purposes.

The next 5 columns are what 
differentiate Stock1 from Stock2.  I 
created this section because I 
discovered that we had stocks (Texaco 
is the best example, but many mutual 
funds pay year-end "special" 
dividends which would be "lost" 
unless you use this approach) which 
paid more than the annual estimated 
dividend.  Thus, as in the case of 
Texaco, our real yield far exceeded 
the estimated; however, using these 
columns requires you to enter your 
income by security each month.  If 
you received a dividend on this 
security this month enter the amount 
in "CurMoInc" (Current Month's 
Income).

Then, you need to calculate the time 
period for income.  If you start from 
today (as opposed to looking back 
over your records to see how much you 
have earned since you bought this 
security and then entering that 
amount in the column "PtDInc" [Period 
to Date Income] and the time period 
covered which may very well be the 
same value as you've entered in H7; 
if so, you can simply put '+H7 in 
this cell), you would put .08333 in 
this and all other cells in this 
column.  If you peek into this cell 
before entering new data, you will 
see that the "A54" trick can also be 
used here, e.g. .0833+A54.

                                     
 -4-

If all that is too much trouble, use 
the Worksheet-Delete-Columns command 
to wipe out Columns J-N (it takes 
several seconds for this command to 
execute).  Then, go to cell M7 which 
now shows "ERR" because inside the 
cell there is a defective formula 
(because we just decimated the cell 
it was looking for: L7); insert this 
new one "+I7+N7" (which adds 
"Ann%Gain to "CurYldCost" to give you 
Total Return (see below).  Range-Copy 
this formula into the remaining cells 
in this section and into its Total 
line.  Voila!  You now have Stock2!

"EstAnnInc"(Estimated Annual Income) 
is calculated  by multiplying the 
number of shares you own times the 
annual income per share (you can get 
this from your newspaper or your 
broker).

You've just finished the first 
security.  The rest of the values 
have been calculated for you.  Ain't 
"automation" grand?

They are:

-"CurYldCst" (Current Yield Cost): 
Your annual yield (annual income 
divided by capital invested) based on 
what you paid for the security, a 
more meaningful number, in my view, 
than the next one which is

-"CurYldMkt" (Current Yield Market): 
Your annual yield based on the 
current market value of your 
security.

-AnnTotRet (Annual Total Return): The 
sum of your annual yield (based on 
your cost) plus the annualized 
percentage gain or loss.  This 
measures what your money has done for 
you on an annualized basis.  Of 
course you can realize the capital 
gain -- or suffer the capital loss -- 
only by selling it as the current 
price (otherwise you have just a 
"paper" gain or loss), but if you 
want a "one number" score for your 
securities, this is it.

"%ofPort" (Percentage of Portfolio): 
tells you the percentage of the total 
for each security.  For example, one 
of our guidelines is that we want no 
security to be more than 5% of the 
total, so we will sell some shares to 
get at or below our target.  
Similarly this number in the Total 
line for each section tells you what 
percentage of your portfolio is in 
stocks, bonds and "cash" (Money 
Market fund(s) plus true cash 
balances).

Okay, one done, now on to the next 
one in whatever order you've chosen.

There are 14 rows in the Stocks 
section.  Either that's just right 
(unlikely) or too many or too few.

If it's just right, go ahead to the 
end of the section.

If you don't need 14 rows, you will 
want to delete the surplus rows.  
Here's how.  Put your LAST security 
in Row 20 (the row just above the ---- line.  Then, use the Worksheet-Delete-Rows command to "paint" the 
rows you want to delete.  Doing it 
this way allows the program to 
accomodate more easily to row 
deletion and to adjust the formulas 
in the Total line automatically .

                                     
 -5-


If you need to add rows, move the 
cursor to any cell in Row 20; use the 
Worksheet-Insert-Row command to 
"paint" in the number of new rows you 
need.  Again, this method allows the 
formulas to adjust themselves 
automatically.

A few words about the "StockTot" 
(Stock Total) line.  Not all of the 
values shown are arithmetic totals.  
Gain#Yrs, Inc#Yrs are simple 
averages, i.e., not weighted; since 
these values are used to calculate 
Ann%Gain and Ann%Yld, the effect is 
to give you the average Gain and 
Yield for this section of your 
portfolio.   Similarly, CurYldCost, 
CurYldMkt and AnnTotRet are the 
average values for this section.  The 
same is true for the "IncTot" 
([Fixed] Income Total) and "PortTot" 
(Portfolio Total).

When you get done with all this 
spreadsheet "stuff", I suggest you 
spend some time studying the Yield, 
Gain and Total Return values for each 
of your stocks because after all, 
this why you're doing all this.

Now, on to the next section: Bonds.  
The only trick here is that you will 
need to calculate a "fake" number of 
shares.  For example, DEF Corp is 
really one $25,000 bond, but because 
bonds are priced in percentages of 
par (face value), the "number of 
shares" is 250 which allows the 
formula to give you the "right" 
answer (250 x 97.625[%] = 24406 
[$24,406.00]).  The rule: drop the 
last two digits of the face value of 
the bond, e.g. 25,000 becomes 250, 
etc.

Other than that, everything is the 
same.  I'll wait while you complete 
this section (including expanding or 
contracting the number of rows to 
meet your needs AND changing the 
formulas in column M).

In the real non-profit spreadsheet, I 
have yet another section for U.S. 
Treasuries.  I left it out of these 
two to simplify them a bit.  If you 
have Treasuries and want them in a 
separate section, you can create one 
if you know your way around 
spreadsheets.  If not, leave me a 
message and I'll upload a version 
with that section added.

On to the Cash section.  In our case, 
the Money Market Fund (MMFund) tends 
to vary from month to month as income 
and principal flows in and out.  By 
the strictest of definitions, we have 
no "basis" to use as a starting point 
and therefore, we can have no capital 
gain.  So far so good, but I wanted 
my spreadsheet to "settle" to the 
broker's statements, i.e. the CurVal 
Total column will equal the total on 
the broker's statement.  Also, I 
wanted to know the average yield (on 
basis and on market), capital gain 
(in absolute dollars and as an 
annualized percentage) and Total 
Return for the entire portfolio.  To 
meet those several objectives, I 
decided to calculate a basis for the 
MMF which is really a "plug" or 
"settling" number.  (That's why you 
will find a formula in a cell which 
you would normally fill in yourself.)  
While this caculated number has no 
significance, it does allow me to 
achieve all of my objectives.  Thus, 
the Capital Gain is a "real" number 
because it is the sum of the "real" 
capital gains from the Stocks and 
Bonds section and no capital gain is 
calculated in the Cash section.  

Purists could argue that the 
annualized percentage gain is not 
"real" since it's calculated using a 
total which contains a "plug" number.  
In fact, purists, mathematicians and 
securities professionals could argue 
with several aspects of this whole 
spreadsheet.  Let 'em.  The PortTot 
line gives me a "feel" for the entire 
portfolio which is all I need because 
any decisions we make are based on 
the individual security row, not the 
Total line.

                                     
-6-


Okay, the program will calculate the 
MMF basis for you, so all you have to 
do is to enter its current balance in 
the "CurVal" column.

Also, since the balance tends to 
fluctuate, the only way to estimate 
the annual income from that source is 
to put the current yield in either 
the "Ann%Yld" cell (Stock1) or 
"CurYldCst" (Stock2), then in the 
"EstAnnInc cell" you will see a 
calculated annual income produced by 
multiplying the current balance by 
the current yield.

If you have cash at the statement 
date that's not earning, use the Cash 
row and just enter the balance in the 
"CurValue" cell.

"PortTot" (Portfolio Total) is where 
all this comes together.  Remember I 
used a "plug" for the basis for our 
MMFund, that caculation uses the 
value calculated for you in the 
"PortTot" Basis cell.  The purpose, 
as I said, being to allow the Total 
line to be what it says it is and for 
the calculations on that line to be 
useful.

You may now be saying to yourself: 
"Whatever he just said, I don't 
understand or, I do, but in either 
case, I don't care to play."  I don't 
blame you, the explanation does make 
all this sound complex (it ain't).  
Do you have an option?  Sure, just 
Worksheet-Delete-Row the whole Cash 
section (you will have to adjust the 
formulas in the PortTot row) or, even 
easier, delete the "dummy" values in 
the Cash section and leave those rows 
blank (no forumula adjustment 
required).

Just below PortTot is "PrevMo" 
(Previous Month) which you will want 
to fill with 0's this time.  Next 
month, BEFORE you update, use 
Worksheet-Recalculate to turn off 
Recalculate, then key the figures in 
the "PortTot" row into this row.  You 
might think you could use the Range-Copy command here, but you can't 
(well, you can, but you won't get the 
result you want for reasons I won't 
bore you with here.)

UPDATING
Next month, after moving the values 
in the PortTot line to PrevMo and 
with Worksheet-Recalculate still off, 
key in the current per share prices, 
check to see if the EstAnnInc values 
are still current (companies do 
increase and decrease their dividends 
from time to time).

If you've chosen to use Stock1, you 
will also need to up date the PtDInc, 
CurMoInc and CuPtdInc columns.  With 
Worksheet-Recalculate still off, 
delete the value in CurMoInc column 
and key in the value in CuPtdInc into 
PtDInc.  Then, fill in this month's 
income received by security in 
CurMoInc.  Use Files-Save (which, if 
you are wise, you will do every few 
minutes or so during the creation of 
your own spreadsheet to be sure you 
don't lose a lot of hard work because 
of a crash), then use Worksheet-Recalculate to turn that function on.  
And, whaddya know, you're done!

Happy tracking!