YEAR and MONTH confusion

[KathyC]

I am trying to calculate the compounded annual growth rates (CAGR) o
stocks on my spreadsheet. To do this, I am trying to input th
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 yea
and some are several years old--I need a formula that will cover eac
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 correcte
formula (which looks like mine) then gives a number like: -22048

Does anybody see something I"m missing? Or is there an easier way t
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


:confused

 

[Mike]

Kathy,

You're missing a closing ")" just before the "*12". As it is you're
multiplying only today's year by 12.

Also, if you want a positive number you'll need to subtract the buy
date from today's date, rather than the way you have it.

Mac

 

[Mike]

Kathy,

You're missing a closing ")" just before the "*12". As it is you're
multiplying only today's year by 12.

Also, if you want a positive number you'll need to subtract the buy
date from today's date, rather than the way you have it.

Mac

 

[KathyC]

Hi Mac

Oops! I should have taken that first paren out on the first statement.
It actually wasn't there on the last try. The 2nd statement is how I
had it:

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

But it does make sense to have an extra set of parens to be sure that
the 12 multiplies against the subtraction result, so:

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

Unfortunately that gives me a result of 0.00 when using the buy date
4/1/2002 and today=11/4/05.

I also threw extra parens around the last half, with the same 0.00
result:

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


Any other suggestions?

Thanks, Kathy

 

[Ron Rosenfeld]

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

 

[DOR]

1. Your R[-1]C references refer to the cell one row above in the same
column as your formula, when your earlier post implied that the buy
date was in the same row. If your buy date is immediately to the left
of the cell containing this formula, you should use RC[-1]. Maybe the
cell above contains a date in this month, which would give you the zero
result. Ensure that you are referring to the cell with the buy date
rather than to another cell. Otherwise, you will continue to get a
strange result. Can you use the default reference notation, A1, B1
etc.? You may find it easier to use.

2. You are still subtracting today's date from the (earlier and
therefore smaller) buy date which will give you a negative result, as
Mike pointed out. You should subtract the buy date from today's date
to get a positive result.

If you get the elapsed time correct, your basic growth rate formula
should give you a reasonably accurate result.

 

[Mike]

Hi Kathy,

Try this simpler formula:
=(100/65)^(1/((TODAY()-DATE(2002,4,1))/365))-1
My example assumes you bought the stock on 4/1/02 for $65 and sold it
today for $100. It should return a CAGR of 12.7%
You can subsitiute the prices and buy date with cell references.

 

[KathyC]

One and all,

Sorry for being such a doofus. Let's see....I was tired? I don't know
what the heck I'm doing? Pick an excuse!

Anyway, I appreciate your help. In the end I just wrote some VB...I'm
just not good with long formulas...I hate them; therefore, they hate
me!

For some reason, VB is much easier for me and the loop can handle all
different date ranges without me having to change anything within a
formula.

So thank you.....I learned to be more careful if nothing else!

Kathy