Exponential

[Guest]

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??

 

[Rick Rothstein \(MVP - VB\)]

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

 

[joeu2004]

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.

 

[joeu2004]

Errata....

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!

 

[joeu2004]

Errata#2 (oiy!)....

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!

 

[Stan Brown]

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.)

 

[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.

Rick

 

[Stan Brown]

Sun, 28 Oct 2007 10:22:26 -0400 from Rick Rothstein (MVP - VB)

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.

 

[Rick Rothstein \(MVP - VB\)]

You can't. Mathematically it is undefined to raise a negative

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

 

[Stan Brown]

Sun, 28 Oct 2007 23:11:27 -0400 from Rick Rothstein (MVP - VB)

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!