Dynamic XIRR Function -- I'm Lost

[zpasanen]

Hello,
I have a series of cash outflows and inflows over 144 months. The first 24 months are cash outflows - the next 120 month cash inflows. My sheet is set up as follows

Row 1 - Numbered 1-144
Row 2 - Months (Beginning 8/1/18 thru 144 months)
Row 3 - Cash flows

Currently my sheet calculates the XIRR thru the entire 144 months, but if I want to exit the project earlier (say month 36) id like to be able to change the sale date (on another sheet) and have the XIRR detect the sale month and calculate it for that new period. Any thoughts would be more than appreciated.

Thanks in advance.

 

[sheetspread]

Something like:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14
2	8/1/2018	9/1/2018	10/1/2018	11/1/2018	12/1/2018	1/1/2019	2/1/2019	3/1/2019	4/1/2019	5/1/2019	6/1/2019	7/1/2019	8/1/2019	9/1/2019
3	-15000	2200	400	1000	1800	2700	500	1900	900	2700	3000	600	2100	1900
4
5
6	Sale Date	IRR
7	8	-0.6322
Sheet1

Cell Formulas
Range	Formula
B7	=XIRR(OFFSET($A$3:$N$3,,,,A7),OFFSET($A$2:$N$2,,,,A7))

Match() could find the date sequences (apparently not needed here)

 

[zpasanen]

Amazing. Worked like a charm! Thank you!!!