how to calculate XIRR dynamically in excel and in

问题:

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)"

回答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).

回答2:

XIRR requires two arrays of arguments -- values and dates (with an optional third "guess" argument). To try to stitch together those arrays from non-contiguous ranges of purchase dates, total returns, purchase amounts, and a number of "today's dates" would be quite a challenge for an Excel formula. Hence I provide a User Defined Function written in VBA.

The arguments for the function are the fund or script (sFund) and your data table range laid out as you have in your example above.

As suggested in one of your comments, I used TODAY as the date corresponding to the Total Return column. Easily changed if that is not the case.

Also, note that I did not use the "forXIRR" column, rather I just used the negative of the NetAmount column. If you delete the forXIRR column, you will need to change the reference to .TotalReturn

I created a custom Class in order to make things a bit more understandable.

After you insert the Class Module, you must rename it cFUND

Class Module

Option Explicit
'Rename cFUND

Private pFund As String

Private pPurchDt As Date
Private pPurchDts As Collection

Private pPurchAmt As Double
Private pPurchAmts As Collection

Private pTotalReturn As Double
Private pTotalReturns As Collection

Public Property Get Fund() As String
    Fund = pFund
End Property
Public Property Let Fund(Value As String)
    pFund = Value
End Property

Public Property Get PurchDt() As Date
    PurchDt = pPurchDt
End Property
Public Property Let PurchDt(Value As Date)
    pPurchDt = Value
End Property
Public Function ADDPurchDt(Value As Date)
    pPurchDts.Add Value
End Function
Public Property Get PurchDts() As Collection
    Set PurchDts = pPurchDts
End Property

Public Property Get PurchAmt() As Double
    PurchAmt = pPurchAmt
End Property
Public Property Let PurchAmt(Value As Double)
    pPurchAmt = Value
End Property
Public Function ADDPurchAmt(Value As Double)
    pPurchAmts.Add Value
End Function
Public Property Get PurchAmts() As Collection
    Set PurchAmts = pPurchAmts
End Property

Public Property Get TotalReturn() As Double
    TotalReturn = pTotalReturn
End Property
Public Property Let TotalReturn(Value As Double)
    pTotalReturn = Value
End Property
Public Function ADDTotalReturn(Value As Double)
    pTotalReturns.Add Value
End Function
Public Property Get TotalReturns() As Collection
    Set TotalReturns = pTotalReturns
End Property

Public Function CalcXIRR()
    Dim v, d, w
    Dim I As Long
    With Me
        ReDim d(.PurchDts.Count * 2 - 1)
        ReDim v(UBound(d))

        I = 0
        For Each w In .PurchAmts
            v(I) = w
            I = I + 1
        Next w
        For Each w In .TotalReturns
            v(I) = w
            I = I + 1
        Next w

        I = 0
        For Each w In .PurchDts
            d(I) = w
            I = I + 1
        Next w
        Do Until I > UBound(d)
            d(I) = Date
            I = I + 1
        Loop
    End With

    CalcXIRR = WorksheetFunction.XIRR(v, d)
End Function
Private Sub Class_Initialize()
    Set pPurchDts = New Collection
    Set pPurchAmts = New Collection
    Set pTotalReturns = New Collection
End Sub

Regular Module

Option Explicit
Function fundXIRR(sFund As String, rTable As Range) As Double
    Dim vSrc As Variant
    Dim cF As cFUND
    Dim I As Long

vSrc = rTable

Set cF = New cFUND
For I = 2 To UBound(vSrc, 1)
    If vSrc(I, 2) = sFund Then
        With cF
            .PurchDt = vSrc(I, 1)
            .ADDPurchDt .PurchDt
            .Fund = vSrc(I, 2)
            .PurchAmt = -vSrc(I, 5)
            .ADDPurchAmt .PurchAmt
            .TotalReturn = vSrc(I, 7)
            .ADDTotalReturn .TotalReturn
        End With
    End If
Next I

fundXIRR = cF.CalcXIRR

End Function

---

Given your data above, XIRR for B would be calculated as:

XIRR({-35000;-50000;43000;61500},{"1/8/2015";"1/14/2015";"8/29/2016";"8/29/2016"}

Here are the results for your data above:

回答3:

I will assume based on your table that the date is column A, the fund (or "Script") is column B and the "ForXIRR" is column F. The below will do it for you:

{=XIRR(IF(B:B="A",F:F,0),IF(B:B="A",A:A,0))}

Make sure you enter this with Shift + Ctrl + Enter since it is an array formula.

Note: The above will calculate for "A", but substitute that part of the formula for any other fund, or use a cell reference to calculate for others.

EDIT:

I realized the above will only work for the first letter in the sequence, please use below, and again, adjust the "B" to whatever you need. This will work for any of the letters present. Also, adjust all the ranges to match your needs, I only did rows 1:10 for my test purposes. The below adjusts the start of the range so it is always the first value for whichever fund is input (ie "A", "B", "C") to allow this to calculate. Without this change, the above will always start with a zero unless it is the first in the list ("A" in the OPs example), and XIRR only calculates with a negative number as the first value.

=XIRR(IF(OFFSET($B$1:$B$10,MATCH("B",$B$1:$B$10,0)-1,0,ROWS($B$1:$B$10)-MATCH("B",$B$1:$B$10,0)+1,1)="B",OFFSET($F$1:$F$10,MATCH("B",$B$1:$B$10,0)-1,0,ROWS($B$1:$B$10)-MATCH("B",$B$1:$B$10,0)+1,1),0),IF(OFFSET($B$1:$B$10,MATCH("B",B1:B10,0)-1,0,ROWS($B$1:$B$10)-MATCH("B",$B$1:$B$10,0)+1,1)="B",OFFSET($A$1:$A$10,MATCH("B",$B$1:$B$10,0)-1,0,ROWS($B$1:$B$10)-MATCH("B",$B$1:$B$10,0)+1,1),0))