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

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?

• Why not just take the ending portfolio value divided by the windfall amount? Why does it matter that some lots are ahead and some behind? Feb 4 at 1:50
• You can do lot-based accounting, or you can do total position based accounting, it really depends on you. Although lot-based accounting does give you more data points and more flexibility.
– CQM
Feb 4 at 2:46
• @DStanley I am not sure if it really matters, as such, but I am curious to see how the DCA strategy works comparing lots Vs had I invested a lump sum. It is all about learning for me... Feb 4 at 5:48

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

• Thanks, very useful. Let's see what else is offered before I accept ;o) Feb 4 at 5:49