How do I track my investment value when using Dollar Cost Averaging strategy

Question

I am in the process of investing a windfall using Dollar Cost Averaging strategy.

I want to plot my portfolio value in a spreadsheet, but if I simply chart the current price, it doesn't reflect the fact that I bought at different prices and that some units might be ahead on the purchase price and some currently behind.

Is there a formula or method that takes the range of purchase prices into account when calculating current position?

Answer 1

(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:

1. the Starting Balance (might be $0),
2. the Current Account Balance, and
3. 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.