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





--
KathyC
------------------------------------------------------------------------
KathyC's Profile: http://www.excelforum.com/member.php...o&userid=25124
View this thread: http://www.excelforum.com/showthread...hreadid=482400