motivated by discussions on the Financial Webring Forum
Every once in a while there's a discussion about how to calculate your Return on Investments, sometimes called ROI, or sometimes people use ROR (Rate of Return) or ...
>And some say IRR, eh?
Uh ... yes, and that's what we want to talk about: IRR and Excel's XIRR
... and other assorted methods for calculating your annual return.
If you begin with some portfolio and neither invest new monies nor withdraw monies, the calculation is easy. See, for example this.
Ah, but if you're putting money into your portfolio and/or withdrawing to pay for a case of beer, it ain't easy ... and that's where IRR and XIRR come in.
First, some commonly accepted definitions:
XIRR% is the answer to the question:
>But don't some use IRR even if the deposits or withdrawals are NOT at regular intervals?
"What constant, annual, bank-like interest am I getting, considering various deposits and withdrawals at arbitrary times?"
IRR% is the answer to the question:
"What constant, bank-like interest am I getting, considering periodic deposits and withdrawals?"
XIRR(cash flows, dates), because it knows the dates of each deposit/withdrawal, gives an annual return, regardless of the time period or timing of the deposits/withdrawals.
IRR(cash flows), because it does NOT use the dates of each deposit/withdrawal, gives a return per period.
If the periodic deposits/withdrawals are made every 30 days, then IRR gives the 30-day return.
You might do (1+IRR)^(365/30) - 1 to get the 365-day return.
Ay, and there's the rub.
I'm quite sure that IRR was meant to be for regularly timed cash flows
(like every month or every year) but some software now calculates IRR with irregularly timed cash flows ... just like XIRR does.
In Quicken, for example, their IRR is the same as Excel's XIRR.
In the Open Office spreadsheets, they say:
I might also point out that, since the ritual for finding the Return (whether it's IRR or XIRR) is mathematically verbose, the software often needs some help.
For that reason, you may have to provide a "guess", like 10%. In other words, the software is likely to calculate:
XIRR(cash flows, dates, guess) and IRR(cash flows, guess).
If you omit the "guess", the software is likely to assume it's 10% ... which could get you into trouble if your actual annual return was, say, -50%. (That is, a lousy guess.)
There are other ways in which you may calculate an annual return. For example, if you'd like to know how well a particular stock or mutual fund is doing, you ignore any cash flows and just consider
some initial investment, like $10K, and see what the buy-and-hold portfolio value is by year's end.
See this example where the annual return for the fund is quite different from your annual return.
Indeed, a buy-and-hold mutual fund return (without cash flows) is a measure of a fund manager's performance ... not yours!
Sometimes, an "annual return" may be quoted which does consider your cash flows into and out of your portfolio each month.
A return is calculated for each month and the twelve monthly returns, R1, R2 etc. are "linked" to give the annual Return like so:
1 + Return = (1+R1)(1+R2)(1+R3)...(1+R12)
This may (or may not!) agree with either IRR or XIRR ... especially if one of the months has a -100% return making (1+R) = 0.
Example: if you begin on Jan 1, 2007 with $1K and invest another $1K every 30 days
(Jan 31, Mar 2 etc., the last being on Nov 27/07), ending with $13K on Dec31/07, then:
 XIRR% = 15.6% and IRR% = 1.225%.
The annual "linked" return would be 0% if, for example, R1% = -100%.
The IRR percentage is the 30-day return. Annually, you'd write: (1.01225)(365/30) - 1 = 0.160 or 16.0%.
>Aren't they supposed to be the same? After all, every 30 days sounds like regular cash flows to me!
Uh ... yes, it does doesn't it.
If x = 1 + (30-day Return), then x should satisfy:
 x12.17 + x11.17 + x10.17 + ... + x1.17 = 13
... where $money$ is measured in kilobucks.
Note that the first $1kilobuck is invested for 12.17 30-day periods, the second for 11.17 30-day periods etc. etc.
The first $kilobuck is invested for 365 days and that's 365/30 = 12.17 30-day periods.
That last $kilobuck is in from Nov 27 to Dec 31 and that's 34 days and that's a fraction 34/30 = 1.17 of a 30-day period.
>So what's the solution to that equation?
30-day Return = 0.0120 or 1.20% and that'd give an annual return of 1.012365/30 - 1 = 0.156 or 15.6%.
>But isn't that the XIRR return?
Yes, of course.
Now, for the IRR return, we'd assume 12 cash flows, equally spaced in time, the last being a withdrawal of -13 $kilobucks which reduces our portfolio to $0.
The pertinent equation we'd have to solve is:
 x12 + x11 + x10 + ... + x - 13 = 0.
Note that (when calculating IRR) the exponents are always integers.
>That's pretty close to equation  isn't it?
Close, but no cigar. The IRR solution to  is ... can you guess?
>Are you kidding?
It's 1.225% as we've noted in , above.
>And that's a 30-day return, right?
Yes. You can change it to a 365-day return by ...
>Don't tell me! It's ... uh ... uh ...
It's 16.0% as we noted above.
There's other stuff here:
|Newton || an impossible-to-understand tutorial on the equation that must be solved to get returns.
|What return? || returns and returns and returns ...
|Average & Annualized || don 't get 'em mixed up.
|Multiple roots || a warning that, with lots of positive & negative cash flows, there can be multiple solutions to that equation &$%#@!?
|YTD || then there's Year-to-Date returns.
|Dietz || then there are approximate methods
|Reordering || what happens when your returns occur in a different order?
|XIRR bug || a warning that software is not infallible.
Here's a XIRR vs IRR spreadsheet to play with ... just click on the picture:
Note: The spreadsheet works in Open Office, too
Note, too, that IRR just looks at the cash flows, not the dates.
The number of intervals between cash flows
(14 in the example shown) determines the degree of the magic polynomial equation.
If the time span is one year, then (1+IRR)# of periods = IRRannualized.