I’ve had a portfolio of investments for about a year and a half, and I’ve now got enough data points that keeping track of everything is becoming cumbersome. It’s a basket of about 6 different ETFs. I’ve entered monthly values into Excel, based on my monthly statements, in the following fashion.
In my first worksheet, one column contains the monthly values of my portfolio. Another contains the corresponding value of the S&P 500 index. Two more columns normalize the dollar values to the price at the end of the first month.
In another worksheet, these normalized values are plotted.
Another worksheet is similar to the first, but each year has its own row (i.e. 12 columns per row). At the end of each row, the final value is compared to the initial value as a percentage, indicating the percent gain (or loss) for that year.
I also want to keep a record of exactly what is in my portfolio and when it’s there. In the past year and a half, I’ve rebalanced the portfolio once, and I’m also unsure of the best way to track transactions. Perhaps I could list all stocks in a column and indicate transactions in columns, but I think that before long it would be hard to tell what what I’ve currently got and what I had one year ago. Another possibility is to have one per year (since I plan to rebalance once per year) indicating my holdings at the beginning of the year, and then comparing two worksheets side-by-side to see the difference. But it seems like there should be a more efficient way to monitor that.
To recap, here’s what I’m interested in tracking:
[ol]
[li]Portfolio value[/li][li]Portfolio value vs. S&P 500[/li][li]Yearly portfolio performance[/li][li]Yearly Portfolio Holdings[/li][li]Portfolio Transactions[/li][/ol]
Thanks for offering your insight!
You might want to invest in a program specifically designed for tracking investments. (Excel is great, but it is a general purpose tool.)
At the low end, Quicken allows you to set up investment portfolios and download pricing information. Some institutions also allow transaction downloading and importing into Quicken. I am not sure whether it allows for comparisons vs. an index, though. Also, make sure you know which version you are purchasing and that it contains the features you need.
At the high end, well, I am not rich enough to be familiar with such tools. 
Happy investing!
I would recommend Quicken - We use Quicken Deluxe but Quicken Premier offers more investment-targeted tools. It gives us a pretty accurate snapshot of all our accounts at any given time in the past (you just change the “as of” date on the list of investments). It also does some asset-class reporting, you can report on annual rate of return, etc. I know I couldn’t keep track of anything if we didn’t have this set up.
It does do downloads from most of the major brokerage places - we have money through Fidelity and now Vanguard - so any transactions will automatically show up.
There’s also a free online version of it, but it’s not supposed to be as full-featured.
This–I love mint.com. Great site to keep track of ALL your assets and debts. You have to be comfortable with online stuff, but if you are, it is a great tool. You don’t have to keep track of anything except setting up budgets (and they even suggest them based on your spending habits). You can tie into your investment account only if you want, or all your accounts. It keeps track of whatever you allow it access to. It is up to date everytime you look at it as it is tied directly into your brokerage account and automatically updates each time you log in. It can tell you to the penny what your net worth is daily if you wanted/needed that info. You can keep track of all your debts/investments/checking/savings/credit cards/property, etc. Cool site and free too!