Return(s)

Portfolio Return(s)

motivated by e-mail from Dean A.

When calculating your portfolio return, you might use Excel's XIRR function.
Alas, it's possible that there are 2-count-'em-two (or more!) possible solutions to the magic equation that defines the return(s).

>Magic equation??

If the cash flows are A₁, A₂, ... A_N which are invested for times T₁, T₂, ... T_N, then
f(R) = A₁(1+R)^T₁ + A₂(1+R)^T₂ + A₃(1+R)^T₃ + ... + A_N(1+R)^T_N - P = 0
where P is the current portfolio balance. That defines the annualized return(s), R.
The value of f(R) is the "error".
See, for example, this.
(Note that, if P = 0 and all Ts are positive, then R = -1 or -100% is a solution!)

>Yeah, so?
Well, we might investigate the conditions under which multiple solutions can exist, generating some sophisticated mathematical machinery which will give us the number of possible returns and ...

>Yeah, so?

Then again, we might adopt the simple-minded philosophy that "seeing is believing" or maybe "a picture is worth a thousand ~~words~~ equations" and look carefully at a picture of the error in the return estimate. If you see something like Figure 1, you're happy.

However, if you see something like Figure 2, you should worry.

>In Figure 2, which is the correct return?
All of 'em.
In fact, you'll need some other input to decide. For example, you may be able to discard solutions less than -100%.
However, according to "the math", all are possible returns.

>Yeah, but how do I get a picture of that error?
You can play with a spreadsheet that allows you to enter Cash Flows and Dates then type in some range of Returns and click a button and ...

Figure 1
Error in return estimate

Figure 2
Multiple returns

>A picture is worth a thousand ...
Okay! The spreadsheet looks like this:

Click on the picture to download the spreadsheet

You enter the Cash Flows with Dates and the current Balance and its Date, some range of Returns (from Min Return to Max Return) then click the Plot button to see if'n you got a single return.

An estimate of that return is given in cell K1 ... with the error value in cell K2.

>That's one huge error!
You can then reduce the range of Returns and click the button
and you'll get a closeup ... and better estimates, like Figure 3.

>What's the better estimate?
For this example, it's 5.19% with an error of -$1.

>Then what?
That's it!

>But what's that white dot at the origin ... in the spreadsheet?
Ah, yes. That's in case there are several "possible" returns (or zeroes of the "error"). See?
You can then close in on each possible return by changing the plot range.

Figure 3

Figure 4

Fictitious Return(s)

The example in Figure 2 (with three possible returns), has the following cash flows:

Time Amount
4 years ago $10,000
3 years ago -$10,000
2 years ago -$10,000
1 year ago $10,000
Now $1,000
The pertinent equation is:
f(R) = 10,000(1+R)⁴ - 10,000(1+R)³ - 10,000(1+R)² + 10,000(1+R) - 1,000 = 0
where R is the annualized rate of Return and there are three solutions, namely: -88.56% and -28.58% and +19.52%.
Note that the spreadsheet plots f(R) = the "error".
To be able to withdraw $10K after one year (starting with $10K), then withdraw another $10K in the second year, the returns for those first and second years must be ... uh, HUGE!

Then comes an investment of $10K and a final balance (after another year) of just $1K.
That means a loss that last year of at least 90%!!
(A 61.8% return for each of the first two years followed by a 90% loss the last year would do it
... but, of course, the "annualized" return is a single, constant return for all years!)

Although this scenario is unlikely ... I reckon it's possible, eh?

However, the annualized return which we're calculating is a fictitious return.
It's the constant interest a bank would give so that your final portfolio agrees with your current balance (after all the cash flow deposits / withdrawals have been made).

>You stick $10K in the bank, withdraw $10K on two successive years, deposit another $10K a year later and a year after that your bank balance is 1k?
Yes, and you then ask: "What bank interest did I get?"

>And that's the "annualized return", right?

Yes. Since we expect that your balance, hence f(R), would increase if the bank interest R increases, then we'd ignore the middle zero in Figure 4.

>We ignore any zero where the curve is decreasing, right?
Right!
Also, if the bank gave you an interest equal to the large negative value (namely -88.56%), then you could hardly withdraw $10K after one year, eh?
Note that "the math" isn't bothered by negative balances ... though the bank is!
So we'd discard that zero of f(R) as well.

We're then left with the annualized return of 19.52%.

>So, that's it?
Yeah, I think so ... 'cept we should note that, even with that 19.52% annualized return, our initial $10K wouldn't allow us to withdraw $20K over the next two years without our balance going negative.

>Huh? If we're talking the equivalent bank interest and a bank wouldn't let your balance go negative, then what's the annualized return ... for this example?
Did I mention that "annualized return" (or Compound Annual Growth Rate) is a fictitious return?
fictitious: adjective Conventionally or hypothetically assumed or accepted.

>What if negative bank balances means you've borrowed money from the bank?
Yeah, we could consider that. Indeed, if we start with a $10K bank balance, withdraw $30K a year later and a year later end up with a negative balance of, say, -$25K, then a plot of the "error" would look like Figure 5:
>What's the ~~annualized return~~ bank interest?
There's no zero value for the error ... no real solution to 10(1+R)² - 30(1+R) + 25 = 0.

Figure 5
Besides, suppose the bank "interest" were, say 10%.
Then, if you had a negative balance of, say, -$2000, the math would say you'd owe the bank $2000(1 + 0.1) = $2200 one year later.

>What's wrong with that?
Nothing. It makes "bank sense".
But suppose the bank interest were -10% (corresponding to a negative root of the error equation).
If your bank balance were $2K, then a year later it'd be $2000(1 - 0.1) = $1800. The bank is charging you for having a positive balance!

Now suppose you owed the bank $2K. That is, your balance was -$2K.
Then, one year later, you'd only owe the bank $2000(1 - 0.1) = $1800.
>Nice! What's the name of that bank?
The moral is that interpreting "annualized return" as "bank interest" requires ... uh, suspension of normal ideas of what banks do.
>So?
Did I mention that "annualized return" is a fictitious return?
Just pray that the cash flows, dates and final portfolio value give an error curve like Figure 6
... with a single root.

Figure 6
Note that we're taking as our "error": f(R) = A₁(1+R)^T₁ + A₂(1+R)^T₂ + A₃(1+R)^T₃ + ... + A_N(1+R)^T_N - P
Note, too, that f(0) = A₁ + A₂ + A₃+ ... + A_N - P. Suppose this is negative ... as in Figure 6.
Further, if T₁ > 0 is the largest time period, then f(R) +∞ as R +∞ provided A₁ is positive ... as in Figure 6.
Then we'd expect a positive return with a positive slope, eh?
>And that's guaranteed, right?
You kidding? There's this scenario:

It gives Figure 7, with two returns having a positive slope.
>Which is the "correct" answer?
Beats me !!

Figure 7