+ Reply to Thread
Results 1 to 16 of 16

Problem with indirect function in XIRR function

  1. #1
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    254

    Problem with indirect function in XIRR function

    Hello,

    I have in E181:

    F177:Q177,S177:AD177,AF177:AQ177,AS177:BD177,BF177:BQ177,BS177:CD177,CF177:CQ177,CS177:DD177,DF177:DQ177,DS177:ED177,EF177:EQ177,ES177:FD177,FF177:FQ177,FS177:GD177,GF177:GQ177,GS177:HD177,HF177:HQ177 these are the cash flow cells

    in F181 I have:

    F169:Q169,S169:AD169,AF169:AQ169,AS169:BD169,BF169:BQ169,BS169:CD169,CF169:CQ169,CS169:DD169,DF169:DQ169,DS169:ED169,EF169:EQ169,ES169:FD169,FF169:FQ169,FS169:GD169,GF169:GQ169,GS169:HD169,HF169:HQ169 these are the months associated with the cash flows

    these strings are returned by a macro I wrote....
    when I enter this formula

    =XIRR(INDIRECT(E181),INDIRECT(F181)), I got #REF error, looks like Excel does recognize the strings. Can someone please help?
    thanks
    Last edited by lynnsong986; 01-17-2020 at 03:20 PM.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Problem with indirect function in XIRR function

    please upload your WB

  3. #3
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Problem with indirect function in XIRR function

    INDIRECT can't work with union range

  4. #4
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    254

    Re: Problem with indirect function in XIRR function

    if Indirect can't do that, is there a way I can get around?

    this is related to another posting I did in the VBA section...

    https://www.excelforum.com/excel-pro...ml#post5262113

    thanks so much
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,927

    Re: Problem with indirect function in XIRR function

    In my limited experience, it seems that the XIRR() is one of those functions that really does not play nice with discontiguous ranges. As one who is not a user nor a fan of big array formulas, I would tend towards a solution that:
    1) Uses filters, lookups, or other strategies to extract the necessary data into a nice, contiguous ranges.
    2) Apply the XIRR() function to those ranges.
    I have seen others provide array functions that seem to get the XIRR() function to operate on "subranges" within a larger range. Perhaps the solutions proposed here may help: https://www.excelforum.com/excel-for...h-vlookup.html
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Problem with indirect function in XIRR function

    Untested, but try this

    =XIRR(INDEX((F177:HR177)*(LEN(F169:HR169)>4),0),INDEX((F169:HR169)*(LEN(F169:HR169)>4),0))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  7. #7
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    254

    Re: Problem with indirect function in XIRR function

    thanks so much for your time. the reason I did it this way is because the user doesn't want to see a separate sheet where I can pull the monthly cash flows in a contiguous data block (they want to see data and calculation on the same page and they need the monthly cash flows and a year column for each year). on top of that the number of years is a variable and I have a macro add/delete month/year columns according to that. I'll keep poking around and see if I can get it to work on the same sheet...thanks so much!

  8. #8
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Problem with indirect function in XIRR function

    List of numbers of columns by the {}
    It is not so easy to create and manage but you can try.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    hm. strange the first attempt was replace to 0 but result of XIRR was err. Now it's ok. #6 is solution.
    Last edited by BMV; 01-17-2020 at 04:06 PM.

  9. #9
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    254

    Re: Problem with indirect function in XIRR function

    OMG!!! it works perfectly!!! this is unbelievable! you are a genius! I spent 2 hours trying to get this to work (most of the time being spent writing a macro that didn't work in this case), thank you so much Ace_XL for your wonderful formula and everyone else here helping me out!

  10. #10
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    254

    Re: Problem with indirect function in XIRR function

    Thanks very much for your help BMV, the formula provided by Ace_XL works like a charm

  11. #11
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    254

    Re: Problem with indirect function in XIRR function

    Hi Ace_XL, I have another problem with the formula - the beginning month is always a January in a year but the 1st cash flow could be in any month in that first year. XIRR wouldn't work if the 1st cash flow is zero. I have a formula that gave me the column letter of the 1st cash flow (this changes based on projects/assets). i.e. 1st cash flow column is "AH", how can I incorporate this in the formula you came up with so that the cash flows start from this 1st non-zero value (any zeroes after will be included).

  12. #12
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    254

    Re: Problem with indirect function in XIRR function

    I figured it out! thanks!!

    =XIRR(INDEX((INDIRECT(C167&"177:HR177"))*(LEN(INDIRECT(C167&"169:HR169"))>4),0),INDEX((INDIRECT(C167&"169:HR169"))*(LEN(INDIRECT(C167&"169:HR169"))>4),0))

  13. #13
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Problem with indirect function in XIRR function

    Glad you got it sorted. In case you do not wish to use the helper cell C167, use this formula (not the prettiest though!)

    =XIRR(INDEX((INDEX(F177:HR177,MATCH(1,INDEX(--(F177:HR177<>0),0),0)):HR177)*(LEN(INDEX(F169:HR169,MATCH(1,INDEX(--(F177:HR177<>0),0),0)):HR169)>4),0),INDEX((INDEX(F169:HR169,MATCH(1,INDEX(--(F177:HR177<>0),0),0)):HR169)*(LEN(INDEX(F169:HR169,MATCH(1,INDEX(--(F177:HR177<>0),0),0)):HR169)>4),0))

  14. #14
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    254

    Re: Problem with indirect function in XIRR function

    oh wow you are truly awesome!! thanks a ton!

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

    Re: Problem with indirect function in XIRR function

    The problem is that INDIRECT only works with SINGLE-AREA ranges. While

    (F177:Q177,S177:AD177,AF177:AQ177,AS177:BD177,BF177:BQ177,BS177:CD177,CF177:CQ177,CS177:DD177,DF177:DQ177,
    DS177:ED177,EF177:EQ177,ES177:FD177,FF177:FQ177,FS177:GD177,GF177:GQ177,GS177:HD177,HF177:HQ177)

    [note the parentheses] may be a valid 17-area range reference, INDIRECT can't handle it. OTOH, INDIRECT can handle arrays of textrefs with a lot of coaxing. For example, with A1: =ROWS(A$1:A1) and filled down into A2:A12, =INDIRECT({"A2";"A3";"A5";"A7";"A11"}) returned diectly as an array formula returns a 6-row by 1-column array of #VALUE! errors, but =N(INDIRECT({"A2";"A3";"A5";"A7";"A11"})) returns the array {2;3;5;7;11}. This is undocumented behavior.

    IOW, INDIRECT passed an array of textrefs returns something which seems to be an array of range references. FWIW, those results could be used as 1st arguments to COUNTIF, which would return an array result due to that argument. Since COuNTIF only accepts range references as 1st arguments, it seems INDIRECT passed an array argument returns an array of them. This is inference, not proof. To repeat: this is undocumented behavior.

    All that said, there's a much simpler approach if you use array formulas. CORRECTION

    =XIRR(IF(MOD(COLUMN(F177:HQ177),13)<>5,F177:HQ177,0),IF(MOD(COLUMN(F169:HQ169),13)<>5,F169:HQ169,MAX(F169:HQ169)+1)

    This replaces the cells you want EXCLUDED in cols R, AE, AR, BE, BR, etc with 0 values and dates equal to one day after the last cashflow date. If that date is in cell HQ169, simply replace the MAX call above with HQ169. Adding cashflows of zero after the end of the actual cashflows should have no effect whatsoever on XIRR.
    Last edited by hrlngrv; 01-17-2020 at 06:08 PM.

  16. #16
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    254

    Re: Problem with indirect function in XIRR function

    I see, thank you so much for explaining this to me, and more thanks for the formula hrlngrv! I feel I've learnt so much in one day. Love this forum and the great people here!

+ 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. [SOLVED] Problem with XIRR function returning #NUM!
    By Myrna Larson in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 07:05 AM
  2. Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 02:05 AM
  3. Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 01:05 AM
  4. [SOLVED] Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 12:05 AM
  5. Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2005, 11:05 PM
  6. Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2005, 10:05 PM
  7. Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-06-2005, 09:05 PM
  8. [SOLVED] Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2005, 09:40 PM

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