1

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:

enter image description here

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:

enter image description here

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!

  • I need some more clarification to help you out. In the first chart, 67.77000 is the number of shares of an investment at a given time. -100.00 next to that number means what? After there was 67.77000 shares you deposited 100 shares into the account? withdrew 100 shares out of the account? What does it mean? – Alex B Mar 20 '15 at 21:36
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

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.