Calculate XIRR for a specific "item" from a table that's sorted by date

[vksy]

The following sheet contains multiple items and their respective cash flows in one table. All entries are sorted by item in order to make the XIRR function work.

XIRR.xlsx
	A	B	C	D	E	F	G
1	Item	Date	Amount		Item	XIRR
2	X	5/6/12	825		X	0.0843
3	X	7/22/12	-100		Y	0.0856
4	X	12/18/15	675		Z	0.0801
5	X	11/14/19	475
6	X	12/31/20	-3,000
7	Y	6/13/13	775
8	Y	2/1/14	-50
9	Y	3/28/14	600
10	Y	10/8/18	500
11	Y	12/31/20	-3,000
12	Z	8/2/13	575
13	Z	1/1/15	850
14	Z	4/13/16	-100
15	Z	9/5/17	600
16	Z	12/31/20	-3,000
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=XIRR(OFFSET(C$2,MATCH(E2,A$2:A$18,0)-1,0,COUNTIF(A$2:A$18,E2)),OFFSET(B$2,MATCH(E2,A$2:A$18,0)-1,0,COUNTIF(A$2:A$18,E2)))

There is just one problem. The layout isn't very user friendly. A row needs to be inserted for every future entry. This can be problematic in a big table with many cash flows. As shown below, the ideal solution would be to sort by date. For new entries, just find the bottom.

XIRR.xlsx
	A	B	C	D	E	F	G	H	I
1	Item	Date	Amount		Item	Date	Amount	XIRR
2	X	5/6/12	825		X	12/31/20	-3,000	?
3	X	7/22/12	-100		Y	12/31/20	-3,000	?
4	Y	6/13/13	775		Z	12/31/20	-3,000	?
5	Z	8/2/13	575		Overall	12/31/20	-9,000	?
6	Y	2/1/14	-50
7	Y	3/28/14	600
8	Z	1/1/15	850
9	X	12/18/15	675
10	Z	4/13/16	-100
11	Z	9/5/17	600
12	Y	10/8/18	500
13	X	11/14/19	475
14
15
16
17
18
19
20
Sheet2

And this is where I'm hitting a wall. Please help with the XIRR formula for this particular scenario. Many thanks.

 

[DRSteele]

This may not be too helpful, but do you have access to Excel 365? The new versions of Excel have a new function called FILTER that will work inside XIRR. I use it all the time for XIRR and it makes the formula simple.

 

[joeu2004]

I use Excel 2010, and I am not familiar with new functions, especially those that might be limited to Office 365.

Also, I am not a fan of OFFSET, since that is a "volatile" function that causes unnecessary recalculation of its cell and any cells that depend on it directly or indirectly.

The following is how I would implement the formulas in column H of Sheet2.

They assume that data might be in rows 2 through 1000 (or less).

For the "overall" calculation, they assume that B2:C2 contains the first cash flow for all items.

They rely on "helper" formulas in column I. Otherwise, replace references to column I with its formula multiple times in the formulas in column H.

The formulas in column J are only for "proof of concept"; they are not necessary. To that end, I added the following "overall" calculation in F5 of Sheet1:

=XIRR(C2:C16, B2:B16)

Book1
	A	B	C	D	E	F	G	H	I	J
1	Item	Date	Amount		Item	Date	Amount	XIRR	1st Rel Row#	same?
2	X	5/6/2012	825		X	12/31/2020	-3000	8.42797964811325%	1	TRUE
3	X	7/22/2012	-100		Y	12/31/2020	-3000	8.56033891439438%	3	TRUE
4	Y	6/13/2013	775		Z	12/31/2020	-3000	8.01262766122818%	4	TRUE
5	Z	8/2/2013	575		Overall	12/31/2020	-9000	8.33802372217178%		TRUE
6	Y	2/1/2014	-50
7	Y	3/28/2014	600
8	Z	1/1/2015	850
9	X	12/18/2015	675
10	Z	4/13/2016	-100
11	Z	9/5/2017	600
12	Y	10/8/2018	500
13	X	11/14/2019	475
Sheet2

Formulas:
H2:
{ =XIRR(IF(ROW(INDEX($A$2:$A$1000,I2):$A$1001)=ROW($A$1001), G2, IF(INDEX($A$2:$A$1000,I2):$A$1001=E2, INDEX($C$2:$C$1000,I2):$C$1001, 0)),
IF(ROW(INDEX($A$2:$A$1000,I2):$A$1001)=ROW($A$1001), F2, IF(INDEX($A$2:$A$1000,I2):$A$1001=E2, INDEX($B$2:$B$1000,I2):$B$1001, 0))) }
H5: { =XIRR(IF(ROW(C2:C1001)=ROW(C1001), G5, C2:C1001), IF(ROW(C2:C1001)=ROW(C1001), F5, B2:B1001)) }
I2: =MATCH(E2, $A$2:$A$1000, 0)
J2: =H2=Sheet1!F2

Note that the curly-braces indicate formulas that are array-entered. Do not type the curly-braces yourself. But press ctrl+shift+Enter instead of just Enter.

 

[DRSteele]

I will post the Excel 365 solution here so that others may benefit.

MrExcel posts18.xlsx
	A	B	C	D	E	F
1	Item	Date	Amount		Item	XIRR
2	X	5/6/2012	825		X	8.43%
3	X	7/22/2012	-100		Y	8.56%
4	X	12/18/2015	675		Z	8.01%
5	X	11/14/2019	475		overall	8.34%
6	X	12/31/2020	-3000
7	Y	6/13/2013	775
8	Y	2/1/2014	-50
9	Y	3/28/2014	600
10	Y	10/8/2018	500
11	Y	12/31/2020	-3000
12	Z	8/2/2013	575
13	Z	1/1/2015	850
14	Z	4/13/2016	-100
15	Z	9/5/2017	600
16	Z	12/31/2020	-3000
Sheet60

Cell Formulas
Range	Formula
F2:F4	F2	=XIRR(FILTER($C$2:$C$16,$A$2:$A$16=E2),FILTER($B$2:$B$16,$A$2:$A$16=E2))
F5	F5	=XIRR(C2:C16,B2:B16)

 

[joeu2004]

I will post the Excel 365 solution here so that others may benefit.

That implementation assumes that the last cash flow for each item is included in columns B:C, as in Sheet1.

But that is not the case in Sheet2.

I suspect that an implementation for Sheet2 is not too different. But since I cannot use FILTER in Excel 2010, I don't want to speculate.

Perhaps DRSteele can provide a similar implementation for Sheet2, not Sheet1.

Caveat on the use of FILTER even for Office 365 users, from the support webpage: ``This function is currently available to Microsoft 365 subscribers in Current Channel. It will be available to Microsoft 365 subscribers in Semi-Annual Enterprise Channel starting in July 2020``.

 

[DRSteele]

Actually, had those three -3000 entries not been in the list, it would have been necessary move them to the list because XIRR requires a positive and a negative, and the array cannot be appended.

I do this all the time for portfolios. I have a list of every transaction and date for each asset, including things like dividends, DRIPS, other income, etc. But crucially what needs to be in the list is a Buy transaction and then the current Market Price, one of which must be negative. It is far better to have the data in the form of an official Excel Table. The advantages there are that you can just add new transaction to the bottom and everything updates; it can even be sorted by Date, à la:

MrExcel posts18.xlsx
	A	B	C	D	E	F
1	Item	Date	Amount		Item	XIRR
2	X	5/6/2012	825		overall	8.34%
3	X	7/22/2012	-100		X	8.43%
4	Y	6/13/2013	775		Y	8.56%
5	Z	8/2/2013	575		Z	8.01%
6	Y	2/1/2014	-50
7	Y	3/28/2014	600
8	Z	1/1/2015	850
9	X	12/18/2015	675
10	Z	4/13/2016	-100
11	Z	9/5/2017	600
12	Y	10/8/2018	500
13	X	11/14/2019	475
14	X	12/31/2020	-3000
15	Y	12/31/2020	-3000
16	Z	12/31/2020	-3000
Sheet60

Cell Formulas
Range	Formula
E3:E5	E3	=UNIQUE(Table19[Item])
F2	F2	=XIRR(Table19[Amount],Table19[Date])
F3:F5	F3	=XIRR(FILTER(Table19[Amount],Table19[Item]=E3),FILTER(Table19[Date],Table19[Item]=E3))
Dynamic array formulas.

 

[joeu2004]

[... deleted by me ...]

 

[joeu2004]

Actually, had those three -3000 entries not been in the list, it would have been necessary move them to the list because XIRR requires a positive and a negative, and the array cannot be appended.

Initially, I disgreed with "would be necessary".

In my formula in H5 of Sheet2, I demonstrated how to "append" (effectively) to a fixed-length array of data.

However, DRSteele might be correct for his formula because (speculating) FILTER returns a variable-length array of just the data that qualifies. Right?

In any case, I agree that it does simplify things, even for my formuas, if the data for the "end" cash flows (e.g. 12/31/2020) are in the same columns (B:C) as the actual data.

They could be entered starting in rows 1001, allowing for actual data up to rows 1000, if we don't want to rely on Insert Row to enter new actual data.

Those formulas could reference the data in F:G, if the separation is desirable.

 

[DRSteele]

Well, you don't need to estimate the number of potential rows if you use an Excel Table. The whole thing ends up being a straightforward procedure in Excel 365. As I said, I do it all the time and it's simple as a pimple.

 

[joeu2004]

They [my formulas] assume that data might be in rows 2 through 1000 (or less).

@vksy.... I hope it is clear that although I allow for data in rows 2 to 1000, you are not required to populate all of those rows with data.

That should be clear from my example, which has only your original data in rows 2 to 13.

My point was: it is better to have limited ranges like A2:A1000 than full-column ranges like A:A, as many people write.

But at the same time, it is not necessary to limit those ranges to just the rows that have data (e.g. A2:A13).

If you feel that you might have more than "1000" (actually 999) transctions over time, change end references in my formulas to 2000, 5000, 10000 (plus one) or whatever.

These comments apply only to my formulas. They are in no way intended to "respond" to DRSteele's comments about his use of Excel tables.