: Calculating a simply complicated return? 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

Vote Up Vote Down   0 Reactions

Login to give Reaction!

Calculating a simply complicated return?
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!

(1)

Login to post a comment!