IRR Error Message

Things that aren't right, that are in the queue to be fixed... along with workarounds.
Xeno
Posts: 55
Joined: Sat Oct 25, 2008 4:15 pm

Re: IRR Error Message

Post by Xeno » Wed Nov 12, 2008 2:26 pm

To your test case, the 17Bii responds "Many/no solutions; key in guess; [STO] {IRR%}" while a real 12C says "Error 3". A fake 12C says 49 :).

I looked up the 12C manual and on p179 it explains that whenever it gives a positive answer, it is real. If it gives a negative answer then there may be other answers, while if it gives "Error 3" then the problem is too complex for calculation.

For the last case, on pp149-150 it gives a procedure for MIRR, or modified IRR. The process comprises the user providing discount and investment rates after which the IRR can be calculated for the actual flows. The steps are something like this:
Enter the negative cashflows, using zero as placeholders for positive ones. Enter your discount rate (e.g. 6%) into i and calculate NPV. Keep that number.
Enter the positive cashflows, using zero as placeholders for negative ones. Enter your assumed investment rate (e.g. 10%) and calculate NFV, which on a 12C involves first calculating NPV, changing the sign, entering it as PV then calculating FV in a normal TVM calculation across n periods.
Now take your two results and do a standard TVM calculation of i over n periods using your calculated PV and FV.

If I may be provocative and it passed Roy's review, then could you not program something like the following?
If not approaching a root, or simply if there are multiple sign changes, stop and ask the user for borrowing and investment rates.
Then calculate MIRR according to the above procedure (all of which is mechanical; you have the flows in there already).

Just a thought :)

Xeno
Posts: 55
Joined: Sat Oct 25, 2008 4:15 pm

Re: IRR Error Message

Post by Xeno » Fri Nov 14, 2008 12:17 am

Some thinking aloud:

Further to my comments above, I wondered why the 12C/17B could cope with Ray's problem which started this thread, despite the multiple sign changes and still (correctly) produce an error on Kim's simple cashflow example. Remember that the 12C/17B manuals both say that delivery of a positive answer without a prior error message means the answer is the only root, so it is possible to cope with certain cases of multiple sign changes, and they should not necessarily be discarded.

It happens that I have a root-finder which I wrote to deal with IRR on shares (stocks) which may be bought, sold and split in different quantities over many years, and which also deliver dividends. Therefore, there may be multiple sign changes (buys and sells). I wrote into this routine two checks, one of which looks for failure to converge in reasonable time and the other simply abandons the effort if the answer becomes "absurdly" large (e.g. > 10,000%). These checks will trap Kim's problem, and variations on it, successfully.

Based on the conversation here, I added a MIRR function so that I could deal better with problematic cases. The basic IRR function now checks for multiple sign changes and passes these problems to MIRR, after a warning. There, you have the option of entering your own pair of values as I described in the previous post, or simply letting the calculator rip in which case it uses the MIRR technique (dealing separately with negative and positive cashflows) but assuming the same rate for discount and investment values.

Within the convergence checks described, this will find a positive solution where one or more exist. Given there are likely to be multiple solutions, cross-checking shows that MIRR (in my implementation) tends to find the lowest positive value while IRR (in my implementation) tends to find the highest. Remember there may be others. Cashflows which are nett negative at a zero rate of return are always abandoned.

In Ray's original example, all methods give the 15.63% found by the HP calculators unless you differentiate and vary discount and investment rates. I am not sure it is sufficient to say this may be a wrong answer and to abandon the attempt, particularly since HP itself says in its written documentation that the answer is right.

Just some thoughts about different ways of dealing with the problem.

Xeno
Posts: 55
Joined: Sat Oct 25, 2008 4:15 pm

Re: IRR Error Message

Post by Xeno » Fri Nov 14, 2008 7:57 pm

I just downloaded the update, v1.1.0

On the impossible problem you presented earlier, it now locks up rather than giving an error message or prompting to enter a value. From your comment I was expecting it to give the error message we now see for Ray's problem, that multiple sign changes are not allowed.

RaySammartino
Posts: 11
Joined: Sat Aug 09, 2008 5:05 am

Re: IRR Error Message

Post by RaySammartino » Sat Nov 15, 2008 5:45 am

Wow....your last few posts were a bit difficult to follow. Suffice it to say that if I enter the original problem into a "real" 12c I get the correct answer (1st entry into CFo). In the v2.1 on the Iphone I get an error message which seems to be built in for our protection from possible erroneous answers. When I enter the first cash flow (in v2.1) in CFj I get the correct answer. That is the same answer as the real HP12c. Also I get the same answer in the real 12c when entering same cash flow into CFj. So It seems to ok and I suppose I will operate with this in mind. What do you think?

Xeno
Posts: 55
Joined: Sat Oct 25, 2008 4:15 pm

Re: IRR Error Message

Post by Xeno » Sat Nov 15, 2008 1:30 pm

I stand to be corrected but I think you are right, Ray. It should not matter if you leave CFo at zero and enter the real CFo in CFj, not varying Nj from 1. However, looking back over the discussion, you should be aware of the possibility of there being multiple answers so yours may not be "real". You might also hang the calculator occasionally, as I did with Kim's test problem.

Therefore, do not take entering CFo into CFj as a simple workaround but rather as a conscious decision to see whether you get an answer and what it is.

theDev
Posts: 240
Joined: Mon Aug 04, 2008 8:58 pm

Re: IRR Error Message

Post by theDev » Sat Nov 15, 2008 3:52 pm

Xeno wrote:I just downloaded the update, v1.1.0

On the impossible problem you presented earlier, it now locks up rather than giving an error message or prompting to enter a value. From your comment I was expecting it to give the error message we now see for Ray's problem, that multiple sign changes are not allowed.
Oh lovely.

Something I need to look into... (software is SO particular!)

Thanks for pointing this out Xeno... the sign change checker was supposed to catch this but I've obviously hozed something.

Kim

Xeno
Posts: 55
Joined: Sat Oct 25, 2008 4:15 pm

Re: IRR Error Message

Post by Xeno » Sun Nov 16, 2008 3:41 pm

There are other web resources which explain the situation on sign changes and IRR in both mathematical and lay terms, but for the curious here is a good discussion of the problems with IRR, about which Kim is trying to warn. It has pictures :)
http://hadm.sph.sc.edu/COURSES/ECON/invest/invest.html

It does not touch on MIRR but uses NPV for comparison. That is superior but still carries IRR's poor assumption of a single rate for both cost of capital and investment return.

I have been re-thinking these issues and intend to abandon this thread and start a new one in the "suggestions" topic area.

Xeno
Posts: 55
Joined: Sat Oct 25, 2008 4:15 pm

Re: IRR Error Message

Post by Xeno » Wed Nov 26, 2008 4:12 pm

Sorry to revive this one but I have discovered something interesting. I am using Calc 12E v1.1.1

Posts above, and the calculator itself on two of the problems discussed above (including Ray Sammartino's original), declare that "only one sign change [is] allowed".

Not true.

Try this problem:
f FIN
10000 CHS g CFo
2000 g CFj
1000 CHS g CFj
13000 g CFj
f IRR

There are three sign changes in the above but not only does Calc 12E return 12.99% rather than an error message, it is correct (or at least very reasonable) to do so! FMRR using rates of 5.5% and 10% answers 12.60%

Further, 12E will even solve this one.
-100000
500000
-200000
100000
150000
Getting 362.98%, the other possible root being -147.31%. Here, FMRR with the above rates more sensibly answers 61.12%.

Change the signs of the two 100000 values and there is still no sign change error message but 12E eventually gives up declaring it "could not determine a unique result" (this being a sound thing to do).

A 17B behaves the same while a 12C does similarly but with less accuracy in the results.

So, I deduce that you do not apply a universal sign change checker but have some additional problem filter, yet that filter for "reasonable problems" may be slightly erratic. I would have expected the second of those problems to be rejected for IRR, although it is not essential given the result found should be sufficient warning that IRR is inappropriate. However, if the second of those problems is acceptable to calculate, then what is so wrong with Ray's problem which started this thread? The found answer on a 12C is very close to a reasonable answer for his problem.

Roy may have some comments to make, given I took these problems and some discussion from his paper :D

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest