Search code examples
excelexcel-formulairrxirrvba

XIRR Formulae For each investment

I am trying to apply an XIRR Formulae so that the percentage of each investment is calculated separately. I attempted an IF formulea an was not successful. I need to amend the below formulae. This will be useful as i am trying to apply it to 200 invesmtents plus. 

=XIRR(C2:C17,B2:B17)

Below is the data. enter image description here

Below is the result i am trying to get.

enter image description here

Solution

  • There are some problems of your data setup. Here is how the IRR works (http://www.investopedia.com/ask/answers/022615/what-formula-calculating-internal-rate-return-irr-excel.asp). Without your data, here is how you can make this to work for matching different investments.

    See the attached image below and assume you are entering the formula from cell F2 and here you go:

    =XIRR(OFFSET($C$1,MATCH(E2,$A$2:$A$17,0),0,COUNTIF($A$2:$A$17,E2)),OFFSET($B$1,MATCH(E2,$A$2:$A$17,0),0,COUNTIF($A$2:$A$17,E2)))

    Please note this is an array formula (need to click Ctrl + Shift + Enter together)).

    enter image description here

    What I did is to use OFFSET to find the pertaining ranges for dates and values of each investment. And here I have also showed you the results for a valid data inputs. Try and let me know.