XIRR help

[ngarzacathexis]

I'm trying to calculate XIRR with various initial dates and an end dates.

This is what I have now

Date Column(monthly) Investment #1 Investment #2.... etc.
5/30/2014 xxxx(values) xxxx(values)
. . .
. . .
. . .
. . .
5/30/2029 xxxx(values) xxxx(values)

Investment #1: start: 8/30/2014 end: 10/31/2020
Investment #1: start: 1/31/2018 end: 5/30/2029

I have about 35 different investments so manually would take a while and I will be adding more eventually. So I want to be able to use a XIRR formula that will retrieve the cash flow values based on the start and end date within the date column range to give the XIRR solution.

Any ideas? If you need more details just let me know.

Thank you!

 

[ngarzacathexis]

biocideJ,

I think I got it to work. I had to create a reference cell with the initial invested amount and the last revenue flow expected.

Then I used the following formula to search the date and value columns for the initial date and end date. As well as initial value and end value.
=XIRR(INDEX(B2:B$372,MATCH(EOMONTH(B$374,-1),$A$2:$A$372,0)):INDEX(B2:B$372,MATCH(B$376,$A$2:$A$372,0)),INDEX($A$2:$A$372,MATCH(B$379,B$2:B$372,0)):INDEX($A$2:$A$372,MATCH(B$380,B$2:B$372,0)))

That look okay to you?

 

[BiocideJ]

biocideJ,

I think I got it to work. I had to create a reference cell with the initial invested amount and the last revenue flow expected.

Then I used the following formula to search the date and value columns for the initial date and end date. As well as initial value and end value.
=XIRR(INDEX(B2:B$372,MATCH(EOMONTH(B$374,-1),$A$2:$A$372,0)):INDEX(B2:B$372,MATCH(B$376,$A$2:$A$372,0)),INDEX($A$2:$A$372,MATCH(B$379,B$2:B$372,0)):INDEX($A$2:$A$372,MATCH(B$380,B$2:B$372,0)))

That look okay to you?

Without being able to see your spreadsheet, it is impossible to say, but I would create a few manual XIRR formulas to error-check the values being returned.
If that looks good then I suspect you are set.

 

[ngarzacathexis]

I validated the data with a couple manual formulas and it came back correct.
Sorry for the confusing explanation but thank you for all your help.