Lotus 1-2-3 DRiP Spreadsheet by Brad Sharp
Here is the updated version of my program. The wysiwyg file (DRIPVALU.FM3) is necessary if you want to print the data - it includes the reduced font information. To help you run it, here are some brief instructions.
On the upper portion, you will need to enter the companies that you DRiP and their Symbols (3 times or copy). You will also need to enter the annual dividend rate per share under "Indicated Annual Dividend".
Each day, you will have to enter the closing price of each stock in your portfolio in the proper column.
You can "clear" my portfolio data, but don't clear any cell that has a formula in it or the program may not work.
All entries are done in the individual stock data field below the top summary portion. Enter your activity and the program will automatically
total your investment and log to the summary portion. You might want to see how I did it before you start clearing entries. This data is my actual DriP Portfolio.
TRANSACTIONS IN PROGRESS: The program will track the value of money you send into your DRiP program, but have not yet received a statement to show how the money was actually invested. To the right of each individual company data field, you will see a section titled "Transactions In Progress". Enter the dollar-value of your OCP in that section. It will automatically update the summary section above. There is a section you can manually input the same data right below the summary. This has no effect on the program and is only there for a reminder.
When you receive confirmation from the transfer agent, you will need to clear the value in the "transaction In Progress" cell by entering a value of $0 and then post your actual transaction.
The summary section contains some interesting information:
VALUE: I reflect value in 3 different terms: Pct of Total Portfolio, Cash + Dividends and just Cash. If your goal is to try to keep your cash contributions to each DRiP relatively equal, then you can use the third column. If you want to keep cash and dividends equal, then the second. Percentage of each stocks in the total portfolio value is in the first column.
DIVIDENDS: I consider dividend income a very important part of the performance of my DRiPs so I added some extra dividend evaluation calculations. Total income per investment is automatically shown. The annual yield reflects the yield you "lock in" each time you make a purchase. This is computed for your whole holding in each stock. This is also shown in the data entry section under the summary on the right. The "market yield" column shows what dividend you would lock in if you make an additional investment today - or the current dividend yield. This info may be useful in determining which OCP to make.
The program also provides an average dividend yield for the total portfolio. My goal was to keep my yield over 2%. Right now I am at 2.2078%. This will change with every investment you make.
ANNUAL PRICE CHANGE: I have just added a tracking formula to calculate the annual price change for the stocks in your DRiP portfolio. It is located on the far right of the sheet. Just right arrow over until you find it. On 12-31 of each year you will have to enter the year end data for your stocks and copy the formulas for the next year. It's pretty simple really.
I have created a macro to print the document. Just hit Ctrl + P at the same time and the thing should automatically print.
Good luck and don't hesitate to e-mail any questions about the program.
Brad Sharp