How do I account for newly added money when computing my rate of return?

Question

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?

For example:

• 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?

Answer 1

A couple ideas:

Use excel - it has an IRR (internal rate of return) that can handle a table of inputs as you describe, along with dates deposited to give you a precise number.

Go simple - track total deposits over the year, assume half of that was present in January. So, for example, your account started the year with $10k, ended with $15k, but you deposited $4k over the year. It should be clear the return (gain) is $1k, right? But it's not 10%, as you added during the year. I'd divide $1k/$12k for an 8.3% return. Not knowing how your deposits were structured, the true number lies between the 10% and 6.7% as extremes. You'll find as you get older and have a higher balance, this fast method gaining accuracy, as your deposits are a tinier fraction of your account and likely spread out pretty smoothly over the year anyway.

Answer 2

Use TWIRR (aka TWRR). Time Weighted rate of return. It's sort of the opposite of XIRR. XIRR results change dramatically depending on the timing of the cashflows. It might be useful to also model returns that are unaffected by the timing. This is how funds report returns, and this number allows you to compare to funds and indices. During periods of steady deposits, XIRR will continually understate performance. And in retirement, when you have steady withdrawals, XIRR will overstate.

TWRR is talked about here: http://www.dailyvest.com/PRR/prr_calcmethods.aspx#twrr

I've made a simple spreadsheet that you can use as a starting point, if you like: http://moosiefinance.com/static/models/spreadsheets.html (top entry in the list)