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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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

M
  • Question Question
Unannualise an XIRR results
Replies
1
Views
157
Scott R
S
A
  • Question Question
Calculating the MWRR using the (X)IRR Function
Replies
1
Views
715
Avengerooo
A
L
  • Question Question
XIRR CALCULATION WITH BLANK CELLS
Replies
1
Views
2K
StephenCrump
S
P
  • Question Question
Dynamic XIRR
Replies
1
Views
2K
bassplr19
B
N
  • Solved
Find next visible cell, add a formula and then fill column down
Replies
8
Views
450
MARK858
MARK858

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

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