IRR function in Excel 2010 returns #NUM! error for an investment withall negative cash flows

[Michael Marshall]

My investment has an initial cash outflow of $1000 followed by series of losses on this investment thus all remaining cash flows are negative.

I was interested in knowing my loss in terms of internal rate of return for this investment so I tried using Excel IRR function with my data listed below but I was given a #NUM! error in Excel 2010 using the IRR function.

My cash flows are as follows

-1000
-644.89
-338.33
-87.34

I did try an alternative IRR function that is part of tadXL Excel add-in which is no longer available online and it reported a correct IRR for this investment.

So what makes tadXL report an IRR for all negative cash flows and Excel IRR function is unable to find the internal rate of investment where investment makes no money and there are all losses.

 

[Ron Rosenfeld]

My investment has an initial cash outflow of $1000 followed by series of losses on this investment thus all remaining cash flows are negative.

I was interested in knowing my loss in terms of internal rate of return for this investment so I tried using Excel IRR function with my data listed below but I was given a #NUM! error in Excel 2010 using the IRR function.

My cash flows are as follows

-1000
-644.89
-338.33
-87.34

I did try an alternative IRR function that is part of tadXL Excel add-in which is no longer available online and it reported a correct IRR for this investment.

So what makes tadXL report an IRR for all negative cash flows and Excel IRR function is unable to find the internal rate of investment where investment makes no money and there are all losses.

From Help on IRR: Values must contain at least one positive value and one negative value to calculate the internal rate of return.

Your series contains all negative values.

In addition, I'm not sure your loss as a percentage is meaningful.. Your cash flow series is interpreted as showing multiple cash flows out, and nothing remaining at the end.

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%.

 

[joeu2004]

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.

 

[Ron Rosenfeld]

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.

Thanks for that information. I did not realize he was the FE :-(

 

[joeu2004]

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.

"Obviously", I meant -100%, not -1%. I'm sure it is not "obvious" to the
OP, though.

 

[Michael Marshall]

"Ron Rosenfeld" <[email protected]> wrote: > So you invested $1,000; and then you made further investments > of $644.89; $338.33 and $87.34. Or a totalinvestment 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 ofthe 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 complexnumbers 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 albeitit 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 morecomplex financial problems.

 

[Michael Marshall]

My investment has an initial cash outflow of $1000 followed by series of losses on this investment thus all remaining cash flows are negative. I wasinterested in knowing my loss in terms of internal rate of return for thisinvestment so I tried using Excel IRR function with my data listed below but I was given a #NUM! error in Excel 2010 using the IRR function. My cash flows are as follows -1000 -644.89 -338.33 -87.34 I did try an alternative IRR function that is part of tadXL Excel add-in which is no longer available online and it reported a correct IRR for this investment. So what makes tadXL report an IRR for all negative cash flows and Excel IRR function is unable to find the internal rate of investment where investment makes no money and there are all losses.

If you are wondering how did I come about the cash flows I posted in my original post in the first place

Then I must say that the cash flows I listed earlier as

-1000
-644.89
-338.33
-87.34

weren't the ones I had in the first place.

These cash flows were the left overs of the original cash flows for which Idesired to find the complete IRR solution set

And here were the original cash flows listed below where I had an investment of $1000 as cash outflow followed by four cash inflows of $500, $400, $300 and $100

-1000
500
400
300
100

The first IRR solution turns out to be 14.49% ( you can confirm this using Excel IRR function as follows )

=IRR( {-1000, 500, 400, 300, 100} )

Once the first IRR solution was found, I got left with the following cash flows which I had listed in the opening of my message

-1000
-644.89
-338.33
-87.34

For these cash flows Excel IRR returned #NUM! errror and I had to resort tousing tadIRR function to find the second IRR which turns out to be -136.92%

Once the second IRR solution was found using tadIRR, I was left over with the following cash flows

-1000
-275.66
-236.55

And anyone who has little knowledge of algebra would know how to solve for the remaining two IRR values using a formula that results in the following two remaining IRR values

-113.78 + 46.64i %
-113.78 - 46.64i %

In conclusion, using my method and tadXL software I was able to find the complete IRR solution to the following cash flows

-1000
500
400
300
100

as

IRR = { 14.49%, -136.92%, -113.78 + 46.64i %, -113.78 - 46.64i % }

So @JoeU

Do you still question my authority on the subject matter for which I call myself a FinancialEngineer ?

 

[joeu2004]

So @JoeU
Do you still question my authority on the subject matter for
which I call myself a FinancialEngineer ?

ROTFLMAO!

 

[pjthefinancialengineer]

So @JoeU

Mike! you remind me of those self-proclaimed prophets of doom and gloom.

However Mike, yours is a peculiar case as it were others who figured out that there was something special about you.

Mike!, Didn't they deny others who were before you and mocked them as well.

 

[abethefinancialengineer]

[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.

@joeu2004

Even the former claim (Abraham A) too sounds bit like a misrepresentation.

@Mike

You remember, Casey the young business analyst from Huron Consulting in Chicago.

At that time Casey had purchased the older version of tadXL v1.0 that had only 43 financial functions as compared to 95 financial functions in tadXL v2.0

And none of the options of tadXL v2.0 were there in older version of tadXL v1.0

You remember those 5 different messages that Casey left at your site that day, in which he sounded so excited and offered suggestions about mid-year discounting option for investment analysis functions. According to Casey, professional business analysts make use of mid-year discounting rather than the full year discounting.

And IIRC, that night your domain name email server was down so you sent Casey a reply from your personal Gmail address whose user id contained your "real" name and you had invited Casey to join your LinkedIn network.

And you know what happened next Mike!. Casey had a look at your LinkedIn page that had your actual photo and your "real" name

And Casey didn't bother to reply to your email, or did he. Neither did Casey bothered to join your LinkedIn network.

But as I understand Casey still uses your software tadXL v1.0 on a daily basis and on each occasion when tadXL in installed as an add-in a pop up Window in Excel states the copyright notice along with name of the Author as Abraham A.

But I don't suppose that someone like you who is an architect and enforcer of Sequel to Apartheid would be crying racism. Right, Mike!

But then, as Sam Donaldson of ABC News commented on the weekly roundup showcalled "This Week" with David Brinkley in 1990 about the real reason why David Duke lost the Louisiana Gubernatorial election.

If I remember correctly, Sam said

"It wasn't the message, it was the messenger"

Here Sam was alluding to Mr Duke's ties to the Klan

Same can be said of your stint as a politician back in 1992

Mike! your message was "Right" but you weren't the "Right" messenger

A white color would have made a difference rather than your tasteful chocolate looks.

But Mike, your site tadXL is now defunct as joeu2004 puts it. But it wouldn't have to be this way had you accepted that order of 100,000 licenses for tadXL v1.0 from the Chinese value added reseller. Think about it Mike, the $4 million dollars in gross sales would have lasted your next few generations without them holding a job. So why did you refuse such a large sum of money. I think you told me why you did so as your mentor Rush had one time told you that Chinese dollars aren't good money. So now you paid the price Mike, and now you are out of business. So don't blame anyone else but yourself Mike!.

And dittos to what PJ Hooker said in his last post. Messengers have always been persecuted by those who run the State.