+ Reply to Thread
Results 1 to 15 of 15

Help in calculating XIRR for entries from different columns

  1. #1
    Registered User
    Join Date
    04-20-2018
    Location
    India
    MS-Off Ver
    MS office 2016
    Posts
    31

    Post Help in calculating XIRR for entries from different columns

    Hello all,

    I need a help in calculating XIRR for each line on monthly basis entries.

    I have highlighted formula column with Yellow.

    Please suggest where I am getting it wrong as the values with current formula are coming unrealistic.

    Rushi.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Help in calculating XIRR for entries from different columns

    What do you think the {1,0} is doing?

    Can you explain what you are attempting to do, as you formula syntax is VERY strange??
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

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

    Re: Help in calculating XIRR for entries from different columns

    Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into F3, then copy down through F19:

    =XIRR(IF(ROW($C$2:C3)=ROW(C3), C3-E3, $C$2:C3), $B$2:B3)

    Note that the last cash flow in each series must be the net cash flow: additional investment (column C) minus ending balance (column E), not simply the ending balance (negated).
    Last edited by joeu2004; 09-10-2019 at 12:01 PM.

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

    Re: Help in calculating XIRR for entries from different columns

    Quote Originally Posted by joeu2004 View Post
    Array-enter [....]:
    =XIRR(IF(ROW($C$2:C3)=ROW(C3), C3-E3, $C$2:C3), $B$2:B3)
    Although that formula is correct, note that XIRR seems to return 0.00% in F8, F18 and F19.

    In fact, the actual value is about 2.98E-09. In my experience, that is an error state that sometimes should be interpreted the same as #NUM, to wit: we need to provide a third "guess" parameter in order to help XIRR find an IRR.

    It is difficult to determine an appropriate "guess". But sometimes, -10% is sufficient. And in those 3 cases, we are indeed that lucky.

    So, array-enter the following formula into F8, then copy F8 and paste into F18 and F19:

    =XIRR(IF(ROW($C$2:C18)=ROW(C18), C18-E18, $C$2:C18), $B$2:B18, -10%)

  5. #5
    Registered User
    Join Date
    04-20-2018
    Location
    India
    MS-Off Ver
    MS office 2016
    Posts
    31

    Re: Help in calculating XIRR for entries from different columns

    jeou2004,

    Thanks for your efforts. Formula works fine for the normal cases but the instances that you have mentioned (F8, F18 & F19) which needs mention of "guess" in those cells I will have to edit this formula for the manually.

    suggest what other possible combination of functions I could you to overcome this limitation.

    Rushi.

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

    Re: Help in calculating XIRR for entries from different columns

    Quote Originally Posted by rishiambekar View Post
    suggest what other possible combination of functions I could you to overcome this limitation
    Really, none is guaranteed to avoid adding a "guess". And unfortunately, I don't know of any way to automate the determination of a "guess".

    (Other than a customized VBA function, which I do not have time to develop. But keep in mind that for some cashflow series, there might be multiple IRRs or none at all. That's the mathematical nature of finding IRRs.)

    However, Excel IRR seems to be a more stable implementation than Excel XIRR. And I had considered suggesting that you use Excel IRR anyway, since your cash flows are monthly.

    Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into F3, then copy F3 and paste into F4:F19.

    =(1 + IRR(IF(ROW($C$2:C3)=ROW(C3), C3-E3, $C$2:C3)))^12 - 1

    If you compare with the results from my previous XIRR formulas, you will see some difference -- as much as 0.25%. The difference arises because Excel IRR assumes equal periods, whereas XIRR uses the exact number of days
    between cashflows.

    But I want to reiterate: it is only a coincidence that Excel IRR does not need a "guess" when used with your example. It might need a "guess" with another example. It simply cannot be avoided.

    PS.... An alternative is to calculate the so-called time-weighted return (TWR), aka time-weighted rate of return (TWRR). (IMHO, it is neither time-valued nor time-weighted.) However, that requires more information; or we can make some assumptions, for example: all deposits occur at the beginning of the month. Nonetheless, the TWR is always computable. And IMHO, it is a better reflection of the market rate of return. If you're curious, start by reading the wikipage.
    Last edited by joeu2004; 09-11-2019 at 02:28 AM.

  7. #7
    Registered User
    Join Date
    04-20-2018
    Location
    India
    MS-Off Ver
    MS office 2016
    Posts
    31

    Re: Help in calculating XIRR for entries from different columns

    I shall try IRR formula as suggested above. Thanks ioeu2004!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Help in calculating XIRR for entries from different columns

    Or apply a round to the final result:

    =ROUND(XIRR(IF(ROW($C$2:C3)=ROW(C3), C3-E3, $C$2:C3), $B$2:B3),5)

    here set to 5 dps. Adjust as desired/appropriate for the precision of your need.

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

    Re: Help in calculating XIRR for entries from different columns

    Quote Originally Posted by Glenn Kennedy View Post
    Or apply a round to the final result:
    =ROUND(XIRR(IF(ROW($C$2:C3)=ROW(C3), C3-E3, $C$2:C3), $B$2:B3),5)
    Whatever for?! What problem are you trying to solve?

    Surely not to hide the 2.98E-09 and turn the IRR into a solid 0.00%.

    Because I did such a great job (wink) in post #4 of explaining that bogus result.

    With an appropriate "guess", the correct (X)IRRs are -44.09%, -10.03% and -6.81% in F8, F18 and F19.

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Help in calculating XIRR for entries from different columns

    suggest what other possible combination of functions I could you to overcome this limitation.
    I don't have a new combination of functions -- rather a change of spreadsheet. Are you required to use Excel for this? I have noted in the past that some of the numerical instability that causes Excel's implementation of the XIRR() function to return 2E-9 or other error/nonsense value is unique to Excel. In other spreadsheets (Google sheets, LibreOffice Calc, etc.), the XIRR() implementation is more stable and will return correct answers when Excel's implementation fails.

    The present case, for example, I opened the sample file in post #1 in Gnumeric, array entered Joeu2004's formula from post #3 (the one that did NOT include a "guess" parameter) into F2 with no edits, and copied down to F18. The results all seemed reasonable, non-zero, non-error results. If you are allowed to use a spreadsheet other than Excel for this, you might find better numeric stability with other spreadsheets.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  11. #11
    Registered User
    Join Date
    04-20-2018
    Location
    India
    MS-Off Ver
    MS office 2016
    Posts
    31

    Re: Help in calculating XIRR for entries from different columns

    The formula mentioned here returns exactly the same values as that of the one from Post #2 by joeu2004.

  12. #12
    Registered User
    Join Date
    04-20-2018
    Location
    India
    MS-Off Ver
    MS office 2016
    Posts
    31

    Re: Help in calculating XIRR for entries from different columns

    Quote Originally Posted by Glenn Kennedy View Post
    Or apply a round to the final result:

    =ROUND(XIRR(IF(ROW($C$2:C3)=ROW(C3), C3-E3, $C$2:C3), $B$2:B3),5)

    here set to 5 dps. Adjust as desired/appropriate for the precision of your need.
    The formula mentioned here returns exactly the same values as that of the one from Post #2 by joeu2004.

  13. #13
    Registered User
    Join Date
    04-20-2018
    Location
    India
    MS-Off Ver
    MS office 2016
    Posts
    31

    Re: Help in calculating XIRR for entries from different columns

    Quote Originally Posted by joeu2004 View Post
    Whatever for?! What problem are you trying to solve?

    Surely not to hide the 2.98E-09 and turn the IRR into a solid 0.00%.

    Because I did such a great job (wink) in post #4 of explaining that bogus result.

    With an appropriate "guess", the correct (X)IRRs are -44.09%, -10.03% and -6.81% in F8, F18 and F19.
    joeu2004,
    I guess here the more reliable results are with IRR formula (in post #6) instead of XIRR. however want ask two doubts:

    1. Is this a realistic number -44.09%? By just putting "guess" it gets down to -10.03%
    2. At all three places where there is a "guess", it returns to the same result i.e. 10.03%. Strange?

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

    Re: Help in calculating XIRR for entries from different columns

    Quote Originally Posted by rishiambekar View Post
    Is this a realistic number -44.09%?
    "Realistic"?! It is not a word that I would associate with any IRR. (wink)

    Seriously, in some contexts, the IRR "makes sense" as an interest rate.

    But mathematically, an IRR is simply a discount rate that causes the NPV to be (nearly) zero.

    My Excel IRR formula returns -44.1961900237213% in F8, which displays -44.20% (not -44.09%) when formatted as Percentage with 2 decimal places.

    (My Excel XIRR formula returns -44.0892794914544%, which displays as -44.09%.)

    We can check the Excel IRR result by array-entering (press ctrl+shift+Enter instead of just Enter) the following formula, formatted as Scientific:

    =NPV((1+F8)^(1/12)-1, IF(ROW($C$2:C8)=ROW(C8), C8-E8, $C$2:C8))

    For me, that returns about 6.11E-11, which is indeed "nearly zero". Therefore, yes, -44.1961900237213% is "realistic" insofar as it is mathematically correct.

    (We should be able to use Excel XNPV to check the Excel XIRR result, but there is a defect: XNPV does not permit negative discount rates. Therefore, in general, we must use a SUMPRODUCT or array-entered SUM formula to calculate the "XNPV". But that is beyond the scope here, since you have decided to use Excel IRR, I presume.)

    -----

    It is possible that there is more than one IRR. If there is, you might feel that one of the other IRRs is more "realistic".

    Look at the "NPV curve" to determine if there might be multiple IRRs. Enter -99%, -90%, -80%, etc through 100% into K3:K23. Then array-enter the following formula into L3, formatted as Scientific, and copy L3 into L4:L23:

    =NPV((1+K3)^(1/12)-1,IF(ROW($C$2:$C$8)=ROW($C$8), $C$8-$E$8, $C$2:$C$8))

    (See the attached Excel file.)

    An IRR might be between two discount rates where there is a sign change in the NPV.

    I see a sign change only between -50% and -40%. Thus, there probably is indeed only one IRR; and -44.20% is "realistic" insofar as it is between -50% and -40%.

    Arguably, we might extend the NPV curve beyond 100%. But in this case, we can infer that it will just increase monotonically and perhaps become asymptotic to some large positive value.

    (There is little point in extending the NPV curve below -99%, since the discount rate must be less than -100%.)



    Quote Originally Posted by rishiambekar View Post
    By just putting "guess" it gets down to -10.03% [....] At all three places where there is a "guess", it returns to the same result i.e. 10.03%. Strange?
    As you can see in the attached file, I do not get -10.03% in all 3 places (G8, G18 and G19).

    The only way that I might see -10.03% in all 3 places is to select multiple cells (instead of just one cell) when I array-enter the formula.

    (But I cannot do that by selecting just G8, G18 and G19, because they are not contiguous.)

    It is important to select just one cell when we array-enter these formulas. Then copy the one cell and paste into the other cells.

    If you still have a question about this, please attach a (new) Excel file that demonstrates the repeated -10.03%. (Please do not replace your previous attachment.) And explain what you typed (keystroke-by-keystroke) to get repeated -10.03%.
    Attached Files Attached Files
    Last edited by joeu2004; 09-12-2019 at 04:15 PM.

  15. #15
    Registered User
    Join Date
    04-20-2018
    Location
    India
    MS-Off Ver
    MS office 2016
    Posts
    31

    Re: Help in calculating XIRR for entries from different columns

    joeu2004,

    I have all my queries answered. Thanks for such good explanation & your efforts to resolve the doubts in mind.

+ 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. Calculating xirr with vlookup
    By vicky6615 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-22-2019, 05:13 PM
  2. Calculating IRR using XIRR to calculate interest
    By sgcray in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-26-2019, 02:30 AM
  3. Replies: 5
    Last Post: 07-09-2017, 03:24 AM
  4. Calculating XIRR w/ zero's in first few datapoints.
    By supra98x in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2013, 06:52 AM
  5. Calculating XIRR Quarterly (or any time period)?
    By sabunabu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2013, 03:56 AM
  6. Replies: 15
    Last Post: 03-11-2012, 10:22 PM
  7. Replies: 4
    Last Post: 06-08-2006, 10:35 PM

Tags for this Thread

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