having trouble with the IRR function - please help

Posted by Kevin Skinner on September 12, 2001 11:45 AM

I have the following series of cash flows in my spreadsheet:

A1 (1,751,786)
A2 283,663
A3 188,030
A4 225,458
A5 250,611
A6 266,903
A7 266,377
A8 271,533
A9 261,660
A10 275,729
A11 294,513
A12 313,775
A13 331,976
A14 353,625
A15 373,598

Each cell represents one year. I am trying to use the following formula to figure the internal rate of return at the end of each year (except for the first year):

example - year 1 (A1) to year 2 (A2)
IRR(A1:A2)
example - year 1 (A1) to year 4 (A4)
IRR(A1:A4)

Each year I add the new year's cash flows to the formula to come up with a new IRR for the year-end. However, for the first few years, the formula is returning the #NUM! error message, but from year 4 on, the formula is calculating correctly. What is wrong that it will not compute IRR on the first few years of this cash flow? Am I missing something? Help would be greatly appreciated - thanks in advance.

Kevin

Posted by Juan Pablo on September 12, 2001 12:55 PM

The problem is that Excel, after 20 attemps, hasn't found the right answer, so you have to help him a little bit. The IRR has a second, optional, parameter called estimate. Put -80% in it, and you'll be ok. I got this:

-84%, -58%, -36%, -21%, -11%, -5%, 0%, 3%, 6%, 7%, 9%, 10%, 11% and 12%.

Juan Pablo

Posted by Kevin on September 13, 2001 8:43 AM

Thanks for the help