Using the XIRR formula with non contiguous cells

[Gold Dutchy]

Hi everyone,

I have been stuck on an excel problem for the past few days. I have checked other posts but can not find the answer. My problem is the following:

I want to calculate the IRR of a range of cashflows using XIRR (can not use IRR since the cashflows are not evenly distributed). I would like to add a final cashflow which is not contiguous to the range of the other cashflows. So in my excel I would have:

Column C: Row 400 - Row 600: dates of the cashflows
Column D: Row 400 - Row 600: the number of the cashflows

Column C: Row 650: date of a single cashflow
Column D: Row 650: number of a single cashflow

I tried the following formula but get a value error:

=XIRR((D400:D600,D650),(C400:C600,C650))

Does anyone have an idea on how to solve my problem? It would be much appreciated!

 

[JamesCanale]

Don't know what the XIRR function is doing. But I used the filter command to filter out the range. I assumed that there were blanks in B601 to B649, and this would filter them out. But if that isn't the case, you can also filter out your data set by filtering out the dates in C601 to C649. Really, is there way to filter out what is happening in 601-649?

MrExcelPlayground2.xlsx
	J	K	L	M
18	1/1/2008	-10000
19	3/1/2008	2750		86%
20	10/30/2008	4250
21	2/15/2009	3250
22	4/1/2009	2750
23	5/2/2009	3200
24
25
26	10/1/2010	3800
Sheet23

Cell Formulas
Range	Formula
M19	M19	=XIRR(INDEX(FILTER(J18:K26,K18:K26<>"",""),,2),INDEX(FILTER(J18:K26,K18:K26<>"",""),,1))
J23	J23	=J22+31

 

[joeu2004]

I tried the following formula but get a value error:
=XIRR((D400:D600,D650),(C400:C600,C650))

Right. Sadly, XIRR does not support the range union feature, as Excel IRR does.

(FYI, another reason why you could not use Excel IRR with a range union is: Excel IRR would treat the D650 as 250 periods from D400.)

Array-enter (press ctrl+shift+Enter) the following formula:

=XIRR(IF(ROW(D400:D601)=ROW(D601), D650, D400:D601),
IF(ROW(C400:C601)=ROW(C601), C650, C400:C601))

(Some recent versions of Excel do not require that we explicity array-enter the formula.)

This is demonstrated by the example below.

Row 601 in the formula is a "dummy row" -- a placeholder for the data that is actually in row 650. C601:D601 is not actually accessed by the formula; so their values can be anything.

Presumably, C400:D400 contains the initial balance, properly signed. XIRR does not permit it to be zero or empty.

If C400:D400 are zero or empty, XIRR might appear to return 0.00% due to formatting. But it actually returns +/-2.98E-09, which should be treated as an Excel error like #NUM.

And presumably, any unused cells in C401:D600 are empty or zero. XIRR does not permit them to be the null string, for example, which appears to be blank.

Example:

Book1
	A	B	C	D	E	F
1
2	check data
3
4		IRR	14.6506816148758%
5
6	1/1/2021	-100,000
7	2/1/2021	12,345
8	3/1/2021	56,789
9	4/1/2021	13,579
10	5/1/2021	20,000
Sheet1

Book1
	A	B	C	D	E	F
397						same?
398				IRR	14.6506816148758%	TRUE
399
400			1/1/2021	-100,000
401
402			2/1/2021	12,345
403
404			3/1/2021	56,789
405
406			4/1/2021	13,579
407
Sheet1

Book1
	A	B	C	D	E	F
649
650			5/1/2021	20,000
Sheet1

Formulas:
C4: =XIRR(B6:B10, A6:A10)
E398: =XIRR(IF(ROW(D400:D601)=ROW(D601), D650, D400:D601),
IF(ROW(C400:C601)=ROW(C601), C650, C400:C601))

PS.... Presumably, there might be unrelated data between C600:D600 and C650:D650. Otherwise, we could simply write =XIRR(D400:D650, C400:C650) as long as C400:C400 and C401:D650 obey the presumptions stated above. In fact, the data now in C650 and D650 could be in any cells, making the appropriate changes in the formula in E398.

 

[mgk2056]

I was adding the current value ( or cashFlow ) to the bottom of the transaction list of values and dates for XIRR and then using the filter function to copy alongside in the correct format ie starting iwth a negative value being the initial investment and ending with the current positive value and it worked but cumbersome so;
I would now fix your example by pointing XIRR((D398:D600),(C398:C600))
C399 would contain =C650
C398 would contain =C399
D399 would contain =D650
D398 would contain = -1
The last step is the trick to give XIRR a negative number to start. ( Seems weird because the associated date is not the earliest )
This allows yours transaction list assuming it is dynamic range from 400 to something less than 650.
If I have missed something please advise.

 

[Froggy1967]

Right. Sadly, XIRR does not support the range union feature, as Excel IRR does.

(FYI, another reason why you could not use Excel IRR with a range union is: Excel IRR would treat the D650 as 250 periods from D400.)

Array-enter (press ctrl+shift+Enter) the following formula:

=XIRR(IF(ROW(D400:D601)=ROW(D601), D650, D400:D601),
IF(ROW(C400:C601)=ROW(C601), C650, C400:C601))

(Some recent versions of Excel do not require that we explicity array-enter the formula.)

This is demonstrated by the example below.

Row 601 in the formula is a "dummy row" -- a placeholder for the data that is actually in row 650. C601:D601 is not actually accessed by the formula; so their values can be anything.

Presumably, C400:D400 contains the initial balance, properly signed. XIRR does not permit it to be zero or empty.

If C400:D400 are zero or empty, XIRR might appear to return 0.00% due to formatting. But it actually returns +/-2.98E-09, which should be treated as an Excel error like #NUM.

And presumably, any unused cells in C401:D600 are empty or zero. XIRR does not permit them to be the null string, for example, which appears to be blank.

Example:

Book1
	A	B	C	D	E	F
1
2	check data
3
4		IRR	14.6506816148758%
5
6	1/1/2021	-100,000
7	2/1/2021	12,345
8	3/1/2021	56,789
9	4/1/2021	13,579
10	5/1/2021	20,000
Sheet1

Book1
	A	B	C	D	E	F
397						same?
398				IRR	14.6506816148758%	TRUE
399
400			1/1/2021	-100,000
401
402			2/1/2021	12,345
403
404			3/1/2021	56,789
405
406			4/1/2021	13,579
407
Sheet1

Book1
	A	B	C	D	E	F
649
650			5/1/2021	20,000
Sheet1

Formulas:
C4: =XIRR(B6:B10, A6:A10)
E398: =XIRR(IF(ROW(D400:D601)=ROW(D601), D650, D400:D601),
IF(ROW(C400:C601)=ROW(C601), C650, C400:C601))

PS.... Presumably, there might be unrelated data between C600:D600 and C650:D650. Otherwise, we could simply write =XIRR(D400:D650, C400:C650) as long as C400:C400 and C401:D650 obey the presumptions stated above. In fact, the data now in C650 and D650 could be in any cells, making the appropriate changes in the formula in E398.

Great Solution. It works very well.

 

[Froggy1967]

Right. Sadly, XIRR does not support the range union feature, as Excel IRR does.

(FYI, another reason why you could not use Excel IRR with a range union is: Excel IRR would treat the D650 as 250 periods from D400.)

Array-enter (press ctrl+shift+Enter) the following formula:

=XIRR(IF(ROW(D400:D601)=ROW(D601), D650, D400:D601),
IF(ROW(C400:C601)=ROW(C601), C650, C400:C601))

(Some recent versions of Excel do not require that we explicity array-enter the formula.)

This is demonstrated by the example below.

Row 601 in the formula is a "dummy row" -- a placeholder for the data that is actually in row 650. C601:D601 is not actually accessed by the formula; so their values can be anything.

Presumably, C400:D400 contains the initial balance, properly signed. XIRR does not permit it to be zero or empty.

If C400:D400 are zero or empty, XIRR might appear to return 0.00% due to formatting. But it actually returns +/-2.98E-09, which should be treated as an Excel error like #NUM.

And presumably, any unused cells in C401:D600 are empty or zero. XIRR does not permit them to be the null string, for example, which appears to be blank.

Example:

Book1
	A	B	C	D	E	F
1
2	check data
3
4		IRR	14.6506816148758%
5
6	1/1/2021	-100,000
7	2/1/2021	12,345
8	3/1/2021	56,789
9	4/1/2021	13,579
10	5/1/2021	20,000
Sheet1

Book1
	A	B	C	D	E	F
397						same?
398				IRR	14.6506816148758%	TRUE
399
400			1/1/2021	-100,000
401
402			2/1/2021	12,345
403
404			3/1/2021	56,789
405
406			4/1/2021	13,579
407
Sheet1

Book1
	A	B	C	D	E	F
649
650			5/1/2021	20,000
Sheet1

Formulas:
C4: =XIRR(B6:B10, A6:A10)
E398: =XIRR(IF(ROW(D400:D601)=ROW(D601), D650, D400:D601),
IF(ROW(C400:C601)=ROW(C601), C650, C400:C601))

PS.... Presumably, there might be unrelated data between C600:D600 and C650:D650. Otherwise, we could simply write =XIRR(D400:D650, C400:C650) as long as C400:C400 and C401:D650 obey the presumptions stated above. In fact, the data now in C650 and D650 could be in any cells, making the appropriate changes in the formula in E398.

Fantastic solution... works very well.