=RATE(17,1,-5) returns #NUM! but the answer should be about 18.9

[Guest]

Is there a way to get this function to generate the correct answer?
Essentially, I am trying to get the IRR for a cash for of $1 per year for 17
years given a $5 investment. My calculator gives the answer but I want to
program into Excel.

 

[JE McGimpsey]

One way:

A
1 -5
2 1
3 1
....
18 1

20 =IRR(A1:A18) ===> 18.95%


Alternatively,

=IRR({-5,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})

 

[Fred Smith]

Another anomaly in the financial functions.

=Rate(17,-1,5) will give you the correct answer (18.95%), but
=Rate(17,1,-5) won't.

Giving Excel a decent guess solves the problem:
=Rate(17,-1,5,0,0,.2) and
=Rate(17,1,-5,0,0,.2) both work.

My HP12C will returns 18.95% in either case.

Amazing that Microsoft can't equal HP's 20-year old technology.

Hopefully they'll address this in the next release.