+ Reply to Thread
Results 1 to 5 of 5

IRR function returns #NUM error.

  1. #1
    Registered User
    Join Date
    10-10-2020
    Location
    US
    MS-Off Ver
    365
    Posts
    3

    IRR function returns #NUM error.

    Hi. I do not why but IRR returns a #NUM error. What is the problem here?

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: IRR function returns #NUM error.

    It is because the negative values exceed the positive values.
    Glenn



  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: IRR function returns #NUM error.

    It is perfectly okay for negative values to exceed positive values, and vice versa. For example, =IRR({-15000,-10000,10000,10000}) results in -10.0788107060756%.

    -----

    The real problem is that the signs of the first and last cash flows are the same. They are usually different.

    First, the worksheet seems to be incomplete. The PV formulas in A2:A4 reference G2, which is blank. Consequently, the PV discount rate is effectively 0%.

    Second, the cash flows in an IRR model are usually "current dollars", not discounted amounts. That is the purpose of IRR: to determine the average discount rate.

    In any case, without any explanation of what the values in A1:A4 are intended to represent, we really cannot help you remedy the #NUM error.

    With the current cash flow sequence {-15000,10000,10000,-10000}, I believe there is no IRR.

    In this case, that is what #NUM is telling you. I believe there is no "guess" discount rate that will improve the result.

    -----

    Perhaps this will help.

    The first cash flow is usually an initial investment. You can choose plus or minus values; it's arbitrary. Let's choose minus sign.

    The subsequent cash flows are additional net investments (signed the same as the initial investment) or net withdrawals (with the opposite sign). They might be zero for periods with no net cash flow.

    But note that the cash flows are amounts of net change, not an account balance.

    The last cash flow is the ending balance, usually signed as a net withdrawal.
    Last edited by joeu2004; 11-05-2020 at 09:58 PM.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: IRR function returns #NUM error.

    Quote Originally Posted by Glenn Kennedy View Post
    . . . because the negative values exceed the positive values.
    ?

    IRR({-2,1}) and IRR({2,-1}) are both -50%. The ONLY requirement for there to be an IRR is that there be at least one sign change in the cashflows, that is, at least one positive number and one negative number. More of one or the other is irrelevant. Also, if X is a valid array of cashflows, -X is also a valid array of cashflows, and X and -X have the same IRRs because the array of discount factors D = {d[k] = (1+IRR)^-k, k = 0..N} viewed as a vector is orthogonal to both X and -X viewed as vectors. This is pure geometry or linear algebra (in this case, they're effectively the same).

    That's the math. When there are pathological cashflows like {-1.5;1;1;-1}, there are still geometric IRRs, but they're meaningless financially. In this case, IRR = -189.9211893%. For what little it's worth, LibreOffice Calc returns -1.89921189293924 and Google Sheets return -1.899211893. Excel bails out when the IRR would be less than -100%.

  5. #5
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: IRR function returns #NUM error.

    In this case, the #NUM! error means you have a financially meaningless result, less than 100%. The meaning of an IRR less than 100% is that one of the parties to the cashflow (viewed as between 2 people) is willing to pay extra for the opportunity of a 100% loss. Considering your cashflows, you invest 15,000 at time 0. You receive 10,000 each at times 1 and 2. Then you pay 10,000 at time 3 for the thrill of the previous 3 cashflows. Had the cashflows been {-15000;-10000;10000;10000}, the IRR would have been -10.0788107060757%. Why does {-15000;10000;10000;-10000} have an IRR less than 100%? To be blunt, IRR IS MEANINGLESS. All it is is a rate derived from a vector orthogonal to the vector of your cashflows, which means that your cashflow using the IRR as a discount rate produces a zero NPV. While NPV is meaningful for discount rates from -100% to +infinity, it's not meaningful for discount rates less than -100%.

    In short, the correct economic interpretation is that no rational economic actor would want this particular cashflow.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Linest() function returns error
    By Peter Niklas in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2017, 06:15 PM
  2. [SOLVED] DAY-function returns Error
    By Jonathan78 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-18-2015, 08:17 AM
  3. MOD() Function Returns #NUM! Error Value
    By filky in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-16-2014, 04:40 AM
  4. [SOLVED] Using IF function for a range returns error
    By LoneWolf3574 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-09-2013, 04:53 AM
  5. [SOLVED] User defined function returns an error on a standard function used in it.
    By pb48 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-23-2013, 01:35 PM
  6. [SOLVED] VBA Userdefined Function returns #Value! error
    By mandukes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2012, 10:01 AM
  7. Function Returns Value Error
    By realniceguy5000 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-14-2011, 10:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1