0

Given an investment portfolio and a ledger of withdrawls and deposits, I'd like to derive a performance metric that would allow me to compare its performance of a stock index.

A randomly generated example:

Period Balance Cashflow
0 5000.00
1 5028.27
2 5109.40 50
3 5065.93
4 4773.27 -250
5 4775.29
6 7151.80 2400
7 7179.44
8 7527.66 300
9 7488.77
10 7519.12

That is, we had 5000 at start, 7488.77 at the end and in total of 2500 added to the portfolio.

The advice I've found so far is to calculate per-period returns (discounting for the cashflow), then compound them and look at the final number. More specifically:

Calculate per_period returns as (Balance[i] - Balance[i-1] - Cashflow[i]) / Balance[i]:

Period Balance Cashflow Ret_period
0 5000.00
1 5028.27 0.57%
2 5109.40 50 0.62%
3 5065.93 -0.85%
4 4773.27 -250 -0.84%
5 4775.29 0.04%
6 7151.80 2400 -0.49%
7 7179.44 0.39%
8 7527.66 300 0.67%
9 7488.77 -0.52%
10 7519.12 0.41%

From that, calculate total return for the end of each period as (1 + Ret_period[0]) * (1 + Ret_period[1]) * ... * (1 + Ret_period[i]) - 1:

Period Balance Cashflow Ret_period Ret_total
0 5000.00
1 5028.27 0.57% 0.57%
2 5109.40 50 0.62% 1.19%
3 5065.93 -0.85% 0.33%
4 4773.27 -250 -0.84% -0.52%
5 4775.29 0.04% -0.48%
6 7151.80 2400 -0.49% -0.97%
7 7179.44 0.39% -0.58%
8 7527.66 300 0.67% 0.09%
9 7488.77 -0.52% -0.43%
10 7519.12 0.41% -0.03%

... to arrive at -0.03%.

The approach and the formulas make sense, but we arrive at the number that is negative. This makes little sense as we started at 5000, then added 2500 and arrived at 7519. That is, excluding deposits, we are still 19 in green.

Calculated differently, the rate of growth can be (7519 - 5000 - 2500) / 5000 = 0.38%, which makes a bit more sense.

I am clearly missing something, but what is it?

As I mentioned, investments are of the stock type, so the end goal is to have a metric directly comparable to the market indexes.

Angstrom
  • 101
  • 1

3 Answers3

0

The simplified solution does give your effective rate of cumulative return for the period.

It doesn't give rate of return for the portfolio, which is what rate the money actually in it grew at.

The latter is the number you would want to compare to the index... unless you want to compute what the index would have done each year with these same additions and subtractions, which I'm not convinced is a really useful way to compare them; it puts too much weight on specific years.

Open a spreadsheet and play with it a bit, factoring out the cashflow, and I think you'll find that return on the original 5000 actually was slightly negative. The exact timing of your cashflows shifted which years contributed how much, giving you the very slight positive outcome. CAVEAT: I distrust my quickie spreadsheet enough that I'm not willing to share it; it would be good if someone repeated the analysis.

Note that no matter how you slice them, these would be miserable returns for an investment. Admittedly we recently had a few miserable years for the entire market (correcting after a few years where things were doing unreasonably well, I think), so that has to be taken in context... Which is why you're comparing against the index for the same time period.

keshlam
  • 52,634
  • 6
  • 87
  • 177
0

Upon reading up a bit more, the closest answer would be Modified Dietz method, which is defined as "a measure of (...) performance of an investment portfolio in the presence of external flows." It is a linear approximation of internal rate of return (IRR), which is a more generic method.

Angstrom
  • 101
  • 1
0

so the end goal is to have a metric directly comparable to the market indexes.

There are two common ways to measure performance, and differ on what you're trying to actually measure.

If you just want to measure the performance of what you're buying within the portfolio, then your method (called Money Weighted Return) is appropriate. It removes the biases from time periods where you have more money invested.

If instead you want to measure the performance of your overall investment decisions (not just what to invest in but when), then an IRR-based approach (XIRR in Excel is a good tool for this) is more appropriate.

IRR tells you at what fixed rate you could have invested your money to end up with the same balance in the end. So it would be a good measure if you wanted to get an equivalent fixed rate or return, but not, say, a stock index where the return is not fixed.

What you're seeing in your data is the balance during the positive return periods are, on average, higher then the balances during the negative return periods, so your portfolio benefits more when the balances were higher, resulting in an overall positive return. But when you cancel out the timing of cashflows, the performance of what you invested in was negative. So you got lucky by having more money in your account during times when returns were higher.

Calculated differently, the rate of growth can be (7519 - 5000 - 2500) / 5000 = 0.38%, which makes a bit more sense.

That's a fine measure but can also be biased by when you added and removed money.

D Stanley
  • 145,656
  • 20
  • 333
  • 404