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]

After searching Google, I found the following article that explains how to calculate XIRR by year. Maybe you'll find it helpful.

Using XIRR to Calculate Returns

The XIRR function is a useful tool to calculate investment returns. The XIRR function can be found in Excel or Google Sheets.

www.biglawinvestor.com

Cheers!

 

[tannaroo]

Thanks - thats pretty much what I have.

 

[Domenic]

That's great, cheers!