software

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

)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%quot;Dick in SLquot; wrote:

gt; Using Excel 2003. I am getting results that does not seem correct. I am
gt; entering the beginning fund balance with a negative sign in cell A2 and the
gt; increase in fund values each year in cells A3 to A5. Dates are all 12/31/xx
gt; using DATE in cells B2 to B5.
gt;
gt; Values are -8580, 2004, 619, 464 and the result is 46.82%
gt;
gt; Using CAGR calculation in Investopedia.com I get 10.78%
gt;
gt; What am I doing wrong in ExceL?
gt;
gt; Thanks

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.

quot;bpeltzerquot; wrote:

gt; )You didn't indcate what formula you used that generated the 46.82%.
gt; To calculate a CAGR, I would take ((end_value/start_value ^ (1 / #_periods))
gt; - 1. In this case ((11667/8580)^(1/3))-1 = 10.7875%
gt;
gt;
gt; quot;Dick in SLquot; wrote:
gt;
gt; gt; Using Excel 2003. I am getting results that does not seem correct. I am
gt; gt; entering the beginning fund balance with a negative sign in cell A2 and the
gt; gt; increase in fund values each year in cells A3 to A5. Dates are all 12/31/xx
gt; gt; using DATE in cells B2 to B5.
gt; gt;
gt; gt; Values are -8580, 2004, 619, 464 and the result is 46.82%
gt; gt;
gt; gt; Using CAGR calculation in Investopedia.com I get 10.78%
gt; gt;
gt; gt; What am I doing wrong in ExceL?
gt; gt;
gt; gt; Thanks

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

quot;Dick in SLquot; wrote:

gt; In my subject line I stated that I was using XIRR, which is supposed to
gt; calculate CAGR. I am not good at math and don't know how to do it as you
gt; suggest (the ^(1/3) ) part of the calculation.
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt; gt; )You didn't indcate what formula you used that generated the 46.82%.
gt; gt; To calculate a CAGR, I would take ((end_value/start_value ^ (1 / #_periods))
gt; gt; - 1. In this case ((11667/8580)^(1/3))-1 = 10.7875%
gt; gt;
gt; gt;
gt; gt; quot;Dick in SLquot; wrote:
gt; gt;
gt; gt; gt; Using Excel 2003. I am getting results that does not seem correct. I am
gt; gt; gt; entering the beginning fund balance with a negative sign in cell A2 and the
gt; gt; gt; increase in fund values each year in cells A3 to A5. Dates are all 12/31/xx
gt; gt; gt; using DATE in cells B2 to B5.
gt; gt; gt;
gt; gt; gt; Values are -8580, 2004, 619, 464 and the result is 46.82%
gt; gt; gt;
gt; gt; gt; Using CAGR calculation in Investopedia.com I get 10.78%
gt; gt; gt;
gt; gt; gt; What am I doing wrong in ExceL?
gt; gt; gt;
gt; gt; gt; Thanks

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

quot;bpeltzerquot; wrote:

gt; Sorry, I missed the XIRR. But you wouldn't consider the yearly changes in
gt; value unless you were taking the capital gain off the table each year;
gt; that's what would make it a cash flow! If you want to use XIRR to calculate
gt; CAGR, and you haven't made additional investments nor taken money out, you
gt; would just use the starting value / date and the ending value / date. So
gt; -8580 and 11667 for the values and 12/31/02 and 12/31/05. XIRR then returns
gt; 10.777%.
gt; As for the other way, in Excel, the formula would be
gt; =((11667/8580)^(1/3))-1. Excel can calulate the power (or root in this case)
gt; for you.
gt; --Bruce
gt;
gt; quot;Dick in SLquot; wrote:
gt;
gt; gt; In my subject line I stated that I was using XIRR, which is supposed to
gt; gt; calculate CAGR. I am not good at math and don't know how to do it as you
gt; gt; suggest (the ^(1/3) ) part of the calculation.
gt; gt;
gt; gt; quot;bpeltzerquot; wrote:
gt; gt;
gt; gt; gt; )You didn't indcate what formula you used that generated the 46.82%.
gt; gt; gt; To calculate a CAGR, I would take ((end_value/start_value ^ (1 / #_periods))
gt; gt; gt; - 1. In this case ((11667/8580)^(1/3))-1 = 10.7875%
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Dick in SLquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Using Excel 2003. I am getting results that does not seem correct. I am
gt; gt; gt; gt; entering the beginning fund balance with a negative sign in cell A2 and the
gt; gt; gt; gt; increase in fund values each year in cells A3 to A5. Dates are all 12/31/xx
gt; gt; gt; gt; using DATE in cells B2 to B5.
gt; gt; gt; gt;
gt; gt; gt; gt; Values are -8580, 2004, 619, 464 and the result is 46.82%
gt; gt; gt; gt;
gt; gt; gt; gt; Using CAGR calculation in Investopedia.com I get 10.78%
gt; gt; gt; gt;
gt; gt; gt; gt; What am I doing wrong in ExceL?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks

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.

quot;Dick in SLquot; wrote:

gt; Thanks for your help. I don't think that the Microsoft Office Assistance
gt; quot;Calculate a compound annual growth rate (CAGR)quot; is really clear regarding
gt; the calculations that I wanted to do.
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt; gt; Sorry, I missed the XIRR. But you wouldn't consider the yearly changes in
gt; gt; value unless you were taking the capital gain off the table each year;
gt; gt; that's what would make it a cash flow! If you want to use XIRR to calculate
gt; gt; CAGR, and you haven't made additional investments nor taken money out, you
gt; gt; would just use the starting value / date and the ending value / date. So
gt; gt; -8580 and 11667 for the values and 12/31/02 and 12/31/05. XIRR then returns
gt; gt; 10.777%.
gt; gt; As for the other way, in Excel, the formula would be
gt; gt; =((11667/8580)^(1/3))-1. Excel can calulate the power (or root in this case)
gt; gt; for you.
gt; gt; --Bruce
gt; gt;
gt; gt; quot;Dick in SLquot; wrote:
gt; gt;
gt; gt; gt; In my subject line I stated that I was using XIRR, which is supposed to
gt; gt; gt; calculate CAGR. I am not good at math and don't know how to do it as you
gt; gt; gt; suggest (the ^(1/3) ) part of the calculation.
gt; gt; gt;
gt; gt; gt; quot;bpeltzerquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; )You didn't indcate what formula you used that generated the 46.82%.
gt; gt; gt; gt; To calculate a CAGR, I would take ((end_value/start_value ^ (1 / #_periods))
gt; gt; gt; gt; - 1. In this case ((11667/8580)^(1/3))-1 = 10.7875%
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dick in SLquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Using Excel 2003. I am getting results that does not seem correct. I am
gt; gt; gt; gt; gt; entering the beginning fund balance with a negative sign in cell A2 and the
gt; gt; gt; gt; gt; increase in fund values each year in cells A3 to A5. Dates are all 12/31/xx
gt; gt; gt; gt; gt; using DATE in cells B2 to B5.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Values are -8580, 2004, 619, 464 and the result is 46.82%
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Using CAGR calculation in Investopedia.com I get 10.78%
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; What am I doing wrong in ExceL?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks

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

quot;bpeltzerquot; wrote:

gt; Perhaps not. The easiest built-in function would be rate, as in
gt; =RATE(3,0,-8580,11667). By setting the PMT to 0, we're effectively just
gt; letting the initial investment grow so we get the CAGR.
gt;
gt; quot;Dick in SLquot; wrote:
gt;
gt; gt; Thanks for your help. I don't think that the Microsoft Office Assistance
gt; gt; quot;Calculate a compound annual growth rate (CAGR)quot; is really clear regarding
gt; gt; the calculations that I wanted to do.
gt; gt;
gt; gt; quot;bpeltzerquot; wrote:
gt; gt;
gt; gt; gt; Sorry, I missed the XIRR. But you wouldn't consider the yearly changes in
gt; gt; gt; value unless you were taking the capital gain off the table each year;
gt; gt; gt; that's what would make it a cash flow! If you want to use XIRR to calculate
gt; gt; gt; CAGR, and you haven't made additional investments nor taken money out, you
gt; gt; gt; would just use the starting value / date and the ending value / date. So
gt; gt; gt; -8580 and 11667 for the values and 12/31/02 and 12/31/05. XIRR then returns
gt; gt; gt; 10.777%.
gt; gt; gt; As for the other way, in Excel, the formula would be
gt; gt; gt; =((11667/8580)^(1/3))-1. Excel can calulate the power (or root in this case)
gt; gt; gt; for you.
gt; gt; gt; --Bruce
gt; gt; gt;
gt; gt; gt; quot;Dick in SLquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; In my subject line I stated that I was using XIRR, which is supposed to
gt; gt; gt; gt; calculate CAGR. I am not good at math and don't know how to do it as you
gt; gt; gt; gt; suggest (the ^(1/3) ) part of the calculation.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;bpeltzerquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; )You didn't indcate what formula you used that generated the 46.82%.
gt; gt; gt; gt; gt; To calculate a CAGR, I would take ((end_value/start_value ^ (1 / #_periods))
gt; gt; gt; gt; gt; - 1. In this case ((11667/8580)^(1/3))-1 = 10.7875%
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Dick in SLquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Using Excel 2003. I am getting results that does not seem correct. I am
gt; gt; gt; gt; gt; gt; entering the beginning fund balance with a negative sign in cell A2 and the
gt; gt; gt; gt; gt; gt; increase in fund values each year in cells A3 to A5. Dates are all 12/31/xx
gt; gt; gt; gt; gt; gt; using DATE in cells B2 to B5.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Values are -8580, 2004, 619, 464 and the result is 46.82%
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Using CAGR calculation in Investopedia.com I get 10.78%
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; What am I doing wrong in ExceL?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks