# XIRR - Dynamic Range

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

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

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

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

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

5. ## Re: XIRR - Dynamic Range

Originally Posted by petekris
I have been basically trying to insert the ending date and valuation date in the last row
Originally Posted by joeu2004
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

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

9. ## 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. ## Re: XIRR - Dynamic Range

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

11. ## 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. ## Re: XIRR - Dynamic Range

Thank you.

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

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