how to calculate XIRR dynamically in excel and in google sheets

Question

I am trying to calculate XIRR for data that I have in the below format

    PurchaseDate    Script  No.ofunits  PurchNAVrate    NetAmount            ForXIRR    TotalReturn
    17/11/2014          A   2241            33              75000           -75000          96000
    8/1/2015            B   53              649             35000           -35000          43000
    14/1/2015           B   75              658             50000           -50000          61500
    14/10/2014          C   2319            32              75000           -75000          108000
    8/1/2015            D   318             109             35000           -35000          40000
    14/1/2015           D   450             110             50000           -50000          57000
    8/6/2015            D   175             114             20000           -20000          22000

I tried lots of different permutations and combinations but not able to calculate XIRR for each Fund. Is there a way I can do it perhaps using offset or search. This is a dynamic list which keeps getting changed based on new funds that are bought or sold

Values for Fund A should be around 14%  
Values for Fund B should be around 13%
Values for Fund C should be around 21%
Values for Fund D should be around 8%

Update Given that solution mentioned by @xor-lx was working very nicely in ms excel but not in google sheets, I have now modified the structure of my data based on @kyle advice and now I can calculate XIRR easily for each fund as a whole. Refer below

PurchaseDate    Today       Script  No.ofunits  PurchNAVrate    NetAmount   ForXIRR TotalReturn     XIRR
17/11/14        31/08/2016  A       2241        33              75000       -75000  96000           "=XIRR(G2:H2,A2:B2)"
                            Total for above fund                                                    "=XIRR(G2:H3,A2:B3)"
8/1/2015        31/08/2016  B       53          649             35000       -35000  43000           "=XIRR(G4:H4,A4:B4)"
14/1/2015       31/08/2016  B       75          658             50000       -50000  61500           "=XIRR(G5:H5,A5:B5)"
                            Total for above fund                                                    "=XIRR(G4:H6,A4:B6)"
14 Oct 14       31/08/2016  C       2319        32              75000       -75000  108000          "=XIRR(G7:H7,A7:B7)"
                            Total for above fund                                                    "=XIRR(G7:H8,A7:B8)"
8 Jan 15        31/08/2016  D       318         109             35000       -35000  40000           "=XIRR(G9:H9,A9:B9)"
14 Jan 15       31/08/2016  D       450         110             50000       -50000  57000           "=XIRR(G10:H10,A10:B10)"
8/6/2015        31/08/2016  D       175         114             20000       -20000  22000           "=XIRR(G11:H11,A11:B11)"
                            Total for above fund                                                    "=XIRR(G9:H12,A9:B12)"

Answer 1

Assuming your table is in A1:G8 (with headers in row 1), and that your Fund of choice, e.g. "B", is in J2, array formula**:

=XIRR(INDEX(F:G,N(IF(1,MODE.MULT(IF(B$2:B$8=J2,{1,1}*ROW(B$2:B$8))))),N(IF(1,{1,2}))),CHOOSE({1,2},INDEX(A:A,N(IF(1,MODE.MULT(IF(B$2:B$8=J2,{1,1}*ROW(B$2:B$8)))))),TODAY()))

Copy down to give similar results for Funds in J3, J4, etc.

I tend to prefer this to set-ups involving OFFSET; not only is it briefer (and therefore more efficient), but also, more importantly, non-volatile.

See here for more if you're interested:

https://excelxor.com/2016/02/16/criteria-with-statistical-functions-growth-linest-logest-trend/

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).