By
Let's consider this the preliminary version of the spreadsheet since I know that some of you will probably be writing in with suggestions and improvements and wonderful things I haven't thought of. If that happens (and when has it not?), I will incorporate the suggestions and re-offer it on Friday along with a list of tips from readers on record-keeping.
I started this topic last Wednesday and Thursday fresh from my latest bout with Schedule D and my annual filing spree. If you missed those articles and you aren't up to speed with your own record keeping, you might want to review them to see if this series could be useful to you.
What I am proposing is minimalist investment tracking with a shallow learning curve and no graphics. It will do two things -- give you what you need to fill out your taxes, and provide the total and annual returns for each investment and the portfolio as a whole -- provided you don't try a lot of fancy things.
You can actually duplicate most of this using paper and pencil, or you can set up your own Excel spreadsheet. I am also providing a sample Excel spreadsheet, but it is NOT a formal, widely applicable program. It's what I use, and if it is useful for you either to track your investments or simply to help you follow along with what I am saying, you are welcome to it. Please don't expect much!
You will need Excel 5.0 or higher to run the file, and you will need at least a rudimentary knowledge of Excel. Even if you don't have Excel, you can download the file and open it with another spreadsheet. The formulas may not be there, but you can see how it is laid out.
The spreadsheet has a few bells and whistles, such as compound annual growth rate (CAGR), which is difficult to do with pencil and paper, and it calculates the CAGR of the portfolio as a whole, including irregular deposits and withdrawals, which is pretty much impossible to do without a computer or specialized calculator. If you set up a pencil and paper version, you can make do with a series of yearly returns that you can compare to the S&P, but not an actual CAGR. We will go into the return functions on Thursday. For now, you can download the sample spreadsheet.
Now let's look at the spreadsheet: The first page tracks each individual stock. The column headings are as I listed them on Thursday, except that I've added one to make split-tracking easier. It automatically adjusts the number of shares you own when you enter a new split factor. (Split factor was explained Thursday.)
Then I've added:
Capital Gains Type: A formula that looks at the buy and sell dates and prints "Long" if the stock was held for more than a year, or "Short" if it was held for 365 days or less. I guess this qualifies as minimalist only because it is done for you -- I couldn't help it -- it's just so much fun! It also helps when filling out Schedule D which requires you to list long and short capital gains transactions separately.
Total Return: Your total return including dividends and capital gains from the sale of your stock. Example: If your stock doubled, no matter how long it took, this column will read 100%. This can get confusing because some accounting packages consider a 100% return to be your original investment, but I went for the more common usage. A 100% return means your investment went up 100%.
CAGR: This is your "annualized" return. If your stock was held for less than a year, the formula extrapolates what the return would have been if the same rate of growth had continued for a full year. (Careful, this can be very deceiving if the stock has only been held for a short time.) If your stock was held for more than one year, the formula gives you the CAGR, or Average Annual Return (AKA geometric return), which is comparable to most other published, multi-year investment returns for a similar period (i.e., you can use it to compare an investment to the S&P or to your portfolio as a whole as long as the time periods are the same).
A second page calculates the annualized return (CAGR or internal rate of return) for the portfolio as a whole, even if you have made deposits and withdrawals, which is no mean feat, but that's a whole 'nother story. Note, if the formula on this page isn't working, you will need to install the Excel Analysis Tool pack. (Go to the Add-Ins option under the Tools Menu. From the list that pops up there, check the box next to Analysis ToolPak. Then the XIRR function will be added to your list of functions, or use the Excel Help menu to find XIRR.)
Have fun, and keep those record-keeping and spreadsheet improvement suggestions coming.
Fool on and prosper!