XIRR Formulae For each investment

Question

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.

Below is the result i am trying to get.

Answer 1

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

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.

Answer 2

pivottable can do it easily.It's not good for U?