problems with RATE function returning #NUM

[jaedend]

I am using this function on each line of an investment portfolio. Here
is the funtion as entered:

=RATE((TODAY()-C3)/365,0,I3,-K3)

C3 = Investment Date
I3 = Original Investment ammount
K3 = Current Value

The problem I'm having is it works on some lines and other lines it
returns #NUM

Here are 2 examples, the first I get a return of 122.5% and the 2nd
gives me #NUM.

C3=7/19/05, I3=162.00, K3=165.23 (returns 122.5%)
C3=7/19/05. I3=162.00, K3=153.76 (returns #NUM)

I don't understand, please help.

 

[Niek Otten]

In your second example, the future value is smaller than the present value.
In the first example, interest is 2% in 10 days, 122.7% per year. Are you
sure your figures are right?

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[Ron Rosenfeld]

I am using this function on each line of an investment portfolio. Here
is the funtion as entered:

=RATE((TODAY()-C3)/365,0,I3,-K3)

C3 = Investment Date
I3 = Original Investment ammount
K3 = Current Value

The problem I'm having is it works on some lines and other lines it
returns #NUM

Here are 2 examples, the first I get a return of 122.5% and the 2nd
gives me #NUM.

C3=7/19/05, I3=162.00, K3=165.23 (returns 122.5%)
C3=7/19/05. I3=162.00, K3=153.76 (returns #NUM)

I don't understand, please help.

From HELP:

"RATE is calculated by iteration and can have zero or more solutions. If the
successive results of RATE do not converge to within 0.0000001 after 20
iterations, RATE returns the #NUM! error value."

In your first example, your answer is correct.

In your second example, run today, the result is -85% and you need a guess of
-60% to get a valid answer.

Both of these values appear to be correct.


--ron