Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default IRR function in Excel 2010 returns #NUM! error for an investment withall negative cash flows

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.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: IRR function in Excel 2010 returns #NUM! error for an investment withall negative cash flows

The IRR function in Excel calculates the internal rate of return for a series of cash flows that occur at regular intervals. It assumes that the cash flows are evenly spaced and that there is at least one positive and one negative cash flow. However, in your case, all the cash flows are negative, which is causing the #NUM! error in Excel 2010.

The tadXL Excel add-in you used probably uses a different algorithm to calculate the IRR, which allows it to handle all negative cash flows. It is possible that tadXL uses a more advanced numerical method to calculate the IRR, such as the Newton-Raphson method, which can handle all negative cash flows.

If you want to use Excel's IRR function to calculate the internal rate of return for your investment with all negative cash flows, you can try adding a positive cash flow at the end of the investment period. This positive cash flow should be equal to the absolute value of the sum of all the negative cash flows. For example, in your case, you could add a positive cash flow of $2110.50 (the sum of all the negative cash flows) at the end of the investment period. This will allow Excel's IRR function to calculate the internal rate of return for your investment.

Alternatively, you can use a different financial function in Excel, such as the XIRR function, which can handle irregularly spaced cash flows and all negative cash flows. The XIRR function requires you to specify the dates of each cash flow, so you will need to create a separate column for the dates. Here's how you can use the XIRR function:
  1. Enter the dates of each cash flow in a separate column.
  2. Enter the cash flows in another column, including the initial cash outflow of $1000.
  3. Select the range of cells containing the dates and cash flows.
  4. Go to the Formulas tab and click on Financial XIRR.
  5. In the XIRR dialog box, enter the range of cells containing the dates and cash flows, and click OK.
  6. The XIRR function will return the internal rate of return for your investment.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default IRR function in Excel 2010 returns #NUM! error for an investment with all negative cash flows

On Mon, 26 Aug 2013 13:23:21 -0700 (PDT), Michael Marshall wrote:

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%.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default IRR function in Excel 2010 returns #NUM! error for an investment with all negative cash flows

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default IRR function in Excel 2010 returns #NUM! error for an investment with all negative cash flows

On Mon, 26 Aug 2013 16:02:12 -0700, "joeu2004" wrote:

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 :-(


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default IRR function in Excel 2010 returns #NUM! error for an investment with all negative cash flows

Errata (typos).... I wrote:
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.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default IRR function in Excel 2010 returns #NUM! error for an investmentwith all negative cash flows

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.
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default IRR function in Excel 2010 returns #NUM! error for an investmentwith all negative cash flows

On Monday, August 26, 2013 4:23:21 PM UTC-4, Michael Marshall wrote:
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.


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 I desired 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 to using 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 ?
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default IRR function in Excel 2010 returns #NUM! error for an investment with all negative cash flows

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


ROTFLMAO!
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default IRR function in Excel 2010 returns #NUM! error for an investmentwith all negative cash flows

On Tuesday, August 27, 2013 12:59:48 PM UTC-4, Michael Marshall wrote:
So @JoeU


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


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.


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default IRR function in Excel 2010 returns #NUM! error for an investmentwith all negative cash flows

On Monday, August 26, 2013 7:02:12 PM UTC-4, joeu2004 wrote:
[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 show called "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.


  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default IRR function in Excel 2010 returns #NUM! error for an investmentwith all negative cash flows

On Monday, August 26, 2013 at 11:53:21 PM UTC+3:30, Michael Marshall wrote:
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.


Please use COMFAR SOFTWARE for more accurate results
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating quarterly investment returns---XIRR or another function?? Carl LaFong Excel Worksheet Functions 13 January 8th 08 10:59 AM
IRR function when the cash flows are not in array? waradmiral1991 Excel Worksheet Functions 1 September 7th 06 05:38 AM
IRR for 2 cash flows Maxymus Excel Worksheet Functions 1 May 13th 06 01:40 AM
Future Value function with differenct cash flows Joe V Excel Discussion (Misc queries) 1 September 3rd 05 06:06 AM
NPV Calc appears incorrect with a large # of negative cash flows MikeW Excel Worksheet Functions 1 September 1st 05 03:31 PM


All times are GMT +1. The time now is 10:03 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"