XIRR Non Contiguous Data

[EasterGreen]

I have cash flows in a table for a variety of contracts but initial investment values are separate and the investment dates are also separate, I have tried OFFSET etc but am struggling to make any sense of it.
Example:

B14..Z14 has the initial investment dates
B15..Z15 has the initial investment values

A20..A99 has the cash flow dates
B20..Z99 has the cash flows

Guess would be 10%.

a very simplified way of showing what I am trying to get in cell B10 could be (but doesn't work)
=XIRR(b$15 then b$20..b$99, b$14 then $a$20..$a$99, 10%)
and C10 in the same manner
=XIRR(c$15 then c$20..c$99, c$14 then $a$20..$a$99, 10%)

Thanks!

 

[joeu2004]

Great explanation!

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formulas:

B10: =XIRR(IF(ROW(B$19:B$99)=ROW(B$19),B$15,B$19:B$99), IF(ROW($A$19:$A$99)=ROW($A$19),B$14,$A$19:$A$99), 10%)

C10: =XIRR(IF(ROW(C$19:C$99)=ROW(C$19),C$15,C$19:C$99), IF(ROW($A$19:$A$99)=ROW($A$19),C$14,$A$19:$A$99), 10%)

 

[EasterGreen]

Thank you very much, works perfectly.

 

[joeu2004]

You're welcome. If you ever decide that Excel IRR is sufficient, it supports the use the range union. So we could write IRR((B$19,B$20:B$99)).

If you are using a recent version of Excel, you might try XIRR((B$19,B$20:B$99),(B$14,$A$20:$A$99)).

That does not work in Excel 2010. But I cannot think of any reason why MSFT cannot allow other functions to accept range unions.

 

[john53]

Great explanation!

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formulas:

B10: =XIRR(IF(ROW(B$19:B$99)=ROW(B$19),B$15,B$19:B$99), IF(ROW($A$19:$A$99)=ROW($A$19),B$14,$A$19:$A$99), 10%)

C10: =XIRR(IF(ROW(C$19:C$99)=ROW(C$19),C$15,C$19:C$99), IF(ROW($A$19:$A$99)=ROW($A$19),C$14,$A$19:$A$99), 10%)

 

[john53]

I'm new to the site and hope someone can help with a question. joeu2004 posted a very nice solution to a question posted by EasterGreen in 2019. I have a slight twist on the original question. More specifically, I would be interested in knowing if it is possible to extend joeu2004's solution to allow for 3 sets of non-contiguous data - initial dates and values, closing dates and values and the intermediate cashflows. Following the format used by EasterGreen, my question is whether there is an XIRR solution that deals with the following:

B14..Z14 has the initial investment dates
B15..Z15 has the initial investment values

B16..Z16 has the closing investment dates
B17..Z17 has the closing investment values

A20..A99 has the cash flow dates
B20..Z99 has the cash flows

Guess would be 10%.

 

[joeu2004]

I'm new to the site and hope someone can help with a question.

Probably. But you should ask the question in a new thread, referring to the URL of this thread, if you wish.

(Use the form "foo dot bar at somewhere dot com" if this forum does not allow you post an actual link yet.)

When you repost in a new thread, provide a concrete example of the data. Ideally, use the XL2BB tool. Alternatively (okay with me, but not with others), upload an Excel file (not images) to a file-sharing website (e.g. box.net/files or dropbox.com), and post the public sharable URL.

Obviously, if you use XL2BB, you need to scale down the problem. Perhaps just columns A, B and C are sufficient. And perhaps you can make do with fewer rows of intermediate cash flows instead of all of rows 20:99.

And please clarify:

1. Is there always data in all rows 20:99? If not, are the rows of unused data truly empty; e.g. ISBLANK(A99) and ISBLANK(B99) both return FALSE? Or do they contain the null string (to appear blank)?

2. Am I correct in interpreting that the data in column B is one set of cash flows for which to calculate an IRR; and column C is another set with a separate IRR; etc? In other words, why do you need more than just column A and column B as an example for your question?

3. If the dates appear to be at regular intervals (e.g. monthly or yearly), why not use Excel IRR instead of XIRR? Excel IRR might be easier to use.

4. Finally, why do assume or insist that the "guess" is 10% (default; ergo, no "guess" is needed at all)? Or are you saying that the "guess" should be a variable (cell reference)? If the latter, what cell is the "guess" in? And must it be the same "guess" for all columns of cash flows?

Sorry for the plethora of questions -- which should be addressed in the new thread, not here. Just trying to get a leg-up so that the new thread is as clear and complete as possible.

I'll look for the new thread.

 

[joeu2004]

Errata....

are the rows of unused data truly empty; e.g. ISBLANK(A99) and ISBLANK(B99) both return FALSE?

Ah, do they both return TRUE? (Doh!)

And it is sufficient for only ISBLANK(B99) to return TRUE. That is, truly empty cash flow values, even if there might be dates in column A.

But if there is no date in column A, does ISBLANK(A99) return TRUE (truly empty)? Or does A99 contain a null string (to appear blank)?

 

[joeu2004]

But you should ask the question in a new thread
[....]
When you repost in a new thread, provide a concrete example of the data.
[....]
And please clarify:

It is indeed better to start a new thread, since the details are often different.

But sorry: if the original posting in this thread was a ``Great explanation!``, yours is, too, since it is just an edit of the original.

Actually, the original problem description was lacking. I made several assumptions, which I should have explained (below).

Nevertheless, making the same assumptions, I could have answered your question better.

For the example below, enter the following formula (in B13, for column B data):

=XIRR(IF(ROW(B$19:B$100)=ROW(B$19), B$15, IF(ROW(B$19:B$100)=ROW(B$100), B$17, B$19:B$100)),
IF(ROW($A$19:$A$100)=ROW($A$19), B$14, IF(ROW($A$19:$A$100)=ROW($A$100), B$16, $A$19:$A$100)))

or

=XIRR(IFS(ROW(B$19:B$100)=ROW(B$19), B$15, ROW(B$19:B$100)=ROW(B$100), B$17, TRUE, B$19:B$100),
IFS(ROW($A$19:$A$100)=ROW($A$19), B$14, ROW($A$19:$A$100)=ROW($A$100), B$16, TRUE, $A$19:$A$100))

The formulas must be array-entered (press ctrl+shift+Enter) in some earlier versions of Excel (e.g. Excel 2010). I don't know about Excel 2016.

Be careful with the partial-absolute and absolute cell references.

The formulas assume that the initial cash flow in B15 is signed appropriately; typically, it is the opposite of the sign of the last net cash flow in B17.

The formulas also assume that unused cells in rows 20:99 are truly empty. That is, ISBLANK(B99) returns TRUE, if you have less data.

FYI, I confirmed that Excel XIRR still does not support range unions of the form (B$15, B$20:B$99, B$17).

Nonetheless, in this case, Excel XIRR is easier to use than Excel IRR because of the need to specify the non-contiguous last net cash-flow (B17).

My example:

Book 1.xlsx
	A	B	C	D	E	F	G	H
11
12					check data
13	IRR	10.82%			Cash Flows	Dates
14	Beg bal	5/13/2021			-100,000	5/13/2021
15		-100,000			9,000	6/13/2021
16	End Bal	5/13/2022			1,200	7/13/2021
17		50,000			1,800	8/13/2021
18					8,200	9/13/2021
19	Cash Flows				6,500	10/13/2021
20	6/13/2021	9,000			8,700	11/13/2021
21	7/13/2021	1,200			2,800	12/13/2021
22	8/13/2021	1,800			3,900	1/13/2022
23	9/13/2021	8,200			7,700	2/13/2022
24	10/13/2021	6,500			3,400	3/13/2022
25	11/13/2021	8,700			4,500	4/13/2022	IRR	same?
26	12/13/2021	2,800			50,000	5/13/2022	10.82%	TRUE
27	1/13/2022	3,900
28	2/13/2022	7,700
29	3/13/2022	3,400
30	4/13/2022	4,500
31
32
Sheet3

Formulas:
B13: see above
G26: =XIRR(E14:E26, F14:F26)
H26: =G26=B13
[/code

 

[john53]

Thanks for the guidance on the most appropriate way to post to the message board.

I used the first of the 2 solutions you suggested and it worked perfectly. That said I don't know why it works. I wasn't entirely certain whether a reply to your suggestions should have been a new posting and so I gambled. My apologies for a newbie's mistakes.