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:
"What constant, annual, banklike interest am I getting, considering various deposits and withdrawals at arbitrary times?"
IRR% is the answer to the question:
"What constant, banklike 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 30day return.
You might do (1+IRR)^(365/30)  1 to get the 365day return.
>But don't some use IRR even if the deposits or withdrawals are NOT at regular intervals?
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 buyandhold 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 buyandhold 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, R_{1}, R_{2} etc. are "linked" to give the annual Return like so:
1 + Return = (1+R_{1})(1+R_{2})(1+R_{3})...(1+R_{12})
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:
[0] XIRR% = 15.6% and IRR% = 1.225%.
The annual "linked" return would be 0% if, for example, R_{1}% = 100%.
The IRR percentage is the 30day 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 + (30day Return), then x should satisfy:
[1] x^{12.17} + x^{11.17} + x^{10.17} + ... + x^{1.17} = 13
... where $money$ is measured in kilobucks.
Note that the first $1kilobuck is invested for 12.17 30day periods, the second for 11.17 30day periods etc. etc.
>Huh?
The first $kilobuck is invested for 365 days and that's 365/30 = 12.17 30day 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 30day period.
>So what's the solution to that equation?
It's
30day Return = 0.0120 or 1.20% and that'd give an annual return of 1.012^{365/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:
[2] x^{12} + x^{11} + x^{10} + ... + x  13 = 0.
Note that (when calculating IRR) the exponents are always integers.
>That's pretty close to equation [1] isn't it?
Close, but no cigar. The IRR solution to [2] is ... can you guess?
>Are you kidding?
It's 1.225% as we've noted in [0], above.
>And that's a 30day return, right?
Yes. You can change it to a 365day return by ...
>Don't tell me! It's ... uh ... uh ...
It's 16.0% as we noted above.
Note:
There's other stuff here:
Newton  an impossibletounderstand 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 YeartoDate 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} = IRR_{annualized}.
