XIRR for visible range

[Sotu322]

I am using Excel 2010. U have filters in my data sheet. Need help as how to calculate XIRR on visible cells only.

 

[Domenic]

Assuming that A2:A10 contains the values, and B2:B10 contains the corresponding dates, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=XIRR(INDEX(A:A,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),ROW(A2:A10)*{1,1}))))),INDEX(B:B,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),ROW(A2:A10)*{1,1}))))),0.1)

Adjust the ranges accordingly.

Hope this helps!

 

[Sotu322]

Assuming that A2:A10 contains the values, and B2:B10 contains the corresponding dates, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=XIRR(INDEX(A:A,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),ROW(A2:A10)*{1,1}))))),INDEX(B:B,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),ROW(A2:A10)*{1,1}))))),0.1)

Adjust the ranges accordingly.

Hope this helps!

Thanks. Either i made an error or it didn't work. Pls help I am enclosing a sample sheet

 

[Sotu322]

Thanks. Either i made an error or it didn't work. Pls help I am enclosing a sample sheet

 

[Domenic]

Please post your data as text, or download the add-in xl2BB to post your Excel range, so that we can copy and paste the data into our worksheet, and so that we can actually work with the data. Also, make sure that you provide an example, and include the actual results that you expect.

 

[Sotu322]

Using Excel 2010
* Here I have the individual Investments and their individual XIRR
* * I would like to use the filters in Column A or B or C individually or in other combinations and find out the combined XIRR of the visible cells

mini sheet:

XIRR Sample Sheet query.xlsx
	A	B	C	D	E	F	G	H	I
1	Security	Asset Class	Sub Type	Outflow Date	Outflow Amt	Inflow Date	Inflow Amt	Time Invested in DAYS	XIRR
2	B	G	J	23-07-2018	11,016	18-08-2022	26,989	1487	24.60%
3	B	E	H	29-03-2019	16,405	18-08-2022	27,804	1238	16.83%
4	D	G	I	05-11-2018	15,325	18-08-2022	30,043	1382	19.46%
5	B	F	J	15-11-2019	12,616	18-08-2022	28,060	1007	33.61%
6	C	E	H	28-04-2018	16,049	18-08-2022	34,392	1573	19.35%
7	A	G	I	20-07-2018	23,706	18-08-2022	33,568	1490	8.89%
8	A	E	H	30-11-2016	13,971	18-08-2022	31,466	2087	15.26%
9	B	G	J	11-08-2016	15,497	18-08-2022	30,132	2198	11.67%
10	B	E	H	09-02-2017	22,960	18-08-2022	26,849	2016	2.87%
11	A	F	I	26-03-2018	13,159	18-08-2022	26,148	1606	16.89%
12	B	F	J	02-02-2019	22,659	18-08-2022	28,613	1293	6.81%
13	D	G	J	08-05-2015	11,317	18-08-2022	26,728	2659	12.52%
14	D	F	H	28-06-2019	14,995	18-08-2022	29,453	1147	23.96%
15	C	F	I	21-06-2016	15,118	18-08-2022	31,014	2249	12.37%
16	A	E	H	25-10-2019	22,242	18-08-2022	34,102	1028	16.39%
17	A	F	I	19-11-2017	11,273	18-08-2022	30,389	1733	23.23%
18	A	F	H	19-03-2016	11,362	18-08-2022	30,553	2343	16.66%
19	D	G	I	20-06-2017	13,540	18-08-2022	25,947	1885	13.42%
20	C	F	J	17-10-2015	17,720	18-08-2022	29,576	2497	7.78%
21	B	F	I	18-01-2018	22,518	18-08-2022	25,884	1673	3.09%
Sheet3

Cell Formulas
Range	Formula
H2:H21	H2	=(F2-D2)
I2:I21	I2	=IFERROR((G2/E2)^(365/H2)-1,"")

 

[Sotu322]

Please post your data as text, or download the add-in xl2BB to post your Excel range, so that we can copy and paste the data into our worksheet, and so that we can actually work with the data. Also, make sure that you provide an example, and include the actual results that you expect.

Posted. Please assist

 

[Domenic]

Based on the data that you posted, what should be the actual result that you expect?

 

[Sotu322]

Based on the data that you posted, what should be the actual result that you expect?

Column A, B and C have variables. I want to find XIRR using filters say for column A- "A", column B-"F", column C-"I". OR in any other combinations.
Say I have data in rows 11 & 17, I can find the XIRR by copying these cells in another sheet and use XIRR
but if doing in the same sheet
XIRR does not consider the selected range but all the cells in that range i.e from rows 11 to rows 17 all cells.

 

[Domenic]

Sorry, but what is the actual number that you expect as a result when it's filtered? Can you provide an actual example?