On Monday, August 26, 2013 7:02:12 PM UTC-4, joeu2004 wrote:
"Ron Rosenfeld" wrote: So you invested $1,000; and then you made further investments of $644.89; $338.33 and $87.34. Or a total investment of a bit more than $2070. It would seem your loss is 100%. Ron, you are wasting your time. The OP is a troll. His primary purpose in posing such questions is to ultimately demonstrate the "superiority" of his tadXL add-in functions. I just ignore him. [1] Anyone with a modicum of math knowledge can look at the NPV formula and see that if all the cash flows have the same sign, their sum cannot be zero for valid IRRs greater than -1% (i.e. more positive). The OP allows for IRRs less than -1%. As I have explained to him many times, they are invalid IRRs because they flip the sign of the cash flows for odd-numbered cash-flow periods. Only then can the sum of the cash flows sum to zero. But in practical terms, it is incorrect to flip the sign of some discounted cash flows. An undiscounted inflow cannot turn into a discounted outflow, and vice versa. But the OP constinues to insist that less-than-negative-1% is valid because it can be derived algebraically. ----- [1] FYI, the OP goes by the names Abraham A (on the tadXL website) and "Financial Engineer" (in the MS Answers Communuity, for example). The latter is a misrepresentation. He does not have an FE degree of any sort, and he does not work as a professional FE, according to self-described information on the now-defunct tadXL website, IIRC. Not that an FE degree or FE experience is required to speak intelligently about these financial concepts. I'm just saying that he is not a "Financial Engineer" in any professional sense.


With all due respect to your knowledge of math and programming Excel functions, I would still insist Excel can do a better job in a given number of cases where it currently lacks the functionality.

IRR solution may be viewed as a SET rather than a single result, to do so we can use set notation to illustrate the problem and it's solution set.

f(x) = npv(x)
g(x) = nfv(x)
h(x) = bcr(x)

Before defining a solution set to IRR with set notation let us define an auxiliary set for complex numbers

C = {x: x is a complex number}

Now we define IRR solution set as

IRR = { x: x ˆˆ C, f(x)=0, g(x)=0, h(x)=1 }

This solution set states that IRR is the set of complex numbers where net present value is zero, and net future value is zero and benefit to cost ratio is one.

This defintion of IRR suggests that all solutions of IRR are complex numbers yet we are accustomed to seeing only real numbers as IRR values in programs such as Excel.

This too in part is a correct, since all real numbers in itself are complex numbers with an imaginary part that is zero.

Such as 2.57 is a real number but it is also a complex number such as 2.57 + 0i

The problem that I mentioned in my original post has three solutions albeit it two of these are complex and only one real solution as listed below:

-1.13783117952610221 + 0.46642298766194373i
-1.13783117952610221 - 0.46642298766194373i
-1.3692276409477956 + 0i

In set notation the IRR solution set for this problem is as follows

IRR = { -1.13783117952610221 + 0.46642298766194373i , -1.13783117952610221 - 0.46642298766194373i, -1.3692276409477956 }

In terms of percentange we will multiply each by a 100 to get the IRR as a percentage rate.

But as I said earlier Excel and other spreadsheet programs along with financial calculators seek only a single real solution to IRR out of the complete solution set.

And this problem was my exercise in developing tadXL v3.0 the upcoming version of tadXL add-in where new functions will allow for solution of far more complex financial problems.