Hi,
I'm struggling with an error on exponential computation and need an expert's
help.
One of my investment's holdings is minus. ^^;;

When I tried to annualized the holding period yield, a minus number, it
gives me an error sign #NUM!.

Here is my formula I use. For
((Beg. Value / End. Value)-1)^(1/number of days)
something like
-7.59 % ^ (1/# of days)

How do I get a right number in this case??

You will have this problem whenever the number of days is even... the even
root of a negative number is imaginary. I'm not sure what your calculation
is supposed to be doing, but you can force an answer by making the negative
number positive by using the absolute value function...

Abs((Beg. Value / End. Value)-1)^(1/number of days)

but, in my opinion, that **does** change the meaning of the equation's
answer.

Rick


"escapeman" wrote in message
...
Hi,
I'm struggling with an error on exponential computation and need an
expert's
help.
One of my investment's holdings is minus. ^^;;

When I tried to annualized the holding period yield, a minus number, it
gives me an error sign #NUM!.

Here is my formula I use. For
((Beg. Value / End. Value)-1)^(1/number of days)
something like
-7.59 % ^ (1/# of days)

How do I get a right number in this case??

On Oct 27, 2:13 pm, escapeman
wrote:
When I tried to annualized the holding period yield, a minus
number, it gives me an error sign #NUM!.

Here is my formula I use. For
((Beg. Value / End. Value)-1)^(1/number of days)
[....]
How do I get a right number in this case??

The correct formula is:

(begValue / endValue) ^ (1 / days) - 1

Notice that the -1 is at the end.

But that formula does annualize. Instead, it returns the daily rate
of return. The annualize rate of return is (approximately):

(begValue / endValue) ^ (365 / days) - 1

Caveat: That is not how everyone annualizes the total return of an
investment. But many people do it that way.

Errata....

On Oct 27, 3:37 pm, joeu2004 I wrote:
But that formula does annualize. Instead, it returns the daily rate
of return. The annualize rate of return is (approximately):

Should read....

But that formula does __not__ annualize.

Klunk!

Errata#2 (oiy!)....

On Oct 27, 3:37 pm, I wrote:
Here is my formula I use. For
((Beg. Value / End. Value)-1)^(1/number of days)
[...]
The correct formula is:
(begValue / endValue) ^ (1 / days) - 1

The __correct__ formula is:

(endValue / begValue) ^ (1 / days) - 1

(And again: that should be 365/days, not 1/days.)

I had simply taken your formula and corrected the position of "-1". I
did not notice that you reversed the endValue and begValue as well.

Double-klunk!

Sat, 27 Oct 2007 14:13:01 -0700 from escapeman
:

-7.59 % ^ (1/# of days)

How do I get a right number in this case??

You can't. Mathematically it is undefined to raise a negative number
to a decimal power. (Well, it *is* defined, but the result is not a
real number -- it will involve the square root of minus one.)

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

You can't. Mathematically it is undefined to raise a negative
number to a decimal power.\

For roots (that is, powers that are 1 divided by an integer), you can't for
even roots (1/2, 1/4, etc.) only... odd roots (1/3, 1/5, etc.) are fine. For
example, the cube root of -8, which is written (-8)^1/3, is -2.

Rick

Sun, 28 Oct 2007 10:22:26 -0400 from Rick Rothstein (MVP - VB)
:
You can't. Mathematically it is undefined to raise a negative
number to a decimal power.\

For roots (that is, powers that are 1 divided by an integer), you can't for
even roots (1/2, 1/4, etc.) only... odd roots (1/3, 1/5, etc.) are fine. For
example, the cube root of -8, which is written (-8)^1/3, is -2.

Yes, but if I am not mistaken Excel uses floating point for its
arithmetic, not fractions.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

You can't. Mathematically it is undefined to raise a negative
number to a decimal power.\

For roots (that is, powers that are 1 divided by an integer), you can't
for
even roots (1/2, 1/4, etc.) only... odd roots (1/3, 1/5, etc.) are fine.
For
example, the cube root of -8, which is written (-8)^1/3, is -2.

Yes, but if I am not mistaken Excel uses floating point for its
arithmetic, not fractions.

If you enter this...

=(-8)^(1/3)

in a cell, it will equate to -2, as it should.

Rick

Sun, 28 Oct 2007 23:11:27 -0400 from Rick Rothstein (MVP - VB)
:
You can't. Mathematically it is undefined to raise a negative
number to a decimal power.\

For roots (that is, powers that are 1 divided by an integer),
you can't for even roots (1/2, 1/4, etc.) only... odd roots
(1/3, 1/5, etc.) are fine. For example, the cube root of -8,
which is written (-8)^1/3, is -2.

Yes, but if I am not mistaken Excel uses floating point for its
arithmetic, not fractions.

If you enter this...
=(-8)^(1/3)
in a cell, it will equate to -2, as it should.

You're right, and I was wrong. Thanks for the correction!

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/