Compound annual growth rate (CAGR) using only percentages instead of values

[rbrady]

Hello,

Is it possible to calculate the compound annual growth rate (CAGR) for a period with only the returns by percentage instead of the actual values? For example, I have the quarterly returns for a three-year period, dates and percentages [comma-separated]:

Sep-04,Dec-04,Mar-05,Jun-05,Sep-05,Dec-05,Mar-06,Jun-06,Sep-06,Dec-06,Mar-07,Jun-07,Sep-07
1.00,9.90,-1.10,1.30,5.00,2.10,6.04,-1.40,3.00,6.10,2.40,4.25,2.62
​

The way I used to calculate the CAGR was to create arbitrary intermediate values: Say I start with a value of $100 (I think any value will work) for Jun-04, the Sep-04 value would be $101.00 ($100 + (1*0.0100)), and Dec-04 would be $111.00 ($101.00 + (1+0.0990)), and so on until Sep-07 is $149.23:

101.00,111.00,109.78,111.21,116.77,119.22,126.42,124.65,128.39,136.22,139.49,145.42,149.23
​

Then I would use the RATE function: =RATE(3, ,-101.00, 149.23), where "3" represents the three-year period, the blank is the omitted payments argument, "-101.00" is the negated present value [Aside: I don't understand the negating it, but it is necessary for it to work], and "149.23" is the future value. This formula yields a result of 13.90 %.

I have looked at the XIRR function, but that does not appear to meet my my needs. Because I only know the return by percent, is there a way to calculate this same CAGR (13.90 %) without creating the "placeholder" values?

Thank you very much!
Ryan


PS
Is there a better way to display sample data since a file can't be attached? I found if a line of "cells" from the post is copied and pasted into Excel and then, in 2007, go to

Data > Data Tools > Text to Columns > Comma delimited
​

then things worked OK (but tabs (if I used tabs) only worked if the line was copied from the HTML source).