On Fri, 4 Nov 2005 19:47:44 -0600, KathyC
wrote:


I am trying to calculate the compounded annual growth rates (CAGR) of
stocks on my spreadsheet. To do this, I am trying to input the
formula:

cagr = (current $ / buy price) ^(1/# of years) - 1

or, I suppose, to account for fractions of years:
cagr = (current $ / buy price) ^(1/(# of months / 12)) - 1

Since the buy date is different for each stock--some within the year
and some are several years old--I need a formula that will cover each
condition.

col5 = buy price
col9 = current $
col7 = buy date

So, to figure out the number of months, I've input:

=(YEAR(col7)-YEAR(TODAY())*12 + MONTH(col7)-MONTH(today())

=YEAR(R[-1]C)-YEAR(TODAY())*12+MONTH(R[-1]C)-MONTH(TODAY())


Excel tells me the forumla is not correct, so I accept the corrected
formula (which looks like mine) then gives a number like: -22048

Does anybody see something I"m missing? Or is there an easier way to
achieve this?

Once I get that part right, I'm guessing I'd make the whole formula:

= (col9 / col5) ^ (1/((date formula)/12) -1

Thanks,

KathyC




I think it is easier to use the XIRR function. But you have to either use a
VBA routine, or have your data in contiguous cells. Perhaps hidden in another
area of the worksheet.

=XIRR(Values,Dates)

If you must have the data in non-contiguous cells, you need the VBA function in
order to handle that.

Finally, you must have the analysis tool pak (a free add-in that comes with
Excel) installed. If you do not, look at HELP for XIRR and it'll tell you how
to install it.



--ron