Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dick in SL
 
Posts: n/a
Default How to calculate CAGR for mutual fund performance using XIRR

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default How to calculate CAGR for mutual fund performance using XIRR

)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%


"Dick in SL" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dick in SL
 
Posts: n/a
Default How to calculate CAGR for mutual fund performance using XIRR

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.

"bpeltzer" wrote:

)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%


"Dick in SL" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default How to calculate CAGR for mutual fund performance using XIRR

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

"Dick in SL" wrote:

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.

"bpeltzer" wrote:

)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%


"Dick in SL" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dick in SL
 
Posts: n/a
Default How to calculate CAGR for mutual fund performance using XIRR

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.

"bpeltzer" wrote:

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

"Dick in SL" wrote:

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.

"bpeltzer" wrote:

)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%


"Dick in SL" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default How to calculate CAGR for mutual fund performance using XIRR

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.

"Dick in SL" wrote:

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.

"bpeltzer" wrote:

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

"Dick in SL" wrote:

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.

"bpeltzer" wrote:

)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%


"Dick in SL" wrote:

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dick in SL
 
Posts: n/a
Default How to calculate CAGR for mutual fund performance using XIRR

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

"bpeltzer" wrote:

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.

"Dick in SL" wrote:

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.

"bpeltzer" wrote:

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

"Dick in SL" wrote:

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.

"bpeltzer" wrote:

)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%


"Dick in SL" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do you calculate CAGR In Excel 2003 with no initial cost? Boney Excel Worksheet Functions 1 September 15th 05 09:39 PM
Calculate the CAGR Bruce Excel Worksheet Functions 6 June 7th 05 02:17 PM
how do i calculate CAGR for a series of years of revenues? NuttinButFun2 Excel Worksheet Functions 1 April 18th 05 10:08 PM
How do I calculate CAGR when there are periodic additional investm Roger Phillips Excel Worksheet Functions 3 March 1st 05 03:10 AM


All times are GMT +1. The time now is 10:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"