The following two spreadsheets are samples of spreadsheets mentioned in The Mechanics of Money (Part 3): How I Invest here.

These spreadsheets are compatible with Windows and Linux. I don’t have a Mac to test it on, but should work. They were created using LibreOffice on Linux, and tested using Microsoft Word on Windows.

1. My investment account balance sample spreadsheet is AccountBalance.xlsx You can download a copy here.

This basic spreadsheet has 5 columns: Date, Activity, Debit, Credit, Balance.
Date: enter date of activity
Activity: enter a word or two describing the activity
Debit: withdrawal (if any)
Credit: deposit (if any)
Balance: current balance.

For future reference, the formulas and formatting of the columns is as follows:
Date column: Formatted for MM/DD/YYYY. You can change this if desired.
Activity column: Formatted as text
Debit, Credit, Balance columns: Formatted as currency (two decimal digits).
Balance column: Formula is (where “x” is row number and “y” is previous line number) =Ey-Cx+Dx
Example: for Line 3, the first transaction after balance forward, the formula is =E2-C3+D3

To start, enter the starting “balance fwd” in cell E2. Then enter any additional transactions as they occur each on a separate line. When entering a new item, enter the date in the Date column, then either the credit or debit amount (or both) for that date, then click in column E followed by (on Linux or Max) Ctrl-D. This brings the formula from the previous line’s column E into the one you just clicked in, and calculates the new balance.

2. The following is a copy of the spreadsheet I use to keep track of my stock ownership. It allows me to track all transactions, including stock purchases and dividends received, StockSpreadsheet.xlsx You can download a copy here.

Don’t be put off by divide by zero (#DIV/0!) error messages. Once you enter a transaction, the correct value will appear.

I have included some of the formulas for some cells for the first two companies.

The spreadsheet has two sample company pages (Company 1, Company 2) to start you off. To start, enter transactions in the appropriate company page (see below). You can change the names of the individual pages (tabs) but you may need to redo the formulas on the summary page that references the company pages. Add a new page for any additional companies.

Each individual page for a stock has the following columns:
Date, Dividends per share, Purchase amount, Dividend, Total amount invested, Service fees, Amount invested, Price per share, Number of shares, Total shares in plan, Market value.
Date: the date of a transaction.
Dividends per share: how much the company is paying for each share owned
Purchase amount: If I purchase shares directly I enter the amount here
Dividends: Total amount of dividends paid
Service fees: Sometimes the transfer agent levies fees for some transactions
Total Amount invested: The total amount of dividends received or amount invested, minus any fees
Price per share: The price of one share at the time of investment (dividends paid or direct investment)
Number of shares: For his transaction, how many shares were added to my account
Total shares in plan: The total number of shares in this company I own
Market value: This is total amount invested times price per share.

Here is an overview of the Summary page:
There are nine columns: Stock symbol, Company name, Market Value, Quarterly dividend, Annual dividend, Yield (%), Monthly dividend, Portfolio %, % of Dividends.
Market value: from the last “Market value” in the company’s page
Quarterly dividend: from the last quarterly dividend received
Annual dividend: Quarterly dividend times 4
Yield (%): Annual dividend divided by Market value
Monthly dividend: Quarterly dividend divided by 3
Portfolio %: The percentage of total portfolio for this stock
% of Dividends: The percent of all dividends for this company’s dividend

The Quarterly dividend for each company on the Summary page gets its value from the Company’s individual page. Annual dividend is just quarterly times 4. Monthly dividend is just quarterly dividend by 3. The annual calculation does not account for changes in dividends in mid-year that a company may make, but it is close enough for me. Similarly, monthly dividend amount is not usually a very useful number, I added it to give me a perspective when comparing to my monthly bills and expenses.

For future reference, the formulas and formatting of the columns is as follows:
Summary page:
Columns C, D, E, G: Currency
Columns F, H, I: Percentage
Cell C1 (Total portfolio): =SUM(C4:C99)
Cell F1 (Total Q dividends): =SUM(D4:D99)
Cell C4 (Company 1, mkt value): =$’Company 1′.J4
Cell D4 (Company 1, quaterly div.): =$’Company 1′.D4
Cell E4 (Company 1, annual div.): =D4*4
Cell F4 (Company 1, Yield %): =E4/C4
Cell G4 (Company 1, monthly div.): =D4/3
Cell H4 (Company 1, Portfolio %): =E4/C1
Cell I4 (Company 1, % of Dividends): =D4/F1
Cell C5 (Company 2, mkt value): =$’Company 2′.J4
Cell D5 (Company 2, quaterly div.): =$’Company 2′.D4
Cell E5 (Company 2, annual div.): =D5*4
Cell F5 (Company 2, Yield %): =E5/C5
Cell G5 (Company 2, monthly div.): =D5/3
Cell H5 (Company 2, Portfolio %): =E5/C1
Cell I5 (Company 2, % of Dividends): =D5/F1

Company 1 and Company 2 pages:
Column A: Date column: Formatted for MM/DD/YYYY. You can change this if desired.
Columns B, C, D, E, F, G, J: Formatted for currency to two decimal places. You may need to change this if more decimal places needed.
Columns H, I: Formatted for Number with two decimal places. You may need to change this if more decimal places needed.
Cell J4 (mkt value): =G4*I4

To start, enter the stock symbol in Summary cell A4, and the name in cell B4. On Company 1 page, enter the date of the transaction in cell A4. If you received a dividend, enter the dividend per share in cell B4 and the total dividend received in cell D4. If you made a direct investment enter the amount in cell C4. In either case if there are any fees deducted, enter it in E4. Enter the total amount of the transaction in cell F4 (minus fees if any). Cell G4 gets the price per share, and H4 gets the total amount of shares in this transaction. Cell I4 gets the total number of shares owned after this transaction. Cell J4 will calculate automatically the total current market value.

Once you enter the data in the first company’s page, the values will be displayed on the Summary page.

As mentioned, you can change the names of the individual pages (tabs) but you may need to redo the formulas that reference those pages. Add a new page for any additional companies.

How to keep up to date
Once you enter a second transaction for any company on its page, change the formulas for that company on the summary page. For example, after entering a second transaction for Company 1 on the Company 1 page on line 5, change the summary page formulas for Company 1 in cell C4 from =$’Company 1′.J4 to =$’Company 1′.J5, and if you received a dividend from that company, change summary page cell D4 from =$’Company 1′.D4 to =$’Company 1′.D5.

* * *

Please report any errors to the email address on the Contact page.

* * *


Get Email Alerts When New Item Is Posted

Sign up to be notified when a new item is posted. Click here: Sign Up Link