XIRR and YTD ... continuation of XIRR stuff
motivated by e-mail from Cassius M.

Here's an interesting and seemingly parodoxical thing.

>I love paradoxal things! I remember when ...
Pay attention.

Suppose we begin on Jan 1, 2003 with a portfolio worth \$10K.
A month later, on Feb 1, 2003 we withdraw \$9K and leave a balance of \$1500.
At that point (on Feb 1, 2003), we withdraw the \$1500 and stick it under our pillow and let it stay there, month after month.

>That's a 0% investment, right?
Right!
So what do you expect our annualized return to be after Feb 1 ... say on Mar 1 or Apr 1 or ...?

>I'd expect it it get smaller as the months go by because ...
Because our money is now under the pillow, at 0% return?
You're quite right ... and we can use the Excel XIRR(B2:B4,A2:A4) function to show that.
 The situation is illustrated in Figure 1 ... where cell C4 contains XIRR(B2:B4,A2:A4) We have an "annualized" return of 66.49% Figure 1
Okay, now we change the date in cell A4 to Jun 1, 2003 ... as in Figure 2.
 The situation is illustrated in Figure 2 ... where cell C4 contains XIRR(B2:B4,A2:A4) We have an "annualized" return of 45.76% Figure 2
>See? I told you it'd go down because ...
Next question:
How would our Year-To-Date return change?

>It'll go down because we're at 0% return after Feb 1.
Let's do them one at a time:
 From Jan 1 to Mar 1: Our annualized-XIRR-return is 66.49% as of Mar 1, 2003 (see Figure 1). That's a 365-day return. Over just 59 days , it'd be (1.6649)59/365 - 1 = 0.0859 or 8.59%. That's (1+C4)^(D4/365)-1, in cell E4. Figure 1A
 From Jan 1 to Jun 1: Our annualized-XIRR-return is 45.76% as of Jun 1, 2003 (see Figure 2). That's a 365-day return. Over 151 days (from Jan 1 to Jun 1), it'd be (1.4576)151/365 - 1 = 0.1687 or 16.87%. That's (1+C4)^(D4/365)-1, in cell E4. Figure 2A
>Huh?
And at year's end:

where, as before, cell E4 contains (1+C4)^(D4/365)-1.

>The YTD return actually increases? That seems ... uh ...
Remember what YTD means.
If the YTD return is 8.59% (over 59 days), it means that a single \$100 investment would grow to \$108.59 (in 59 days).
If the YTD return is 16.87%% (over 151 days), it means a single \$100 investment would grow to \$116.87 (in 151 days).
What it doesn't mean is that a series of investments and withdrawals will result in the YTD return.

>There's a moral here, right?
Yes.

>Yeah, but how do you explain ...?
 Okay, consider Figure 1A again. You put money into a bank that pays a constant 66.49% per year. You deposit \$10K on Jan 1, withdraw \$9K on Feb 1. At 66.49% per year you'd have \$1500 by Mar 1. That YTD return of 8.59% means that a single \$100 deposit on Jan 1, in the same bank, would give you \$108.59 by Mar 1. Figure 1A

 Now consider Figure 2A. You put money into a bank that pays a constant 45.76% per year. You deposit \$10K on Jan 1, withdraw \$9K on Feb 1. At 45.76% per year you'd have \$1500 by Jun 1. That YTD return of 16.87% means that a single \$100 deposit on Jan 1, in the same bank, would give you \$116.87 by Jun 1. Figure 2A
The bank interest is less, but you've keep your \$100 there longer

>The moral? If your YTD goes up, your money may be under the pillow.