motivated by e-mail from Dean A.
>A picture is worth a thousand ...
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).
If the cash flows are A1, A2, ... AN which are invested for times
T1, T2, ... TN, then
f(R) = A1(1+R)T1 +
A3(1+R)T3 + ...
+ AN(1+R)TN - 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!)
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 ...
Then again, we might adopt the simple-minded philosophy that "seeing is believing" or maybe
"a picture is worth a thousand
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 ...
Error in return estimate
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.
>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.
The example in Figure 2 (with three possible returns), has the following cash flows:
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!
|4 years ago||$10,000|
|3 years ago||-$10,000|
|2 years ago||-$10,000|
|1 year ago||$10,000|
The pertinent equation is:|
f(R) = 10,000(1+R)4 -
- 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".
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?
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.|
Besides, suppose the bank "interest" were, say 10%.
>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:
annualized return bank interest?
There's no zero value for the error ... no real solution to 10(1+R)2 - 30(1+R) + 25 = 0.
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.
Note that we're taking as our "error": f(R) = A1(1+R)T1 +
A3(1+R)T3 + ...
+ AN(1+R)TN - P
>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.
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.
Note, too, that f(0) = A1 + A2 + A3+ ... + AN - P.
Suppose this is negative ... as in Figure 6.
Further, if T1 > 0 is the largest time period, then f(R) +∞ as R +∞
provided A1 is positive ... as in Figure 6.
Then we'd expect a positive return with a positive slope, eh?
See also Net Present Value.
motivated by comments by Hymas
Portfolio Returns ... with additional information !
Okay, suppose we had additional information about the machinations of our portfolio. Then we can ...
>You mean besides the Cash Flows and Dates and final portfolio balance?
Yes. Suppose we knew the portfolio balances at the time of each Cash Flow.
For example, let's reconsider that last example shown in Figure 7.
Suppose the portfolio balances were like so, immediately before and after the Cash Flows:
What'd happen to $1.00 invested in the same stock?
|Date||Cash Flow||Balance before Cash Flow||Balance after Cash Flow||Gain for the period
|March 1, 2000||$1,000||$0||$1,000||N/A
|March 1, 2001||-$5,000||$6000||$1,000||6000/1000 = 6.00
|March 1, 2002||$8,000||$900||$8,900||900/1000 = 0.90
|March 1, 2003||-$4,000||$7,000||$3,000||7000/8900 = 0.79
|March 1, 2004||$0||$4,000||$4,000||4000/3000 = 1.33
It'd end up, after 4 years, as: $1.00 (6.00)(0.90)(0.79)(1.33) = $5.67
... and that's an annual gain of 5.671/4 = 1.543
... which is an "annualized return" of 0.543 or 54.3%.
>But the Cash Flows are the same, right?
Yes, but we now have additional information so the problem of multiple XIRR returns vanishes.
>But the return will depend upon the portfolio balances at the time of each Cash Flow, right?
>What about poor ol' XIRR and that magic equation and ...?
If you don't have the additional information, go with XIRR ... and pray.
>So, does that ritual have a name? I mean, multiplying the period gains and ...
Yes. Time-weighted Returns