What is IRR?
Before we define IRR, let's examine the BetterInvesting goal of 15%. We are told that a compound annualized return of approximately 15% over 5 years will double our money. Let's look at the math:
The formula is:
IRR = (FV / PV) ^ (1 / N) - 1
Where FV is Future Value,
PV is Present Value,
N is years.
^ means raised to
An example with real numbers is:
(200 / 100) ^ (1 / 5) - 1 =
2 ^ (1/5) - 1 =
1.1487 - 1 = 0.1487 (IRR = 14.87%).
Great! Now we see that a rate of return of 14.87% over 5 years will double our money. How do we apply this to a club with deposits and withdrawals (or any other account with a series of deposits and withdrawals)?
First the wrong way:
Summing the member deposits and withdrawals gave me $22,373.87 and the current value is 24,638.67 (as of 9/20)
(2/21/04 to 09/20/05) n is 1.58
IRR = (24638.67 / 22373.87) ^ (1 / 1.58) - 1
IRR = 0.0629 or 6.3%
But at the August 2005 meeting, I reported a return of 20%.
The formula: IRR = (FV / PV) ^ (1 / N) - 1 only applies to a single investment (no additional investments or withdrawals). IRR can be thought of as an interest rate that, if applied to each transaction during the time it's invested, produces the final figure. Put another way, pretend you had invested in a fixed-rate savings account. What compound yield would that account need to pay to produce the same ending value?
Now, it is time to let Excel handle the heavy lifting. If we enter all the deposits, withdrawals and dates, Excel can calculate the IRR. The function we use for this is XIRR (amounts, dates) in date order from oldest to most recent. A brief example using actual club data is below.
XIRR(B1:B7, A1:A7) where B1:B6 is the deposits/withdrawals and B7 is the ending value. And A1:A6 is the transaction dates and A7 is the ending date. The correct IRR for 9/20/2005 was 12.39%.
OK, how can we double check this? Remember we are looking for the compound interest rate we can apply to each transaction to produce the ending value. If we multiple each individual investment or withdrawal by (1 + IRR) ^ N, where N is the number of years from the date of that investment to the date of the current value, the sum of those results should be *very* close to the current value. We'll have as many different values for N as we have individual investments/withdrawals. Below is an example of the double check using the same data as above.
Date Amt N 12.39%
04/16/05 $1,490.00 0.4301 1,566.7647
05/21/05 $1,350.00 0.3342 1,403.7427
06/18/05 ($1,624.06) 0.2575 -1,673.6506
06/19/05 $1,300.00 0.2548 1,339.2668
07/16/05 $1,480.00 0.1808 1,511.5879
08/20/05 $1,490.00 0.0849 1,504.8534
N is the number of *years* between the date and 9/20. The last column is the amount * (1 + .1239) ^ N. The sum of all of these transactions is $24,638.67.
The same amount we used with the XIRR function to get the IRR of 12.39%. The figures match, as of 9/20 our IRR was 12.39%.
Recall at the August meeting, I reported a return of 20%. Remember prices do fluctuate. Also, the value of the club increased while the return decreased. How does this happen? Well, on 8/19, the total value of the club on 8/19 was $24,216.93. Then there was a deposit of $1,490.00 on 8/20 and the value of the club on 9/20 was $24,638.67. This is an increase in value of 1.7%. An increase in value does not reveal anything about performance.
Things to keep in mind:
(1) IRR is always both a "compound" and "annualized" return.
(2) Removed from the context of "over how long", return has no real meaning. "15% per year over 5 years" and "15% per year over 1 year "and "15% per year over 10 years" are three very different things. A "15%return" with no mention of time period doesn't mean anything useful to an investor. Neither "15% over 1 year" nor "15% over 10 years" implies that your money doubled over 5 years.
Learning Events Near You: