%OP%TM1
%OP%BM1
%OP%LM5
%OP%HE/%H2%Z88 Users' Club Software Library%H2%/
%OP%FO/%H2%Z152%H2%//@P@/
%CO:A,12,70%%C%%H1%%H2%INVESTMENT PORTFOLIO MANAGER

%C%%H1%BY STEVE EDWARDS - 4794



This spreadsheet can be used to keep track of your investments 
(shares, fixed interest securities, unit trusts, PEP's etc). The 
purchase and sale of shares are in columns A to G. The income from 
investments is in columns I to N The summary of the previous sections 
and the estimated profit based on current market values is in columns 
P to X.

The instructions below only mention shares but the points equally 
apply to other types of investment.  The section dealing with income 
from investments assumes income is received net of basic rate tax. If 
you have any investments for which income is received gross the 
easiest way round this is to ignore columns M and N (and blank out 
the headings - do not use the delete command) and rename column L as 
"GROSS".

The spreadsheet is initially set up for only 10 investments, which 
should cope with many people's needs. However if you have more than 
10 investments the spreadsheet can be extended to handle them - see 
section E - CREATING ADDITIONAL SECTIONS.

In the instructions which follow company names, share types and dates 
should be entered whilst in TEXT mode. All other entries should be 
made whilst in NUMBER mode. The mode can be changed via the OPTIONS 
menu. Alternatively, enter everything in NUMBER mode and change the 
mode of the names, types and dates by pressing <>ENT whilst the 
cursor is in the slot containing such data.


%H2%METHOD

%H1%A - PURCHASE OF SHARES

For shares in a company not previously held (or a diferent type of 
shares in a company already held) select the first blank section. 
Enter the company name and share type (eg ordinary, preference) in 
column B at the top of the section.

For shares in a company for which you already have a similar type of 
shareholding, select the first spare row in the appropriate section.

Enter the date & number of shares acquired in columns A & B.  

Enter the transaction type in column C ,eg "1" for normal purchase, 
"2" for rights issue, "3" for bonus (scrip) issue. Do not use a 
number greater than "3" as this is used for disposals. The 
transaction type is not necessary for the purchase of shares but 
merely provides additional information. 

Enter the unit cost in column D, in which case the total cost in 
column E will be automatically calculated, or alternatively ignore 
the unit cost column and enter the total cost in column E, 
overwriting the formula already stored in the slot. (However, when 
the section for a particular type of share is full and earlier 
transactions are deleted to allow more room any formulae deleted will 
not reappear unless manually re-entered , and so when that particular 
row is reused you will have to enter total cost and not unit cost - 
see below.)

Once all transactions have been entered press <>A to recalc thereby 
updating the figures.

The spreadsheet will now show the number of shares currently held, 
remaining cost (after disposals), any profit on disposals on the 
bottom row of each section and the profit on each disposal.


%H1%B - SALE OF SHARES

Move to the appropriate section for the shares you have sold.

Enter the number of shares you have sold AS A NEGATIVE NUMBER in 
column B.

Enter "4" as the transaction type in column C. This is essential to 
ensure the proper working of the spreadsheet.

Enter either the proceeds per unit in column D or total proceeds in 
collumn E - see section on purchase of shares.

Once all transactions have been entered press <>A to recalc.

The spreadsheet will now show the number of shares currently held, 
remaining cost (after disposals), any profit on disposals on the 
bottom row of each section and the profit on each disposal.

The cost of shares disposed of is calculated from the average cost of 
each share held at the date of disposal.This method is not the same 
as that used in capital gains tax computations, where complex 
matching rules are involved. However, total net gains up to £5000 
made by an individual in any tax year are exempt from tax and so most 
people do not pay capital gains tax. In general gains calculated 
according to the capital gains tax rules will be slightly lower than 
those calculated using this program. The longer ago the shares were 
purchased the greater will be the divergence between the 2 figures. 


%H1%C - INCOME FROM INVESTMENTS 

Income is shown in columns I to N.

Enter the date of receipt of the income (dividends,interest) in 
column I.

Enter the number of shares held in column J.

Enter the dividend/interest per unit in column K.

Alternatively, enter the net income in column L, overwriting the 
formula, in which case you may leave columns J and K blank. (However, 
when the section for a particular type of share is full and earlier 
transactions are deleted to allow more room any formulae deleted will 
not reappear unless manually re-entered, and so when that particular 
row is reused you will have to enter net dividend and not merely 
income per unit - see below.)

Once all transactions have been entered press <>A to recalc.

The spreadsheet will automatically calculate the tax (at basic rate 
tax of 25%PC%) and the gross income.

The bottom row of each section will show the total gross, tax and net 
to date.


%H1%D - WHAT TO DO WHEN A SECTION IS FULL

Save the file under a different name so you have full transaction 
details of all transactions to date. The procedure which follows will 
delete all transactions to date from the current file for any section 
which is full.

For each shareholding section which is full enter the figures from 
the "TOTAL" line in the top line of the section for columns B,F and 
G. Overwrite the first date in column A of the section  with B/F to 
indicate that the figures are balances brought forward and not single 
entries.Block replicate a blank slot into columns A to D for all rows 
except the first one. Block replicate a blank slot into the top row 
of columns C,D and E. Press <>A to recalc. If any slot in column E 
still contains a non zero figure ,then move the cursor to that slot 
and press SPACE followed by ENTER. This will only be necessary where 
the original cost/proceeds were entered in total - see sections on 
purchase & sale of shares. When all such slots have been corrected 
press <>A again to correct the total figure in column E.

For example, in the first section enter the figures from B17,F17 and 
G17 in B7,F7 and G7.

Enter "B/F" in A7.
<>BRE      RANGE TO COPY FROM   D4
           RANGE TO COPY TO     A8D16
<>BRE      RANGE TO COPY FROM   D4
           RANGE TO COPY TO     B7D7
<>A

If say E12 contains "5678.22" move the cursor to E12 and press SPACE 
ENTER. <>A

All slots should now show the correct figures.

For each income section which is full enter the figure from the 
"TOTAL" line in the top line of the section for column L. .Block 
replicate a blank slot into columns I to K for all rows in the 
section. Enter "B/F" in the top row of column I to indicate the 
figures are balances brought forward and not single entries.

Press <>A to recalc. If any slot in column L (except the top row)  
still contains a non zero figure ,then move the cursor to that slot 
and press SPACE followed by ENTER. This will only be necessary where 
the original dividends/interest were entered in total - see sections 
on income from investments.  When all such slots have been corrected 
press <>A again to correct the total figure in column N.

For example, in the first section enter the figure from L17 in L7:
<>BRE      RANGE TO COPY FROM   K4
           RANGE TO COPY TO     I7K16
<>A

If say L12 contains "218.72" move the cursor to L12 and press SPACE 
ENTER. <>A

All slots should now show the correct figures.


%H1%E - CREATING ADDITIONAL SECTIONS

This is achieved by the following method:

1) It is recommended that you always keep a "spare" section from 
which to create additional sections.

2) It is essential that the final section is used to create 
additional sections as the earlier sections have slightly different 
formulae which made it easier to prepare the original spreadsheet. 
The formulae in the final section have been modified to permit the 
creation of additional sections.

3) Block replicate the final existing section to about 2 rows below 
the end of the final section. e.g. as the spreadsheet stands 
originally:
<>BRE
RANGE TO COPY FROM  A159N173
RANGE TO COPY TO    A176                 

4) If the final section is already in use, the creation of the 
additional section will copy all the entries which appear in the 
final section into the additional section. These must all be erased. 
To do this block replicate a blank slot in to columns A to D and H to 
K. e.g. in the example provided above:
<>BRE
RANGE TO COPY FROM  D0
RANGE TO COPY TO    A180D189
and  <>BRE
RANGE TO COPY FROM  DO
RANGE TO COPY TO    H180K189
   
Also if cost of shares/proceeds have been entered in total in column 
E these will have to be erased as above. Similarly, for net income 
entered in total in column L.

6) The new section is now ready for use.
%P0%
%H1%F - INVESTMENT SUMMARY

This section as the name implies displays a summary of your 
investments. It shows for each type of investment in each company the 
number of shares held, their total cost (after deducting any shares 
sold), total net income to date, total profits on disposals and if 
you enter the current market values per share in column V it will 
calculate the profits to be earned if you sell all your shares 
immediately.

This section is set up already for 10 investments but if you set up 
additional sections as explained in note E then you will have to 
enter some formulae yourself as follows: e.g. if you set up an 
additional section starting at A190, enter in the first available row 
(ie row 17) the following formulae whilst in NUMBER mode:

in P17 enter the reference B190 - to copy the company name
in Q17 enter the reference B191 - to copy the share type
in R17 enter the reference B204 - to copy the total number of shares 
from the total line
in S17 enter the reference F204 - to copy the total cost of the 
shares from the total line
in T17 enter the reference L204 - to copy the total income from the 
shares from the tptal line
in U17 enter the reference G204 - to copy the total realised profits 
from the total line

In this way the spreadsheet can handle up to 20 investments. If more 
than this are necessary you will also need to move the formulae on 
row 30 further down (ensuring that the range specified in the 
brackets includes all the rows used for the investments). If more 
than 20 investments are set up, the columns W and X will also have to 
be amended if you wish to know the profit to be earned at current 
prices. e.g. if you have 22 investments 2 more rows will have to be 
set up (columns W and X are already set up to handle up to 20 
investments). To do this:
<>BRE 
RANGE TO COPY FROM    W26X26  ie the columns & rows to copy
RANGE TO COPY TO      W27W28  ie the rows to copy to


%H1%G - PRINTOUTS

%H4%1 printouts of purchases/sales sections

Move the cursor to A1 
Press <>Z to mark the start of the block
Press <>+CURSOR DOWN to move to the bottom row of the spreadsheet
Move the cursor right until it is in column G
Press <>Z to mark the end of the block
Press <>PO then ENTER when the options appear on the display

Providing your printer is correctly set up the printout will be 
produced. I have inserted page breaks so that each A4 size page 
contains 3 complete sections. If you use additional sections I 
recommend that you insert similar page breaks after every 3 sections. 
To insert a page break move the cursor to the row where you wish the 
page break to appear and select INSERT PAGE from the EDIT menu.
%H4%2 printouts of income sections

Move the cursor to I1 
Press <>Z to mark the start of the block
Press <>+CURSOR DOWN to move to the bottom row of the spreadsheet
Move the cursor right until it is in column N
Press <>Z to mark the end of the block
Press <>PO then ENTER when the options appear on the display

Providing your printer is correctly set up the printout will be 
produced. I have inserted page breaks so that each A4 size page 
contains 3 complete sections. If you use additional sections I 
recommend that you insert similar page breaks after every 3 sections. 
To insert a page break move the cursor to the row where you wish the 
page break to appear and select INSERT PAGE from the EDIT menu.


%H4%3 printouts of the summary

Move the cursor to P1
Press <>Z to mark the start of the block
Move the cursor to colmn X using the TAB key
Move the cursor down to the "TOTAL" line using the SHIFT key and the 
down cursor key together
Press <>Z to mark the end of the block
You will require a wider piece of paper than A4 to fit the summary on 
unless you use condensed print (see your printer manual or design a 
new printer driver on the Z88 to produce condensed print) or reduce 
some of the column widths. If a column is much wider than required 
move the cursor to that column and press <>W then enter the new 
column width. I have set the column widths of the columns in this 
section to 14.
Press <>PO followed by ENTER to print the summary


%H1%H- GENERAL POINTS

All data should be entered with the spreadsheet set to MANUAL 
recalculation in the OPTIONS menu.

As the spreadsheet works from left to right (except for the average 
cost per share shown in the "TOTAL" line of column D) 2 recalcs 
should be sufficient to fully update the spreadsheet but allow for 3 
just in case!

If you do not have many share dealings the sections as set up should 
last you a long time. Income from investments is usually paid every 6 
months and so each income section should last for 5 years before it 
becomes full.

Alternatively you may prefer to clear out all the records at the 
start of each year (after saving the spreadsheet under a different 
name first) so that each file shows the details of your transactions 
for a year.
%CO:B,12,60%%CO:C,12,48%%CO:D,12,36%%CO:E,12,24%%CO:F,12,12%