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% | ||
22 | TOTAL INFLOW | 18-08-2022 | -5,87,710 | ||||||||
23 | |||||||||||
24 | |||||||||||
25 | |||||||||||
26 | |||||||||||
27 | example XIRR FOR FILTERED CELLS | ||||||||||
28 | Security | Asset Class | Sub Type | Outflow Date | Outflow Amt | Inflow Date | Inflow Amt | Time Invested in DAYS | XIRR | ||
29 | A | F | I | 26-03-2018 | 13,159 | 18-08-2022 | 26,148 | 1606 | 16.89% | ||
30 | A | F | I | 19-11-2017 | 11,273 | 18-08-2022 | 30,389 | 1733 | 23.23% | ||
31 | 18-08-2022 | -56,537 | |||||||||
32 | |||||||||||
33 | XIRR | 20.18% | ***want this result | ||||||||
34 | |||||||||||
35 | 1 | do not get correct XIRR if it is applied on the main sheet using filters | |||||||||
36 | 2 | want XIRR for the selected range | |||||||||
37 | 3 | XIRR is not considering selected range but considers all the cells in the range | |||||||||
38 | 4 | XIRR works when the data is copied separately like example above which is very tedious | |||||||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H2:H21 | H2 | =(F2-D2) |
I2:I21 | I2 | =IFERROR((G2/E2)^(365/H2)-1,"") |
E22 | E22 | =-SUBTOTAL(9,G2:G21) |
E31 | E31 | =-SUM(G29:G30) |
B33 | B33 | =XIRR(E29:E31,D29:D31) |
=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)
sotu.xlsm | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Security | Asset Class | Sub Type | Outflow Date | Outflow Amt | Inflow Date | Inflow Amt | Time Invested in DAYS | XIRR | XIRR Result | 13.38% | |||
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% | |||||
22 | TOTAL INFLOW | 18-08-2022 | -587,710 | |||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L1 | L1 | =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:H21 | H2 | =(F2-D2) |
I2:I21 | I2 | =IFERROR((G2/E2)^(365/H2)-1,"") |
E22 | E22 | =-SUBTOTAL(9,G2:G21) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
sotu.xlsm | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Security | Asset Class | Sub Type | Outflow Date | Outflow Amt | Inflow Date | Inflow Amt | Time Invested in DAYS | XIRR | XIRR Result | 20.18% | |||
11 | A | F | I | 26-03-2018 | 13,159 | 18-08-2022 | 26,148 | 1606 | 16.89% | |||||
17 | A | F | I | 19-11-2017 | 11,273 | 18-08-2022 | 30,389 | 1733 | 23.23% | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L1 | L1 | =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,H17 | H11 | =(F11-D11) |
I11,I17 | I11 | =IFERROR((G11/E11)^(365/H11)-1,"") |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Thanks a lot. workingIn 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
A B C D E F G H I J K L 1 Security Asset Class Sub Type Outflow Date Outflow Amt Inflow Date Inflow Amt Time Invested in DAYS XIRR XIRR Result 13.38% 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% 22 TOTAL INFLOW 18-08-2022 -587,710 Sheet1
Cell Formulas Range Formula L1 L1 =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:H21 H2 =(F2-D2) I2:I21 I2 =IFERROR((G2/E2)^(365/H2)-1,"") E22 E22 =-SUBTOTAL(9,G2:G21) Press CTRL+SHIFT+ENTER to enter array formulas.
After the filter is applied
sotu.xlsm
A B C D E F G H I J K L 1 Security Asset Class Sub Type Outflow Date Outflow Amt Inflow Date Inflow Amt Time Invested in DAYS XIRR XIRR Result 20.18% 11 A F I 26-03-2018 13,159 18-08-2022 26,148 1606 16.89% 17 A F I 19-11-2017 11,273 18-08-2022 30,389 1733 23.23% Sheet1
Cell Formulas Range Formula L1 L1 =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,H17 H11 =(F11-D11) I11,I17 I11 =IFERROR((G11/E11)^(365/H11)-1,"") Press CTRL+SHIFT+ENTER to enter array formulas.
Hope this helps!
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
A B C D E F G H I J K L 1 Security Asset Class Sub Type Outflow Date Outflow Amt Inflow Date Inflow Amt Time Invested in DAYS XIRR XIRR Result 13.38% 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% 22 TOTAL INFLOW 18-08-2022 -587,710 Sheet1
Cell Formulas Range Formula L1 L1 =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:H21 H2 =(F2-D2) I2:I21 I2 =IFERROR((G2/E2)^(365/H2)-1,"") E22 E22 =-SUBTOTAL(9,G2:G21) Press CTRL+SHIFT+ENTER to enter array formulas.
After the filter is applied
sotu.xlsm
A B C D E F G H I J K L 1 Security Asset Class Sub Type Outflow Date Outflow Amt Inflow Date Inflow Amt Time Invested in DAYS XIRR XIRR Result 20.18% 11 A F I 26-03-2018 13,159 18-08-2022 26,148 1606 16.89% 17 A F I 19-11-2017 11,273 18-08-2022 30,389 1733 23.23% Sheet1
Cell Formulas Range Formula L1 L1 =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,H17 H11 =(F11-D11) I11,I17 I11 =IFERROR((G11/E11)^(365/H11)-1,"") Press CTRL+SHIFT+ENTER to enter array formulas.
Hope this helps!
Thanks a lot. working very very smoothly. saved me a lot of effort.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
A B C D E F G H I J K L 1 Security Asset Class Sub Type Outflow Date Outflow Amt Inflow Date Inflow Amt Time Invested in DAYS XIRR XIRR Result 13.38% 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% 22 TOTAL INFLOW 18-08-2022 -587,710 Sheet1
Cell Formulas Range Formula L1 L1 =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:H21 H2 =(F2-D2) I2:I21 I2 =IFERROR((G2/E2)^(365/H2)-1,"") E22 E22 =-SUBTOTAL(9,G2:G21) Press CTRL+SHIFT+ENTER to enter array formulas.
After the filter is applied
sotu.xlsm
A B C D E F G H I J K L 1 Security Asset Class Sub Type Outflow Date Outflow Amt Inflow Date Inflow Amt Time Invested in DAYS XIRR XIRR Result 20.18% 11 A F I 26-03-2018 13,159 18-08-2022 26,148 1606 16.89% 17 A F I 19-11-2017 11,273 18-08-2022 30,389 1733 23.23% Sheet1
Cell Formulas Range Formula L1 L1 =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,H17 H11 =(F11-D11) I11,I17 I11 =IFERROR((G11/E11)^(365/H11)-1,"") Press CTRL+SHIFT+ENTER to enter array formulas.
Hope this helps!
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...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 ?
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | DATE | AMMOUNT | ||
2 | 25-07-2021 | 1996.00 | ||
3 | 20-04-2021 | 990.00 | ||
4 | 01-07-2021 | 107800.00 | ||
5 | 12-07-2021 | 68600.00 | ||
6 | 17-08-2021 | 1135.00 | ||
7 | 17-08-2021 | 59038.00 | ||
8 | 17-08-2021 | 65853.00 | ||
9 | 17-08-2021 | 266831.00 | ||
10 | 02-09-2022 | -489034.00 | ||
11 | ||||
12 | XIRR | 0.0000002980% | ||
13 | ||||
14 | *** Why is XIRR giving wrong result ? | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B12 | B12 | =XIRR(B2:B10,A2:A10) |