XIRR Multiple Cashflows Ignoring Text Rows

[tannaroo]

Hi
I'm trying to calculate the XIRR of various stock purchases/sales that have header rows with text. But XIRR doesn't like that so was tryinh to use the INDIRECT function within XIRR but I just couldn't figure it out. Any help would be greatly appreciated.

I want the XIRR function (cell A2) to basically ignore any row that has text, so it only identifies the dates (column A) with the corresponding cashflow (olumn F)

stock purchase example.xlsx
	A	B	C	D	E	F
2	#VALUE!
3	STOCK A	TICKER A	SECTOR
4	Date	Bought/Sold	Number	Price		Amount (Base)
5	10-Jun-22	B	100	48.00		(4,800.00)
6	13-Jun-22	B	100	46.00		(4,600.00)
7	14-Jun-22	B	100	45.00		(4,500.00)
8	14-Jul-22	B	50	43.00		(2,150.00)
9	31-Jul-22	S	(300)	40.00		12,000.00
10	27-Sep-22	B	100	43.00		(4,300.00)
11	17-Nov-22		150	60.00		9,000.00
12
13	STOCK B	TICKER B	SECTOR
14	Date	Bought/Sold	Number	Price		Amount (Base)
15	10-Jun-22	B	1,000	6.00		(6,000.00)
16	13-Jun-22	B	100	4.00		(400.00)
17	14-Jun-22	B	100	3.00		(300.00)
18	14-Jul-22	B	50	9.00		(450.00)
19	17-Nov-22		1,250	12.00		15,000.00
Sheet1

Cell Formulas
Range	Formula
A2	A2	=XIRR(F5:F20, A5:A20)
A11,A19	A11	=TODAY()
C11	C11	=SUM(C5:C10)
F5:F10,F15:F18	F5	=ROUND(((-C5 * D5) + E5), 2)
F11,F19	F11	=-ROUND(((-C11 * D11) + E11), 2)
C19	C19	=SUM(C15:C18)

 

[Domenic]

Try the following...

tannaroo.xlsm
	A	B	C	D	E	F
1
2	1.630387509
3	STOCK A	TICKER A	SECTOR
4	Date	Bought/Sold	Number	Price		Amount (Base)
5	44722	B	100	48		-4800
6	44725	B	100	46		-4600
7	44726	B	100	45		-4500
8	44756	B	50	43		-2150
9	44773	S	-300	40		12000
10	44831	B	100	43		-4300
11	11/19/2022		150	60		9000
12
13	STOCK B	TICKER B	SECTOR
14	Date	Bought/Sold	Number	Price		Amount (Base)
15	44722	B	1000	6		-6000
16	44725	B	100	4		-400
17	44726	B	100	3		-300
18	44756	B	50	9		-450
19	11/19/2022		1250	12		15000
Sheet1

Cell Formulas
Range	Formula
A2	A2	=XIRR(INDEX(F5:F19,N(IF(1,MODE.MULT(IF(ISNUMBER(A5:A19),ROW(A5:A19)-ROW(A5)+{1,1}))))),INDEX(A5:A19,N(IF(1,MODE.MULT(IF(ISNUMBER(A5:A19),ROW(A5:A19)-ROW(A5)+{1,1}))))))
A11,A19	A11	=TODAY()
C11	C11	=SUM(C5:C10)
F5:F10,F15:F18	F5	=ROUND(((-C5 * D5) + E5), 2)
F11,F19	F11	=-ROUND(((-C11 * D11) + E11), 2)
C19	C19	=SUM(C15:C18)

Hope this helps!

 

[tannaroo]

For some reason the formula is not working for me. maybe i need an excel add-in of some type or my version of excel i snot supported?

 

[tannaroo]

My bad... Got it working.
Many thanks Domenic

 

[tannaroo]

Hi all,

I got help previously with calculating XIRR on multiple rows. I wanted to take this one further to see if it was possible to calculate XIRR by start dates and end dates?

That is, I'd like to work out IRRs by year (Jan 1 to 31 Dec)

stock purchase example_2.xlsx
	A	B	C	D	E	F
2	31%
3	STOCK A	TICKER A	SECTOR
4	Date	Bought/Sold	Number	Price		Amount (Base)
5	10-Jun-21	B	100	48.00		(4,800.00)
6	13-Jun-21	B	100	46.00		(4,600.00)
7	31-Dec-21	B	100	45.00		(4,500.00)
8	14-Jul-22	B	50	43.00		(2,150.00)
9	31-Jul-22	S	(300)	40.00		12,000.00
10	31-Dec-22	B	100	43.00		(4,300.00)
11	10-May-23		150	60.00		9,000.00
12
13	STOCK B	TICKER B	SECTOR
14	Date	Bought/Sold	Number	Price		Amount (Base)
15	10-Jun-22	B	1,000	6.00		(6,000.00)
16	13-Jun-22	B	100	4.00		(400.00)
17	31-Dec-22	B	100	3.00		(300.00)
18	05-Feb-23	B	50	9.00		(450.00)
19	10-May-23		1,250	12.00		15,000.00
Sheet1

Cell Formulas
Range	Formula
A2	A2	=XIRR(INDEX(F5:F19,N(IF(1,MODE.MULT(IF(ISNUMBER(A5:A19),ROW(A5:A19)-ROW(A5)+{1,1}))))),INDEX(A5:A19,N(IF(1,MODE.MULT(IF(ISNUMBER(A5:A19),ROW(A5:A19)-ROW(A5)+{1,1}))))))
A11,A19	A11	=TODAY()
C11	C11	=SUM(C5:C10)
F15:F18,F5:F10	F5	=ROUND(((-C5 * D5) + E5), 2)
F11,F19	F11	=-ROUND(((-C11 * D11) + E11), 2)
C19	C19	=SUM(C15:C18)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Domenic]

First of all, I see that I previously missed the fact that your date column contains today's date with the current stock value. As such, my formula incorrectly includes it in the calculation. Now, while the formula can be amended to exclude rows containing today's date, I would suggest that you instead return the date as a text value so that it doesn't get included in the calculation. Or, better still, have it labelled "As of 5/10/2023". This way the value is a text value, and it's clear that it's not going to be part of the calculation. Here's an example...

tannaroo v2.xlsm
	A	B	C	D	E	F
1
2	2.98023E-09
3	STOCK A	TICKER A	SECTOR
4	Date	Bought/Sold	Number	Price		Amount (Base)
5	6/10/2021	B	100	48		($4,800.00)
6	6/13/2021	B	100	46		($4,600.00)
7	12/31/2021	B	100	45		($4,500.00)
8	7/14/2022	B	50	43		($2,150.00)
9	7/31/2022	S	-300	40		$12,000.00
10	12/31/2022	B	100	43		($4,300.00)
11	As of 5/10/2023		150	60		$9,000.00
12
13	STOCK B	TICKER B	SECTOR
14	Date	Bought/Sold	Number	Price		Amount (Base)
15	6/10/2022	B	1,000	6		($6,000.00)
16	6/13/2022	B	100	4		($400.00)
17	12/31/2022	B	100	3		($300.00)
18	2/5/2023	B	50	9		($450.00)
19	As of 5/10/2023		1,250	12		$15,000.00
Sheet1

Cell Formulas
Range	Formula
A2	A2	=XIRR(INDEX(F5:F19,N(IF(1,MODE.MULT(IF(ISNUMBER(A5:A19),ROW(A5:A19)-ROW(A5)+{1,1}))))),INDEX(A5:A19,N(IF(1,MODE.MULT(IF(ISNUMBER(A5:A19),ROW(A5:A19)-ROW(A5)+{1,1}))))))
A11,A19	A11	="As of "&TEXT(TODAY(),"m/d/yyyy")
C11	C11	=SUM(C5:C10)
F5:F10,F15:F18	F5	=ROUND(((-C5 * D5) + E5), 2)
F11,F19	F11	=-ROUND(((-C11 * D11) + E11), 2)
C19	C19	=SUM(C15:C18)

Then, to amend the formula so that it takes into consideration a start date and end date, try...

tannaroo v2.xlsm
	A	B	C	D	E	F
1
2	#NUM!	1/1/2022	12/31/2022
3	STOCK A	TICKER A	SECTOR
4	Date	Bought/Sold	Number	Price		Amount (Base)
5	6/10/2021	B	100	48		($4,800.00)
6	6/13/2021	B	100	46		($4,600.00)
7	12/31/2021	B	100	45		($4,500.00)
8	7/14/2022	B	50	43		($2,150.00)
9	7/31/2022	S	-300	40		$12,000.00
10	12/31/2022	B	100	43		($4,300.00)
11	As of 5/10/2023		150	60		$9,000.00
12
13	STOCK B	TICKER B	SECTOR
14	Date	Bought/Sold	Number	Price		Amount (Base)
15	6/10/2022	B	1,000	6		($6,000.00)
16	6/13/2022	B	100	4		($400.00)
17	12/31/2022	B	100	3		($300.00)
18	2/5/2023	B	50	9		($450.00)
19	As of 5/10/2023		1,250	12		$15,000.00
Sheet2

Cell Formulas
Range	Formula
A2	A2	=XIRR(INDEX(F5:F19,N(IF(1,MODE.MULT(IF(ISNUMBER(A5:A19),IF(A5:A19>=B2,IF(A5:A19<=C2,ROW(A5:A19)-ROW(A5)+{1,1}))))))),INDEX(A5:A19,N(IF(1,MODE.MULT(IF(ISNUMBER(A5:A19),IF(A5:A19>=B2,IF(A5:A19<=C2,ROW(A5:A19)-ROW(A5)+{1,1}))))))))
A11,A19	A11	="As of "&TEXT(TODAY(),"m/d/yyyy")
C11	C11	=SUM(C5:C10)
F5:F10,F15:F18	F5	=ROUND(((-C5 * D5) + E5), 2)
F11,F19	F11	=-ROUND(((-C11 * D11) + E11), 2)
C19	C19	=SUM(C15:C18)
Press CTRL+SHIFT+ENTER to enter array formulas.

I'm assuming that since there's at least one positive and one negative value, the reason it returns #NUM! is that it's unable to find a result that works after 'X' number of tries. But I'll leave it for you to sort out.

Hope this helps!

 

[tannaroo]

Thank you very much Domenic - that is awesome!

Regarding your comment about amending your initial formula to 'text out today's date', unless I am missing something with the XIRR function, I want todays date with the current market price included in the XIRR function. I'm trying to measure the market-to-market value with the current XIRR, notwithstanding it may provide some high/loss numbers as its not annualised.

Was there a specific reason why you were thinking it should excluded?

 

[Domenic]

Oh I see, so you actually want it included in the calculation. I thought since it's not actually a cash flow, it shouldn't be included. I'm not a statistician, so I could be wrong.

But then when the calculation takes into consideration a start date and an end date, such as 1/1/2022 and 12/31/2022, today's current stock value won't be included.

So I'm a bit confused.

 

[tannaroo]

Oh I see, so you actually want it included in the calculation. I thought since it's not actually a cash flow, it shouldn't be included. I'm not a statistician, so I could be wrong.

But then when the calculation takes into consideration a start date and an end date, such as 1/1/2022 and 12/31/2022, today's current stock value won't be included.

So I'm a bit confused.

You are correct - its not a cashflow item per se but I treat as a 'liquidation' value. I'm amending the formula to suit my needs but your help has been immensely appreciated.

 

[Domenic]

That's great, glad I could help.

Cheers!