In a post analyzing the performance of Portfolio One (the longest-lived portfolio) I included an excel spreadsheet with performance information. This excel spreadsheet was built over a number of years and I refined it to include more and more relevant information and responses to questions asked by the beneficiaries of the portfolio.
At first I thought that I could get answers merely by reading the statements that I received from my brokerage firm; but over the years I realized that there was a lot of information that wasn’t readily available, for one reason or another. This information includes:
1 ) inception to date, how much of the portfolio has been spent on fees including any annual fees for the account and commissions on buys & sells
2 ) how much of the return is due to dividends, and what is the accumulated dividends for each stock
3 ) inception to date, how much interest income has been earned on the portion of the portfolio that wasn’t invested in stocks but sat in the interest bearing money market account (usually while waiting to select a stock or immediately after a sale until it is re-invested)
4 ) what is the return on each individual stock, determined as dividend income for that particular stock and any unrealized gain / loss on the change in market price based on today’s price against original cost
5 ) for stocks that are sold, what was the gain or loss on that stock, and was it a short term or long term capital gain (was it held more or less than 1 year)
6 ) for stocks that were sold, how has the stock price done since then? The kids liked to ask this question, basically trying to determine whether or not I was right when I told them to sell a particular stock
7 ) what is the current dividend yield on each stock in the portfolio
8 ) what is the return since inception on the portfolio – there is the easy “is it worth more than put in” but the much harder “what is the rate of return in percentage since inception” which is difficult because you need to determine the timing of each investment
9 ) for the current year, what is tax information needed including dividends earned, interest income, and short term and long term gains and losses from sales of stock
In order to account for this you need to get your statements and do a bunch of work, and track it in a spreadsheet. There may be a simple, off the shelf program that does all of this, but I don’t know what it is. Anyways the real effort is in pulling all the transactions off the statement and entering it – once you set up the spreadsheet it mostly calculates everything for you – so even starting with a canned program wouldn’t save too much time (unless it was directly auto-fed from your brokerage account).
Here are the steps I follow each time and some of the “checks and balances” I do to make sure that I don’t miss anything.
Steps to updating portfolio performance:
1 ) update the prices of stocks in the portfolio (on the “balance” tab). Delete any stocks from the balance that have been sold and make a note to add any stocks that have been purchased to the list. If the stock has split (rare), make a note of this and adjust the share total. Make sure the total dollars in the spreadsheet ties out to the total on the site for those stocks (net of new purchases)
2 ) For sells… go to the sells tab, put in the shares and price, and then calculate the gain or loss by matching the cell against the appropriate one on the “buy” tab (you will have to update the formula). Note if there were commissions or not – we show the loss INCLUDING the commission (which is how it is calculated for tax purposes, too) but we want it separate so that we can calculate total costs to date
3 ) For buys.. go to the buys tab, put in the price and cost and commission
4 ) Go to the dividends tab, and add in manually all of the dividends paid out for each of the stocks specifically. For foreign dividends, put them “net” of the overseas with holding and any fees (so that the amount ties to what is added to the money market) – if this were material, we would collect them separately. This is needed for foreign stocks
5 ) Go to the dividends tab and update the pivot table and make sure the total ties out and that each stock is only represented once (i.e. if there are misspellings you will have 2 exxons, for example)
6 ) Go to the MM transaction tab, and add the monthly interest from the money market fund (only)
7 ) Go to the MM pivot tab, and update the pivot table and make sure all of the money market transactions are picked up
8 ) Go to the MM balances tab, and update the balances to match the statement
9 ) Go to the summary tab. You will need to add a line for each purchase. Copy the formulas across and update them. You will need to eliminate every stock sold from the top part with formulas, and put it in the bottom part with sales. For sales, use the net loss / gain from the sale tab and put a note of what the price was when you bought it, when you sold it, and the current price now. You can see sold stocks because they will have a #REF where their price used to be, as a check.
Also on summary tab, update the dividends by linking each stock to the line in the pivot table by stock to the main tab. The total by stock (done manually) for dividends should total to the same total as the pivot table.
Update the heading to show the month. Then update the date in cell A2 – this moves the month held column which is used to calculate the # of months which is used for the return calculation.
CROSS CHECKS on main tab; that the sum of the dollars for stock value matches the total of the balances; that the total of dividends matches the total of dividends pivot.
10 ) On summary tab – look up the “yield” for dividends of every stock on yahoo. While you are at it, update the notes section for each stock with analysis.
For sold stocks – update where they are today, given the original price and the sales price. This is for Monday morning quarterbacking only.
11 ) update the date on the cash flows tab for rate of return – if you have a new cash inflow, adjust the formula by adding a column and checking to see if it is calculating properly
12 ) if it is time for tax treatment, show the gain / loss for sales in the fiscal year, the amount of interest earned, and the amount of dividends received. These 3 items all go onto the return