Excel's XIRR function is nice for calculating the annualized rate of return when there are various investments and withdrawals.
However, there could be a problem ... like so:

See the calculation in Figure 1? Excel's XIRR says the return is 0.0%, if you use as your "guess" 10%. However, the "correct" return, for the numbers in Figure 1, is -4.81% so Excel has it wrong. Now here's the interesting part: Change the initial investment from $200 to $200.00001 and guess what XIRR says? >I'd say -4.81 %, right? Right! In fact, if you play with various initial guesses you'd get that -4.81% when you guess a negative return ... but not when you guess positive returns. >So just guess negative!

Figure 1

But don't you see? Just a microscopic change in the initial investment changes the XIRR response. Doesn't that say something to you?

>Yeah! It says that you should guess a negative return!

Since XIRR tries to find the value of x where a certain f(x) = 0, let's look at a plot of f(x) vs x, in Figure 2. You can see that f(x) = 0 does NOT occur at x = 0, right? >So how did you find that -4.81% answer? I used Newton's Method which gives the answer in just a few iterations:

Figure 2

>So I should download that spreadsheet?
Actually, that's an old one. You might want to try this one ... it's slicker:

>So where's the -4.81%? Uh ... that's left as an exercise, for you. Just stick in the numbers from Figure 1. If'n you do it right you'll get Figure 3 See? It's -4.81% just like it should be. >You can read that -4.81% from the picture? NO! I downloaded the spreadsheet! ... just click on the picture of the spreadsheet.

Figure 3

>Well, I think it's pretty schtoopid to guess a return like 10% when your portfolio is worth less than you invested. It's clearly a negative return and ...
That's not my point. Don't you see? Excel should be able to find the correct return even with such a lousy guess. It ain't MY fault. It's Excel's fault.
Besides, XIRR gets a wrong answer with an initial investment of $200.00 but the right answer when that's changed to $200.00001. Ain't that a bug?
If you try (almost) any other software, such as OpenOffice , NeoOffice, Gnumeric or Google, they all get the right answer.

However, if you want to use Excel, you can always try (for the example in Figure 1):

=XIRR(C2:C8,B2:B8,0.1*SIGN(SUM(C2:C8)))

so your guess is 10% if the sum of cash flows is positive and -10% if the sum of cash flows is negative. ... assuming withdrawals are positive and deposits are negative.

I reckon one should always check for "reasonability" when staring at the answer XIRR gives. There may be more than one ... uh, reasonable answer:

Note that changing the current portfolio balance just shifts the curve up or down, like this.
See also: multiple returns

>Okay, but suppose I want to find the root of f(x) = 0 when it ain't a collection of cash flows?
Hmmm ... good idea!

Newton's Method

Click on the picture below to download the spreadsheet.

>That's a different spreadsheet, right?
Wrong! It's the same spreadsheet as the XIRR bug.

>What's them red lines?
That's Newton at work. Have you forgotten? He moves along tangent lines, closer and closer to the zero of f(x) with every iteration.
The red lines are just the first two in a series of such moves.

>Ain't that what XIRR is supposed to do?
Apparently ...

However, if'n you want just Newton without the financial stuff, you can try this guy: Newton2.xls

Here's more of that stuff from the Newton2 spreadsheet::

>In each case ... what's the guess?
Guess.