Exponential

  • Thread starter Thread starter Guest
  • Start date Start date
G

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??
 
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
 
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??
Click to expand...

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

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

Should read....

But that formula does __not__ annualize.

Klunk!
 
Errata#2 (oiy!)....

Here is my formula I use. For
((Beg. Value / End. Value)-1)^(1/number of days)
Click to expand...
[...]
The correct formula is:
(begValue / endValue) ^ (1 / days) - 1
Click to expand...

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??
Click to expand...

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.)
 
You can't. Mathematically it is undefined to raise a negative
number to a decimal power.\
Click to expand...

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)
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.
Click to expand...

Yes, but if I am not mistaken Excel uses floating point for its
arithmetic, not fractions.
 
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.
Click to expand...

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)
If you enter this...
=(-8)^(1/3)
in a cell, it will equate to -2, as it should.
Click to expand...

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