My 1st Million At 33 – yes, you can do it too

I believe very much in being true to myself and it’s no different in financial matters. A large part of it is to have an accurate and up-to-date picture of my portfolio performance. I have done a quantitative analysis for 2006; however, I think it’s the method rather than the actual numbers that are of interest to my readers. In the first part, I want to go over two topics: tracking performance using Excel, and how to calculate portfolio gains with contributions.

Portfolio Tracking with Excel
I imagine many of you use either Quicken or MS Money. My wife and I use MS Money and it’s an excellent tool for tracking spending. However, I find it lacking when it comes to the more in-depth portfolio analysis I want to perform. Of course, the data can be exported to Excel, but won’t it be easier to use Excel in the first place?

The task would be too daunting if one has to update the price for each stock or fund manually. Fortunately, Excel incorporates quotes from MSN. To do that, select Data:Import External Data:Import Data… and MSN MoneyCentral Investor Stock Quotes.

Select “Parameters” from the prompt and then “Use the following value”. Enter the symbols separated (e.g. “C, IBM”) by commas and “Ok”.

You should see a table like this (assuming you have a live internet connection):

The parameters can be edited later by right clicking the table. You can get the latest MSN quotes by “Refresh Data”. The “Parameter” field has a limited length, but there is no limit to the number of quote tables you can have. You can use the “Refresh All” button to update all tables at once.

Once you have the quotes, you can do the standard spreadsheet manipulations. One caveat in editing the list of symbols is not to changing the location of the cells you’re referencing to (if you’re a regular spreadsheet user you know what I mean).

Calculating returns with contributions
In personal finance (PF) blogosphere it is common to show the total net worth growth without separating savings from investment gains. While it makes a lot of sense from a PF perspective, it can mask poor returns with a high savings rate. The proper way is to calculate the so called time-weighted returns. The precise calculation is rather involved. Fortunately, there is an approximation called the Midpoint Dietz method: In a given month, define

S: portfolio starting value
E: portfolio ending value
C: total contribution (negative if a distribution)
G: $ gain for the month (negative if a loss)
R: return %

Then

G = E – S – C
R = G/(S + 0.5 C)

This approximation assumes that the contribution is made in the middle of the month which is usually true on average. It’s possible to apply the same formula to the full year, although the approximation becomes pretty rough. The more common approach is to compound the monthly returns:

R_year = (1+R₁)(1+R₂)…(1+R₁₂) – 1

where R₁ is the return for January, etc.

In part 2, I will discuss my actual returns for 2006, its standard deviation, Sharpe ratio, alpha, beta and more