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.

@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

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))

Originally Posted by hrlngrv

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....

Originally Posted by hrlngrv

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).