Calculating a simply complicated return?

Question

I have been tracking investments in a spreadsheet for some time. Each row is an equal time period, the balance includes dividends for that time period, and the transfers in our out for that time period. I'm trying to calculate a total return.

(Please note the balance is simple and does not fluctuate with the market. The best way to views this is like an interest bearing account, not a dividend paying stock. In other words, the value of the principal asset is not fluctuating itself, only paying interest.)

Here's an example that seems to work:

The formula I am using (in Google Spreadsheet) is (A1-sum(B:B))/(sum(B:B)). This is (Current Balance - Investment)/Investment (investment being the sum of the deposits and withdrawals, or investments and divestments). In this example (3.12472848-1.96099384)/1.96099384 = 59.3%. That seems to work, I can buy that.

Here's where it gets complicated. I have an investment that has now paid out more than has been invested:

Again, (A1-sum(B:B))/(sum(B:B)), which in this example (67.77+39.79)/-39.79 = -270.3%. In this example the withdrawals/divestments have exceeded the deposits/investments.

This feels like a very dumb question with a very simple answer. I just seem to have a mental block at the moment and can't figure it out. Any help would be very appreciated, thanks!

Answer 1

Since you have the balance at equal periods and the cash flows at the period ends, the best return calculation in this case is the true time-weighted return.

See http://en.wikipedia.org/wiki/Time-weighted_return#Formulae

So, notwithstanding some ambiguity about your figures, here is a calculation using the first three periods from your second table.

1 + r = (3.0848 + 0.7186)/3.1247*
        (3.7824 - 0)/3.0848*
        (3.2473 - 1.3181)/3.7824 = 0.761227

∴ r = 0.761227 - 1 = -0.238773

Giving a total return over the three periods of -23.88%

If the periods are months, multiply by four to annualise.