(Tracking DCA investments is no different than tracking 401(k) investments.)
The easy way (which works only if you invest the same amount every period, on about the same day every period) is to use Excel's =FV() function.
The more flexible method (if you don't contribute the same amount every period, at the same time every period) is to compute the series of future values yourself.
The way I do it (and the results match my plan provider's numbers) is with a spreadsheet having one row per purchase, plus three "separate" cells.
In three separate cells, put:
- the Starting Balance (might be $0),
- the Current Account Balance, and
- the hypothetical growth rate (which you adjust; start with a reasonable guess, you'll be changing it later).
The columns would be something like this:
Date ESP Purchase_Price FV
Where:
Date
is the day you purchased it.
ESP
is "Elapsed Time Since Purchase": how many years from Date
to Now. Use the =DATEIF() function, and divide by 365.
Purchase_Price
is what it says on the tin: how much money you spent purchasing the investments that period, and
FV
(Future Value) is how much that money "should be" worth given the growth rate specified in "Cell 3" above.
Sum up the FV
column at the bottom of that column.
Compare that FV
sum to the Current Account Balance. Adjust the hypothetical growth rate (Cell 3" until the sum of the FV values matches the Current Account Balance, et voilà now you know your investment's growth rate.