1

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?

3
  • 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?
    – D Stanley
    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...
    – Steve
    Feb 4 at 5:48
2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.