I have a simple spreadsheet where I track the balance in my investment account. What should I do to handle new deposits in the account?
- End of Day 1: balance = $100
- End of Day 2: $120 (daily gain 20%)
- End of Day 3: $150 (daily gain 25%)
So after Day 3 I have made 50%. Let's say that in between Day 3 and Day 4, I deposit $100, so at the beginning of Day 4, the balance is $250.
If I compute the return as above, all of a sudden it drops to 25% ($250 balance, $200 in), which is clearly wrong. And furthermore obviously the gain from Day 3 to Day 4 is not 66% ($250/$150), it's zero.
I'm pretty frequently depositing new money into my investment account, so what's the best way to account for this? Is the rate of return some weighted average based on how long the money was in the account?