excel एक्सेल में और Google शीट्स में गतिशील रूप से एक्सआईआरआर की गणना कैसे करें




excel-formula excel-2010 (3)

मैं नीचे दिए गए प्रारूप में मेरे पास डेटा के लिए एक्सआईआरआर की गणना करने की कोशिश कर रहा हूं I

    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

मैंने कई अलग-अलग क्रमांतरणों और संयोजनों की कोशिश की, लेकिन प्रत्येक फंड के लिए XIRR की गणना करने में सक्षम नहीं क्या ऐसा कोई तरीका है जो मैं इसे ऑफ़सेट या खोज का उपयोग कर सकता हूं यह एक गतिशील सूची है जो नई निधियों के आधार पर बदलती रहती है जो खरीदी जाती हैं या बेची जाती हैं

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%

अद्यतन को देखते हुए @ एक्सएआर-एलएक्स द्वारा उल्लिखित समाधान एमएस एक्सेल में बहुत अच्छी तरह से काम कर रहे थे, लेकिन गूगल शीट्स में नहीं, मैंने अब @केली सलाह के आधार पर अपने डेटा की संरचना को संशोधित किया है और अब मैं पूरी तरह से प्रत्येक फंड के लिए आसानी से एक्सआईआरआर की गणना कर सकता हूं । नीचे देखें

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

XIRR को तर्क के दो सरणियों - मूल्यों और तिथियों (एक वैकल्पिक तीसरे "अनुमान" तर्क के साथ) की आवश्यकता है। उन तारों को खरीद तिथियों, कुल रिटर्न, खरीद की मात्रा, और कई "आज की तारीखों" की गैर-सीमावर्ती श्रेणियों से एक साथ सिलाई करने की कोशिश करना एक एक्सेल फार्मूले के लिए काफी चुनौती होगी। इसलिए मैं VBA में लिखित उपयोगकर्ता परिभाषित फ़ंक्शन प्रदान करता हूं।

फ़ंक्शन के लिए तर्क फंड या स्क्रिप्ट ( sFund ) हैं और आपकी डेटा सारणी रेंज आपको sFund है जैसा आपके ऊपर आपके उदाहरण में है

जैसा कि आपकी एक टिप्पणी में सुझाव दिया गया है, मैंने TODAY Total Return कॉलम से संबंधित तारीख का उपयोग किया था। यदि ऐसा मामला नहीं है तो आसानी से बदला जा सकता है।

इसके अलावा, ध्यान दें कि मैंने "forXIRR" कॉलम का उपयोग नहीं किया है, बल्कि मैंने NetAmount कॉलम के नकारात्मक का इस्तेमाल किया है। यदि आप forXIRR स्तंभ को forXIRR , तो आपको संदर्भ को बदलने की आवश्यकता होगी

चीजों को थोड़ी अधिक समझ में लाने के लिए मैंने एक कस्टम क्लास बनाया है

क्लास मॉड्यूल डालने के बाद, आपको इसे cFUND नाम देना होगा

कक्षा मॉड्यूल

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

नियमित मॉड्यूल

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

उपरोक्त आपके डेटा को देखते हुए, B लिए XIRR को इस रूप में गणना की जाएगी:

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

यहां पर आपके डेटा के परिणाम दिए गए हैं:


आपकी तालिका A1:G8 (पंक्ति 1 में हेडर के साथ) में है, और यह कि आपकी पसंद की फंड, जैसे "बी", J2 , सरणी सूत्र **:

=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()))

J3 , J4 , आदि में फंड के लिए समान परिणाम देने के लिए नीचे कॉपी करें।

मैं OFFSET को शामिल करने के लिए सेट अप करने के लिए इसे पसंद करते हैं; यह न केवल ब्रीफ़र (और इसलिए अधिक कुशल) है, लेकिन इससे भी ज्यादा महत्वपूर्ण, गैर-वाष्पशील

यदि आप रुचि रखते हैं, तो इसके लिए यहां देखें:

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

सादर

** सरणी फ़ार्मुलों को 'मानक' फार्मूले के समान नहीं दर्ज किया गया है। बस एन्टर दबाकर, आप पहले CTRL और SHIFT को दबाए रखें, और केवल उसके बाद ENTER दबाएँ यदि आपने इसे सही तरीके से किया है, तो आप एक्सेल को सूत्र के चारों ओर घुंघराले ब्रैकेट {} को बताएंगे (हालांकि मैन्युअल रूप से इन्हें खुद डालने का प्रयास नहीं करते हैं)।


मैं आपकी तालिका के आधार पर मानूंगा कि तारीख को स्तंभ ए है, फंड (या "स्क्रिप्ट") कॉलम बी है और "ForXIRR" कॉलम एफ है। नीचे आपके लिए यह करना होगा:

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

सुनिश्चित करें कि आप इसे Shift + Ctrl + Enter साथ Shift + Ctrl + Enter क्योंकि यह एक सरणी सूत्र है I

नोट: उपर्युक्त "ए" के लिए गणना की जाएगी, लेकिन किसी भी अन्य निधि के लिए फार्मूले का वह हिस्सा, या दूसरों के लिए गणना करने के लिए सेल संदर्भ का उपयोग करें

संपादित करें:

मुझे एहसास हुआ कि इसके बाद के संस्करण केवल अनुक्रम में पहले अक्षर के लिए काम करेंगे, कृपया नीचे का प्रयोग करें, और फिर, जो कुछ भी आप की आवश्यकता है उसे "B" को समायोजित करें। यह मौजूद किसी भी पत्र के लिए काम करेगा इसके अलावा, अपनी आवश्यकताओं को पूरा करने के लिए सभी श्रेणियां समायोजित करें, मैंने केवल 1:10 परीक्षाओं के लिए पंक्तियों को किया है I नीचे सीमा की शुरुआत को समायोजित करता है, इसलिए यह गणना करने के लिए अनुमति देने के लिए जो भी फंड इनपुट (यानी "ए", "बी", "सी") का पहला मान होता है इस परिवर्तन के बिना, उपरोक्त हमेशा शून्य के साथ शुरू होगा जब तक कि यह सूची में सबसे पहले ("ए" को ओपी उदाहरण में), और केवल XIRR केवल एक नकारात्मक संख्या के साथ पहली मान के रूप में गणना करता है।

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




xls