Calculate correctly weighted-average

Question

So I’m adding money to my account in different times and in different amounts. I use this money to buy different short term financial instruments.

I’m calculating the weighted-average of the theoretical past date as if I added all money on a single day (basically sumproduct of date with corresponding amount, divided by sum of all deposits).

I then divide all profit by the sum of all deposits to get a percentage.

Now the problem. If I deposit today, the theoretical past date is indeed changed, but I expect to see the percentage unchanged, regardless of the amount, because its contribution should be zero. Instead I see it decreased.

What am I missing?

Answer 1

The methodology you use to calculate a rate of return for your short-term investments is a bit unconventional and probably misleading, but more importantly, you seem to be applying it incorrectly.

To fix it, sumproduct not the dates with amounts deposited but the number of days from today for each deposit date and then divide it by the total amount deposited. Then subtract that from today to get a weighted average starting date for all of your deposits combined.

If you use this version, a new deposit, however big, will not have any impact on the weighted average starting date of your portfolio when today is its deposit day.

In addition, I suggest you look into the concepts of the time weighted rate of return and the money weighted rate of return which present more typical and better methodologies to calculate the rate of returns for portfolios with interim cash flows.