View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline
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%.