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
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.62The 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.23Then 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!
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).