?? What Function Do I Need ??

[Alan Foxmore]

Hi everyone,

I don't have a financial background so pardon me if the answer is obvious.

I see Excel is loaded with financial functions and I'm not sure of the
correct one to use in my case.

For simplicity, let's say I have stock that I purchased on January 1 for
$1,000. Now, on June 30 (1/2 through the year) it's worth $1,100. The
stock's value increased by 10%. I would like to know the *annual* percentage
rate that I'm track to earn if the stock maintains its current rate of
growth.

Since my original $1,000 increased by 10% to $1,100 in six months, I see I
am on track to earn 10% every six months so that on December 31 the stock's
value will be $1,210 -- an annual return of 21%.

Can someone tell me the proper function to use?

Thanks.

 

[Bernd]

Hello,

Try

=INTRATE("1/1/2007","30/6/2007",1000,1100)

[Try your local date format, please]

Read Excel's help on INTRATE for more details.

Regards,
Bernd

 

[Alan Foxmore]

Hello,

Try

=INTRATE("1/1/2007","30/6/2007",1000,1100)

[Try your local date format, please]

It's not correct -- it returns 20.11%. The correct result is 21.0%.

 

[David Biddulph]

=(1+10%)^2-1
or
=POWER(1+10%,2)-1

 

[Alan Foxmore]

=(1+10%)^2-1
or
=POWER(1+10%,2)-1
--

Well, yea, I could do something like that but I was hoping there was a
built-in Excel function.

What if I look at my stock value on, say, August 13 instead of June 30? Or,
what if it had risen by 7.8292% instead of 10%? Your approach would require
a lot of additional programming. I'm looking for something more general and
standard.

 

[Bernd]

Hello Alan,

Please look at Excel's help for these formulas.

Another suggestion:

=RATE((enddate-startdate)/365,0,-1000,1100)

Regards,
Bernd

 

[Alan Foxmore]

Hello Alan,

Please look at Excel's help for these formulas.

Another suggestion:

=RATE((enddate-startdate)/365,0,-1000,1100)

I tried =RATE(0.5,0,-1000,1100)

and it returned 21%.

I'll need to research it more but thanks!

 

[Alan Foxmore]

Hello Alan,

Please look at Excel's help for these formulas.

Another suggestion:

=RATE((enddate-startdate)/365,0,-1000,1100)

That looked promising at first but for a stock that loses money it seems to
cause an error:

=RATE(0.5,0, -1000,500)

Causes the #NUM! error.

 

[Bernd]

Hi Alan,

Then take
=((1+(endvalue-startvalue)/startvalue)^(365/(enddate-
startdate))-1)*100

Regards,
Bernd