I am having trouble with an excel problem. I have to use the IRR formula for 4 cells in a row but for some reason I keep getting an error(NUM!).

I am using =IRR(C27:G27) for a formula. Each number is $116,000.00 Do I need to change the numbers to General instead of Currency or do I need some negative numbers.

Please Help!!!

  • Show your data and the formula you attempted. What is in what cells? On what cell do you get the error? If you want answer you should ask good questions. – Jan Doggen Sep 3 '13 at 6:34

For the =irr function to work properly, there must be at least one positive value and one negative.


Think of it this way; you cannot get a return on an investment unless you have both made an investment (a negative value) and received a return (a positive value).

Clearly then the series of cash flows must have both at least one positive and at leas one negative value in order for an IRR to exist and be calculable.


Internal Rate of Return calculates the discount rate for an original investment to equal zero NPV over the term. The actual formula is:

enter image description here

If there is no negative number as the initial investment, no combination of positive cash flows will be able to equal zero. If you don't have an initial investment, your rate of return isn't a real number.

As stated above, be sure the first cell in the range has a negative number representing the amount of the investment:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.