XIRR for visible range

S

Sotu322

New Member
Joined
Aug 20, 2022
Messages
14
Office Version
  1. 2010
Platform
  1. Windows
I am using Excel 2010. U have filters in my data sheet. Need help as how to calculate XIRR on visible cells only.
 
Sort by date Sort by votes
XIRR Sample Sheet query.xlsx
ABCDEFGHI
1SecurityAsset ClassSub TypeOutflow DateOutflow AmtInflow DateInflow AmtTime Invested in DAYSXIRR
2BGJ23-07-201811,01618-08-202226,989148724.60%
3BEH29-03-201916,40518-08-202227,804123816.83%
4DGI05-11-201815,32518-08-202230,043138219.46%
5BFJ15-11-201912,61618-08-202228,060100733.61%
6CEH28-04-201816,04918-08-202234,392157319.35%
7AGI20-07-201823,70618-08-202233,56814908.89%
8AEH30-11-201613,97118-08-202231,466208715.26%
9BGJ11-08-201615,49718-08-202230,132219811.67%
10BEH09-02-201722,96018-08-202226,84920162.87%
11AFI26-03-201813,15918-08-202226,148160616.89%
12BFJ02-02-201922,65918-08-202228,61312936.81%
13DGJ08-05-201511,31718-08-202226,728265912.52%
14DFH28-06-201914,99518-08-202229,453114723.96%
15CFI21-06-201615,11818-08-202231,014224912.37%
16AEH25-10-201922,24218-08-202234,102102816.39%
17AFI19-11-201711,27318-08-202230,389173323.23%
18AFH19-03-201611,36218-08-202230,553234316.66%
19DGI20-06-201713,54018-08-202225,947188513.42%
20CFJ17-10-201517,72018-08-202229,57624977.78%
21BFI18-01-201822,51818-08-202225,88416733.09%
22TOTAL INFLOW18-08-2022-5,87,710
23
24
25
26
27example XIRR FOR FILTERED CELLS
28SecurityAsset ClassSub TypeOutflow DateOutflow AmtInflow DateInflow AmtTime Invested in DAYSXIRR
29AFI26-03-201813,15918-08-202226,148160616.89%
30AFI19-11-201711,27318-08-202230,389173323.23%
3118-08-2022-56,537
32
33XIRR20.18%***want this result
34
351do not get correct XIRR if it is applied on the main sheet using filters
362want XIRR for the selected range
373XIRR is not considering selected range but considers all the cells in the range
384XIRR works when the data is copied separately like example above which is very tedious
Sheet3
Cell Formulas
RangeFormula
H2:H21H2=(F2-D2)
I2:I21I2=IFERROR((G2/E2)^(365/H2)-1,"")
E22E22=-SUBTOTAL(9,G2:G21)
E31E31=-SUM(G29:G30)
B33B33=XIRR(E29:E31,D29:D31)
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
In that case, assuming that the "TOTAL INFLOW" row is always the last row, you'll need to amend the formula as follows...

Excel Formula: 
=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)

...which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Before the filter is applied

sotu.xlsm
ABCDEFGHIJKL
1SecurityAsset ClassSub TypeOutflow DateOutflow AmtInflow DateInflow AmtTime Invested in DAYSXIRRXIRR Result13.38%
2BGJ23-07-201811,01618-08-202226,989148724.60%
3BEH29-03-201916,40518-08-202227,804123816.83%
4DGI05-11-201815,32518-08-202230,043138219.46%
5BFJ15-11-201912,61618-08-202228,060100733.61%
6CEH28-04-201816,04918-08-202234,392157319.35%
7AGI20-07-201823,70618-08-202233,56814908.89%
8AEH30-11-201613,97118-08-202231,466208715.26%
9BGJ11-08-201615,49718-08-202230,132219811.67%
10BEH09-02-201722,96018-08-202226,84920162.87%
11AFI26-03-201813,15918-08-202226,148160616.89%
12BFJ02-02-201922,65918-08-202228,61312936.81%
13DGJ08-05-201511,31718-08-202226,728265912.52%
14DFH28-06-201914,99518-08-202229,453114723.96%
15CFI21-06-201615,11818-08-202231,014224912.37%
16AEH25-10-201922,24218-08-202234,102102816.39%
17AFI19-11-201711,27318-08-202230,389173323.23%
18AFH19-03-201611,36218-08-202230,553234316.66%
19DGI20-06-201713,54018-08-202225,947188513.42%
20CFJ17-10-201517,72018-08-202229,57624977.78%
21BFI18-01-201822,51818-08-202225,88416733.09%
22TOTAL INFLOW18-08-2022-587,710
Sheet1
Cell Formulas
RangeFormula
L1L1=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)
H2:H21H2=(F2-D2)
I2:I21I2=IFERROR((G2/E2)^(365/H2)-1,"")
E22E22=-SUBTOTAL(9,G2:G21)
Press CTRL+SHIFT+ENTER to enter array formulas.


After the filter is applied

sotu.xlsm
ABCDEFGHIJKL
1SecurityAsset ClassSub TypeOutflow DateOutflow AmtInflow DateInflow AmtTime Invested in DAYSXIRRXIRR Result20.18%
11AFI26-03-201813,15918-08-202226,148160616.89%
17AFI19-11-201711,27318-08-202230,389173323.23%
Sheet1
Cell Formulas
RangeFormula
L1L1=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)
H11,H17H11=(F11-D11)
I11,I17I11=IFERROR((G11/E11)^(365/H11)-1,"")
Press CTRL+SHIFT+ENTER to enter array formulas.


Hope this helps!
 
Upvote 0
Solution
Domenic said:
In that case, assuming that the "TOTAL INFLOW" row is always the last row, you'll need to amend the formula as follows...

Excel Formula: 
=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)

...which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Before the filter is applied

sotu.xlsm
ABCDEFGHIJKL
1SecurityAsset ClassSub TypeOutflow DateOutflow AmtInflow DateInflow AmtTime Invested in DAYSXIRRXIRR Result13.38%
2BGJ23-07-201811,01618-08-202226,989148724.60%
3BEH29-03-201916,40518-08-202227,804123816.83%
4DGI05-11-201815,32518-08-202230,043138219.46%
5BFJ15-11-201912,61618-08-202228,060100733.61%
6CEH28-04-201816,04918-08-202234,392157319.35%
7AGI20-07-201823,70618-08-202233,56814908.89%
8AEH30-11-201613,97118-08-202231,466208715.26%
9BGJ11-08-201615,49718-08-202230,132219811.67%
10BEH09-02-201722,96018-08-202226,84920162.87%
11AFI26-03-201813,15918-08-202226,148160616.89%
12BFJ02-02-201922,65918-08-202228,61312936.81%
13DGJ08-05-201511,31718-08-202226,728265912.52%
14DFH28-06-201914,99518-08-202229,453114723.96%
15CFI21-06-201615,11818-08-202231,014224912.37%
16AEH25-10-201922,24218-08-202234,102102816.39%
17AFI19-11-201711,27318-08-202230,389173323.23%
18AFH19-03-201611,36218-08-202230,553234316.66%
19DGI20-06-201713,54018-08-202225,947188513.42%
20CFJ17-10-201517,72018-08-202229,57624977.78%
21BFI18-01-201822,51818-08-202225,88416733.09%
22TOTAL INFLOW18-08-2022-587,710
Sheet1
Cell Formulas
RangeFormula
L1L1=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)
H2:H21H2=(F2-D2)
I2:I21I2=IFERROR((G2/E2)^(365/H2)-1,"")
E22E22=-SUBTOTAL(9,G2:G21)
Press CTRL+SHIFT+ENTER to enter array formulas.


After the filter is applied

sotu.xlsm
ABCDEFGHIJKL
1SecurityAsset ClassSub TypeOutflow DateOutflow AmtInflow DateInflow AmtTime Invested in DAYSXIRRXIRR Result20.18%
11AFI26-03-201813,15918-08-202226,148160616.89%
17AFI19-11-201711,27318-08-202230,389173323.23%
Sheet1
Cell Formulas
RangeFormula
L1L1=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)
H11,H17H11=(F11-D11)
I11,I17I11=IFERROR((G11/E11)^(365/H11)-1,"")
Press CTRL+SHIFT+ENTER to enter array formulas.


Hope this helps!
Click to expand...
Thanks a lot. working
Domenic said:
In that case, assuming that the "TOTAL INFLOW" row is always the last row, you'll need to amend the formula as follows...

Excel Formula: 
=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)

...which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Before the filter is applied

sotu.xlsm
ABCDEFGHIJKL
1SecurityAsset ClassSub TypeOutflow DateOutflow AmtInflow DateInflow AmtTime Invested in DAYSXIRRXIRR Result13.38%
2BGJ23-07-201811,01618-08-202226,989148724.60%
3BEH29-03-201916,40518-08-202227,804123816.83%
4DGI05-11-201815,32518-08-202230,043138219.46%
5BFJ15-11-201912,61618-08-202228,060100733.61%
6CEH28-04-201816,04918-08-202234,392157319.35%
7AGI20-07-201823,70618-08-202233,56814908.89%
8AEH30-11-201613,97118-08-202231,466208715.26%
9BGJ11-08-201615,49718-08-202230,132219811.67%
10BEH09-02-201722,96018-08-202226,84920162.87%
11AFI26-03-201813,15918-08-202226,148160616.89%
12BFJ02-02-201922,65918-08-202228,61312936.81%
13DGJ08-05-201511,31718-08-202226,728265912.52%
14DFH28-06-201914,99518-08-202229,453114723.96%
15CFI21-06-201615,11818-08-202231,014224912.37%
16AEH25-10-201922,24218-08-202234,102102816.39%
17AFI19-11-201711,27318-08-202230,389173323.23%
18AFH19-03-201611,36218-08-202230,553234316.66%
19DGI20-06-201713,54018-08-202225,947188513.42%
20CFJ17-10-201517,72018-08-202229,57624977.78%
21BFI18-01-201822,51818-08-202225,88416733.09%
22TOTAL INFLOW18-08-2022-587,710
Sheet1
Cell Formulas
RangeFormula
L1L1=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)
H2:H21H2=(F2-D2)
I2:I21I2=IFERROR((G2/E2)^(365/H2)-1,"")
E22E22=-SUBTOTAL(9,G2:G21)
Press CTRL+SHIFT+ENTER to enter array formulas.


After the filter is applied

sotu.xlsm
ABCDEFGHIJKL
1SecurityAsset ClassSub TypeOutflow DateOutflow AmtInflow DateInflow AmtTime Invested in DAYSXIRRXIRR Result20.18%
11AFI26-03-201813,15918-08-202226,148160616.89%
17AFI19-11-201711,27318-08-202230,389173323.23%
Sheet1
Cell Formulas
RangeFormula
L1L1=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)
H11,H17H11=(F11-D11)
I11,I17I11=IFERROR((G11/E11)^(365/H11)-1,"")
Press CTRL+SHIFT+ENTER to enter array formulas.


Hope this helps!
Click to expand...

Domenic said:
In that case, assuming that the "TOTAL INFLOW" row is always the last row, you'll need to amend the formula as follows...

Excel Formula: 
=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)

...which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Before the filter is applied

sotu.xlsm
ABCDEFGHIJKL
1SecurityAsset ClassSub TypeOutflow DateOutflow AmtInflow DateInflow AmtTime Invested in DAYSXIRRXIRR Result13.38%
2BGJ23-07-201811,01618-08-202226,989148724.60%
3BEH29-03-201916,40518-08-202227,804123816.83%
4DGI05-11-201815,32518-08-202230,043138219.46%
5BFJ15-11-201912,61618-08-202228,060100733.61%
6CEH28-04-201816,04918-08-202234,392157319.35%
7AGI20-07-201823,70618-08-202233,56814908.89%
8AEH30-11-201613,97118-08-202231,466208715.26%
9BGJ11-08-201615,49718-08-202230,132219811.67%
10BEH09-02-201722,96018-08-202226,84920162.87%
11AFI26-03-201813,15918-08-202226,148160616.89%
12BFJ02-02-201922,65918-08-202228,61312936.81%
13DGJ08-05-201511,31718-08-202226,728265912.52%
14DFH28-06-201914,99518-08-202229,453114723.96%
15CFI21-06-201615,11818-08-202231,014224912.37%
16AEH25-10-201922,24218-08-202234,102102816.39%
17AFI19-11-201711,27318-08-202230,389173323.23%
18AFH19-03-201611,36218-08-202230,553234316.66%
19DGI20-06-201713,54018-08-202225,947188513.42%
20CFJ17-10-201517,72018-08-202229,57624977.78%
21BFI18-01-201822,51818-08-202225,88416733.09%
22TOTAL INFLOW18-08-2022-587,710
Sheet1
Cell Formulas
RangeFormula
L1L1=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)
H2:H21H2=(F2-D2)
I2:I21I2=IFERROR((G2/E2)^(365/H2)-1,"")
E22E22=-SUBTOTAL(9,G2:G21)
Press CTRL+SHIFT+ENTER to enter array formulas.


After the filter is applied

sotu.xlsm
ABCDEFGHIJKL
1SecurityAsset ClassSub TypeOutflow DateOutflow AmtInflow DateInflow AmtTime Invested in DAYSXIRRXIRR Result20.18%
11AFI26-03-201813,15918-08-202226,148160616.89%
17AFI19-11-201711,27318-08-202230,389173323.23%
Sheet1
Cell Formulas
RangeFormula
L1L1=XIRR(INDEX(E:E,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),INDEX(D:D,N(IF(1,MODE.MULT(IF(SUBTOTAL(3,OFFSET(E2:E22,ROW(E2:E22)-ROW(E2),0,1))+IF(ROW(E2:E22)=MAX(ROW(E2:E22)),1,0),ROW(E2:E22)*{1,1}))))),0.1)
H11,H17H11=(F11-D11)
I11,I17I11=IFERROR((G11/E11)^(365/H11)-1,"")
Press CTRL+SHIFT+ENTER to enter array formulas.


Hope this helps!
Click to expand...
Thanks a lot. working very very smoothly. saved me a lot of effort.
Please also help on this... XIRR does not give correct data if any one or two data have age less than 1 year. how to tackle this ?
 
Upvote 0
Sotu322 said:
Please also help on this... XIRR does not give correct data if any one or two data have age less than 1 year. how to tackle this ?
Click to expand...
Please provide an example of the data where the formula returns an incorrect result, and then provide the actual result/number that should be returned...
 
Upvote 0
can u ppls help on this : getting wrong XIRR values

Book1
AB
1DATEAMMOUNT
225-07-20211996.00
320-04-2021990.00
401-07-2021107800.00
512-07-202168600.00
617-08-20211135.00
717-08-202159038.00
817-08-202165853.00
917-08-2021266831.00
1002-09-2022-489034.00
11
12XIRR0.0000002980%
13
14*** Why is XIRR giving wrong result ?
Sheet1
Cell Formulas
RangeFormula
B12B12=XIRR(B2:B10,A2:A10)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Calculating the MWRR using the (X)IRR Function
Replies
1
Views
278
Avengerooo
A
L
  • Question
XIRR CALCULATION WITH BLANK CELLS
Replies
1
Views
673
StephenCrump
S
P
  • Question
Dynamic XIRR
Replies
1
Views
860
bassplr19
B
M
  • Question
Formula to return most repeated text in filtered data
Replies
4
Views
228
Domenic
D
drom
  • Solved
Count unique values for filtered column
Replies
1
Views
147
myall_blues
myall_blues

Forum statistics

Threads
1,214,979
Messages
6,122,551
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top