Question about rate of return using XIRR

[SLH]

Hi, I have been following this forum for a long time and learned a tremendous amount.

Recently, I calculated the rate of return of my portfolio using the XIRR function in Excel. Specifically, I calculated the returns for every single year (2007, 2008, 2009, 2010, and 2011) and also over the entire time period (2007 – 2011). Here are the results that I obtained:

Code: Select all

2007   6.57%
2008   -43.02%
2009   40.46%
2010   19.27%
2011   -5.59%
   
2007-2011   3.79%


Then, I also calculated the geometric mean of the five annual returns and I attained the following result:

Code: Select all

Geometric Mean    -0.81%

I was a bit surprised because I was expecting that the geometric mean (-0.81%) would be similar to the XIRR calculation (3.79%) over the entire time period (2007 – 2011).

Any help would be greatly appreciated. Thanks.

[House Blend]

Makes perfect sense. (But I would also check whether the XIRR figure is annualized or not. I don't use Excel, so I don't know what conventions it follows.)

One way to think of it is that the dollars you added during the second half of 2008 and the first half of 2009 earned A LOT more than the dollars you added before or after. Knowing the IRR from year to year doesn't account for the relative amounts of money added during each of those five years. And apparently, you had enough of those 2008/2009 dollars to juice the returns.

Computing XIRR amounts to finding a single rate of interest such that if all of your dollars were earning interest at that one rate from the moment they were invested, you would have exactly the same amount of money that you actually have now.

(This is assuming for simplicity that you are an accumulator.)

[rr2]

Hi, I have been following this forum for a long time and learned a tremendous amount.

Recently, I calculated the rate of return of my portfolio using the XIRR function in Excel. Specifically, I calculated the returns for every single year (2007, 2008, 2009, 2010, and 2011) and also over the entire time period (2007 – 2011). Here are the results that I obtained:

Code: Select all

2007   6.57%
2008   -43.02%
2009   40.46%
2010   19.27%
2011   -5.59%
   
2007-2011   3.79%


Then, I also calculated the geometric mean of the five annual returns and I attained the following result:

Code: Select all

Geometric Mean    -0.81%

I was a bit surprised because I was expecting that the geometric mean (-0.81%) would be similar to the XIRR calculation (3.79%) over the entire time period (2007 – 2011).

Any help would be greatly appreciated. Thanks.

Were you adding in money during this time?

[SLH]

Thank you for your replies. I greatly appreciate it.

Just to clarify a few points:
- Yes, I am in the accumulator phase and have been adding money during this time period (2007 – 2011).
- The XIRR figures are annualized returns.

I am still a bit confused about the overall returns using the two different methods. First, by utilizing the XIRR method to calculate the portfolio return from 2007 to 2011 I obtain an annualized value of 3.79%. My understanding is that this calculation accounts for the external cash flows.

On the other hand, when I can calculate the returns for every single year using the XIRR function I get the following numbers, which also account for the external cash flows.

Code: Select all

2007   6.57%
2008   -43.02%
2009   40.46%
2010   19.27%
2011   -5.59%

Now, in order to calculate the average return (annualized) for these five yearly returns I take the geometric mean and obtain a return of -0.81%.

I still don’t understand why the two values from these two methods (3.79% vs. -0.81%) are not equivalent. Which value provides the correct return for the overall time period (2007 – 2011)?

[sscritic]

I have $100. It doubles in one year to $200. My return is 100%. The next year the $200 drops to $100. My return is -50%. My CAGR is 0 (my $100 became $100 after two years).

I have $100. It doubles in one year to $200. My return is 100%. I add $2000 at the beginning of year two. My $2200 drops to $1100 the next year. My return is -50%. My CAGR is 0 (100% with -50% still gives 0). But I have turned $2100 of investments into $1100, a loss of $1000. What is the meaning of a CAGR of 0 when I have lost almost half my money? CAGR makes sense when there are no ins and outs. That's not what you have.

[Doc7]

Think of it this way.

If you had 100 dollars invested on Jan 1 08 and it dropped to 50 dollars by Dec 31 your annual return is -50%

Add 50 dollars (otal 100) and it rises to 200 by Dec 31 09, for an annual return of 100% in 09.

Your XIRR is about 19%, not 25%, the mean. Adding funds makes a big impact on XIRR as different years have different weights due to increasing investments or withdrawals.

Also - if you don't invest anything, and gain 10% in one year (100 -> 110) and then lose ten percent, you end with 99 dollars for a negative rate of return. Not the mean return of 0%.

[camontgo]

I still don’t understand why the two values from these two methods (3.79% vs. -0.81%) are not equivalent. Which value provides the correct return for the overall time period (2007 – 2011)?

The XIRR function calculates a money-weighted return. So, periods were you had more dollars invested are weighted more heavily in the calculation that periods where you had fewer dollars invested.

The geometric mean is weighting all years equally regardless of the number of dollars invested...even though you used the XIRR money-weighted calculation within each year.

The difference you are seeing is probably because you added money since the very bad year of 2008, so the more recent years with better returns have more weight in full sample XIRR calculation than when you do the geometric average of the individual years.

Time-weighted returns and money-weighted returns measure different things, so the "correct" answer depends on what you are trying to measure.

I previously wrote up some spreadsheet based examples comparing time-weighted and money-weighted returns here: http://www.calculatinginvestor.com/2011 ... t-returns/

[Oicuryy]

See this page from Gummy on the difference between dollar-weighted rate of return and time-weighted rate of return. They produce different numbers.
http://www.financialwebring.org/gummy-s ... nd-you.htm

You have applied the formula for time-weighted rate to a series of dollar-weighted rates. The result is a number with no meaning.

Here is Gummy's page on how to calculate time-weighted rate of return when there are cash flows.
http://www.financialwebring.org/gummy-s ... eturns.htm

Ron

[SLH]

Thank you very much for the replies. The explanations and links are extremely helpful.

[DR]

I don't believe you have to then separately calculate the geometric mean on that series of annualized returns. You simply run the XIRR function over the entire set of years at once--and this will give you the geometric mean or annualized return for the entire period.

[dbr]

I don't believe you have to then separately calculate the geometric mean on that series of annualized returns. You simply run the XIRR function over the entire set of years at once--and this will give you the geometric mean or annualized return for the entire period.

Right. Certainly in Excel the XIRR function reports the annualized (geometric) return. This is true both for time periods of many years or time periods of less than a year (which can produce unusual annual results as short term fluctuations seldom continue for whole years at a time).