How to calculate CAGR for mutual fund performance using XIRR

[Guest]

Using Excel 2003. I am getting results that does not seem correct. I am
entering the beginning fund balance with a negative sign in cell A2 and the
increase in fund values each year in cells A3 to A5. Dates are all 12/31/xx
using DATE in cells B2 to B5.

Values are -8580, 2004, 619, 464 and the result is 46.82%

Using CAGR calculation in Investopedia.com I get 10.78%

What am I doing wrong in ExceL?

Thanks

 

[Guest]

)You didn't indcate what formula you used that generated the 46.82%.
To calculate a CAGR, I would take ((end_value/start_value ^ (1 / #_periods))
- 1. In this case ((11667/8580)^(1/3))-1 = 10.7875%

 

[Guest]

In my subject line I stated that I was using XIRR, which is supposed to
calculate CAGR. I am not good at math and don't know how to do it as you
suggest (the ^(1/3) ) part of the calculation.

 

[Guest]

Sorry, I missed the XIRR. But you wouldn't consider the yearly changes in
value unless you were taking the capital gain off the table each year;
that's what would make it a cash flow! If you want to use XIRR to calculate
CAGR, and you haven't made additional investments nor taken money out, you
would just use the starting value / date and the ending value / date. So
-8580 and 11667 for the values and 12/31/02 and 12/31/05. XIRR then returns
10.777%.
As for the other way, in Excel, the formula would be
=((11667/8580)^(1/3))-1. Excel can calulate the power (or root in this case)
for you.
--Bruce

 

[Guest]

Thanks for your help. I don't think that the Microsoft Office Assistance
"Calculate a compound annual growth rate (CAGR)" is really clear regarding
the calculations that I wanted to do.

 

[Guest]

Perhaps not. The easiest built-in function would be rate, as in
=RATE(3,0,-8580,11667). By setting the PMT to 0, we're effectively just
letting the initial investment grow so we get the CAGR.

 

[Guest]

Finally got back to this. I was looking at XIRR again after reviewing your
comments and think that this is exactly what I wanted, I just did not know
how to make it work. My original post was just a simple example because I
could not understand the Microsoft instructions for computing CAGR. Now I
understand to use only the start value and end value. XIRR also works for
start and end dates that are not both exact annual dates (ie. 02/17/03 start
and 12/31/05 end). I also used it for an investment where I made monthly
contributions for 2 years and then none for 3 years, entering the
contributions with a minus (-), and this seemed to give me the correct CAGR.
Thanks again for your help.

Dick