+ Reply to Thread
Results 1 to 12 of 12

XIRR - Dynamic Range

  1. #1
    Registered User
    Join Date
    07-24-2020
    Location
    India,Canada
    MS-Off Ver
    2016
    Posts
    5

    XIRR - Dynamic Range

    Dear All,

    Requesting assistance to calculate XIRR where the dates and cashflows are derived from another sheet (based on transactions). The ending value and valuation date will depend on the day the XIRR is calculated which is populated separately.

    I am attaching the sheet with sample data, would like assist to calculate XIRR. I have been basically trying to insert the ending date and valuation date in the last row using Offset, Indirect etc., but unable to get my head around it.
    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: XIRR - Dynamic Range

    So, if I follow you (which is by no means certain!!), the formulae to find the start date (first row) in B5 is

    =INDEX($A$1:$A$500,MATCH(TRUE,INDEX(ISNUMBER($A$1:$A$500),),))

    and the last valid date in B6 is:

    =LOOKUP(1E+100,A:A)

    Then 5the XIRR is a bit of a monster:

    =XIRR(INDEX($B:$B,N(IF(1,MODE.MULT(IF($A$9:$A$500>=$B$6,IF($A$9:$A$500<=$B$5,{1,1}*ROW($A$9:$A$500))))))),INDEX($A:$A,N(IF(1,MODE.MULT(IF($A$9:$A$500>=$B$6,IF($A$9:$A$500<=$B$5,{1,1}*ROW($A$9:$A$500))))))))

    This is an array formula. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    I manually verified it was giving the correct answer by selecting the same range using a non-dynamic XIRR in the orange shaded cell.
    Attached Files Attached Files
    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: XIRR - Dynamic Range

    @petekris.... It would have been nice if you provided the manual calculation in B1, especially for an example where the valuation date (B3) is an earlier date (e.g. 1-Oct-2020), not literally =TODAY().

    Does the following work for your purposes? See the attached file.

    In B1, array-enter (press ctrl+shift+Enter instead of just Enter) the following. Excel displays the formula surrounded by curly braces.

    Please Login or Register  to view this content.
    The formula assumes that you replace null strings in A11:A440 with zero, formatted as Custom [$-409]d-mmm-yyyy;;"" so that they appear to be blank.

    Note that the range includes row 441, which is intentionally beyond the last potential row of real data (row 440).

    The formula pairs values in column B and dates in column A up until the valuation date in B3. It substitutes pairs of zeros for dates after that and for "blank" rows through row 440, which Excel XIRR allows.

    Then for "row 441", the formula pairs the ending value in B2 and the valuation date in B3.

    Note that I replaced the formula in B3 with a specific date in order to suit my purposes, just for example.

    I also replaced the formula in B5 ("sale/value dt") with a specific data that makes sense vis-a-vis the valuation date in B3. This change is just for aesthetics.

    The formulas in column E:H are not required; they are provided as a "manual" check of the formula in B1 below.

    I made some assumption, which are not clear from your problem description and spreadsheet design.

    1. If the valuation date occurs on the date of an equity cash flow, XIRR will effectively calculate the __net__ cash flow. In other words, B2 + equityCF. If you want to exclude any equity CF on the valuation date, change "<=B3" to "<B3".

    2. The term "valuation date" is unclear. Is it: (1) the date labeled "valuation date" in B3 (well, duh!); or (2) the date labeled "sale/value dt" in B5. Was I correct to use B3 in the formula?

    Finally, a suggestion.... Change the Conditional Formatting rule for B11:B440 so that zero is displayed in normal font color for dates in A11:A440, but the cell appears blank if there is no date in A11:A440 (zero, in my design). Thus:

    Use formula: =A11=0
    Format > Number > Custom Type: ;;
    Format > Font > Color: Automatic
    Last edited by joeu2004; 11-24-2020 at 06:59 PM.

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

    Re: XIRR - Dynamic Range

    If B7 and B8 could be row indices rather than addresses,

    B7: =MATCH(1,0/(A11:A440=B5))
    B8: =MATCH(B6,A11:A440,0)

    CORRECTION

    B1: =XIRR(INDEX(B11:B440,B8):INDEX(B11:B440,B7),INDEX(A11:A440,B8):INDEX(A11:A440,B7))

    ADDED: I may have misunderstood, and the goal were to incoroprate the date in B3 and value in B2 into the XIRR calculation. If so, with B7:B8 as above,

    B1:
    =XIRR(IF(ROW(INDEX(A11:A441,B8):INDEX(A11:A441,B7+1))-11<B7,INDEX(B11:B441,B8):INDEX(B11:B441,B7+1),B2),
    IF(ROW(INDEX(A11:A441,B8):INDEX(A11:A441,B7+1))-11<B7,INDEX(A11:A441,B8):INDEX(A11:A441,B7+1),B3))
    Last edited by hrlngrv; 11-24-2020 at 11:58 PM. Reason: addendum

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

    Re: XIRR - Dynamic Range

    Quote Originally Posted by petekris View Post
    I have been basically trying to insert the ending date and valuation date in the last row
    Quote Originally Posted by joeu2004 View Post
    It would have been nice if you provided the manual calculation in B1, especially for an example where the valuation date (B3) is an earlier date (e.g. 1-Oct-2020), not literally =TODAY().
    I have might have misinterpreted the flexibility of the design that you require, and complicated the XIRR solution in the process.

    If you simply want to append the ending valuation (B2) and valuation date (B3) to the end of the equity cash flows, array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into B1.

    (See the "simple" worksheet in the attached file.)

    Please Login or Register  to view this content.
    The formula assumes that you replace null strings in A11:A440 with zero, formatted as Custom [$-409]d-mmm-yyyy;;"" so that they appear to be blank.

    If you prefer to leave null strings (why?!!), use the following formula instead.

    Please Login or Register  to view this content.
    See the explanation and suggestions in my posting #2 in this thread.

    But change the suggested Conditional Formatting for B11:B440 as follows:

    Use formula: =OR(A11=0, A11="")
    Format > Number > Custom Type: ;;
    Format > Font > Color: Automatic
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-24-2020
    Location
    India,Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: XIRR - Dynamic Range

    Thank you so much all, will review all the responses today. Much appreciated for all the assistance.

    Yes I am looking to append the end date (or valuation date) and the end value (or valuation) at the end of the transaction dates and transaction values, noting that the transaction dates and transaction values will keep expanding with time.

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

    Re: XIRR - Dynamic Range

    Actually we've all lost sight of the forest while classifying the trees. XIRR doesn't need to have its dates in sorted order.

    If you'd NEVER go below row 440, and B11:B440 cells would contain 0 below the last cashflow, you could add formulas in A441:B441,

    A441: =B3
    B441: =B2

    then take advantage of how XIRR actually works, namely,

    B1: =XIRR(B11:B441,A11:A441)

    XIRR ignores entries in which the date argument is blank and the value argument is 0.

    If you may need to accommodate cashflows going below row 440, copy pictures or picture links to sit on top of A10:B10, and make those cell's actual contents formulas.

    A10: =B3
    B10: =B2

    B1: =XIRR(B10:B###,A10:A###)

  8. #8
    Registered User
    Join Date
    07-24-2020
    Location
    India,Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: XIRR - Dynamic Range

    Dear Joeu,

    Thank you, problem solved. Apologies if the cell labels were not clear, as the worksheet was derived from much larger valuation file, due to my tardiness failed to appropriately re-label for the purpose of seeking assistance. Sincere apologies for that. Here is the solved sheet.

    I was trying to insert end value and end date in the last row through the formula in B7 (address of the last transaction row) and subsequently calculate XIRR in B1.

    I presume that is not possible and your elegant solution solved the issue, awesome. Thank you.

    Your formatting tips are well received as well, will modify accordingly.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-24-2020
    Location
    India,Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: XIRR - Dynamic Range

    Absolutely right, made it more complicated in my head then it actually was, thank you for pointing it out.

    The data will expand beyond row 440 and as suggested to accommodate this, the XIRR range will accordingly change.

    Hopefully the labelling in the solved sheet makes a little more sense.

    Now I can use this solution for the mutual fund cashflows as well.

    Thank you.

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

    Re: XIRR - Dynamic Range

    Quote Originally Posted by hrlngrv View Post
    you could add formulas in A441:B441,
    A441: =B3
    B441: =B2
    then take advantage of how XIRR actually works, namely,
    B1: =XIRR(B11:B441,A11:A441)
    Quite right! I got hung-up on a design that was necessary for my original interpretation. With my second interpretation, I made the minimum design change, without rethinking.

    Your suggestion is the "obvious" way to accomplish what I propose with my second suggestion. Very good!

    (Second time's a charm. Wink!)

    But....


    Quote Originally Posted by hrlngrv View Post
    XIRR doesn't need to have its dates in sorted order.
    [....]
    If you may need to accommodate cashflows going below row 440 [....]
    A10: =B3
    B10: =B2
    B1: =XIRR(B10:B###,A10:A###)
    That does not work. Excel XIRR returns #NUM. (In this case, that is __not__ a "guess" issue. See below.)

    You are correct insofar as __most__ dates do not need to be in ascending order.

    But the __first__ date must be the earliest date, which corresponds to "time 0", the PV date. As the help page explains (poorly): ``If any number in dates precedes the starting date, XIRR returns the #NUM! error value``.

    This is also self-evident from the mathematical formula for the (X)NPV, which XIRR tries to drive to zero iteratively.

    The discount term is 1 / (1+r)^((d[i] - d[1]) / 365)

    where d[i] is the date corresponding to the i-th CF in the range, and d[1] is the date corresponding to the first CF in the range, which again is presumed to be the earliest date.

    IMHO, the easiest way to accommodate larger ranges (beyond row 440) is:

    1. Simply be sure that the range is large enough to accommodate the largest likely number of cash flows within reason; e.g. rows 11:1000 or rows 11:10000. Or

    2. Always insert additional rows __before__ original row 441, shifting original row 441 down. That automatically adjusts references to row 441 in the original formula =XIRR(B11:B441,A11:A441).
    Last edited by joeu2004; 11-25-2020 at 07:25 AM.

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

    Re: XIRR - Dynamic Range

    Guilty: I used LibreOffice Calc to test.

    FWIW, one reason to use Google Sheets rather than Excel or LibreOffice Calc: =xirr({B11:B440;B2},{A11:A440;B3}).

  12. #12
    Registered User
    Join Date
    07-24-2020
    Location
    India,Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: XIRR - Dynamic Range

    Thank you.

+ 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. XIRR with dynamic range
    By kylc3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2017, 03:32 PM
  2. XIRR with dynamic range based on date input (OFFSET)
    By TripleG-CO in forum Excel General
    Replies: 9
    Last Post: 06-29-2015, 08:06 PM
  3. XIRR with dynamic range
    By j.rogne in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2015, 11:30 AM
  4. Dynamic XIRR formula?
    By tonylyx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2015, 11:17 PM
  5. XIRR with Dynamic Range
    By smorkun in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-27-2014, 06:15 PM
  6. XIRR Dynamic Range
    By JeffHowell in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-12-2014, 03:34 PM
  7. XIRR with Dynamic Ranges
    By coldcanuck in forum Excel General
    Replies: 3
    Last Post: 08-04-2011, 02:41 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