XIRR with non-contiguous last value

[spreadsheetmaker]

I have been wrestling with a XIRR formula for the past week. I have a table with outflows and fund names for each outflow. I created a XIRR formula that not only takes into a count a specific fund identified in cell F2 but also prevents any XIRR errors when the initial outflow value is 0. The issue that I'm having right now is that my present value of the fund is located in a non-contiguous cell (G6). I need to somehow expand the array that XIRR is calculating to include the last value in cell G6 with today's date. Based on the data provided, the expected XIRR should be 3.41%.


[TABLE="width: 800"]
<colgroup><col><col><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]FUND[/TD]
[TD] XIRR VALUE [/TD]
[TD][/TD]
[TD][/TD]
[TD]FUND[/TD]
[TD]XIRR:[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD]A[/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD]B[/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD]C[/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD]D[/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Todays: Fund Value[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD]E[/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD] $ 119,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD]F[/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD]G[/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD]D[/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/17/2014[/TD]
[TD]A[/TD]
[TD] $ (75,000.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2015[/TD]
[TD]B[/TD]
[TD] $ (35,000.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/14/2014[/TD]
[TD]C[/TD]
[TD] $ (75,000.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2015[/TD]
[TD]D[/TD]
[TD] $ (35,000.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2015[/TD]
[TD]D[/TD]
[TD] $ (50,000.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2015[/TD]
[TD]B[/TD]
[TD] $ (50,000.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6/8/2015[/TD]
[TD]D[/TD]
[TD] $ (20,000.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Current formula in cell G2 is: =XIRR(IF(OFFSET($B$1:$B$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1)=F2,OFFSET($C$1:$C$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1),0),IF(OFFSET($B$1:$B$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1)=F2,OFFSET($A$1:$A$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1),0))


The reason why I can't simply add today's date to the bottom with the investment name and value is that the actual investment value is pulled from another worksheet. I linked the value of $119,000.00 to cell G6.

It doesn't look like I'm allowed to upload my spreadsheet right now as I'm a brand new forum member.

 

[XOR LX]

Hi,

If this is for Excel 2013 or later, array formula**:

=XIRR(IF(INDEX(1-MUNIT(1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")),,1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")),INDEX(C:C,N(IF(1,MODE.MULT(IF(B2:B16=F2,IF(C2:C16<>0,{1,1}*ROW(B2:B16))))))),G6),IF(INDEX(1-MUNIT(1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")),,1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")),INDEX(A:A,N(IF(1,MODE.MULT(IF(B2:B16=F2,IF(C2:C16<>0,{1,1}*ROW(B2:B16))))))),TODAY()))

If you're on a pre-2013 version I can amend accordingly.

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

 

[spreadsheetmaker]

Its for 2013. It works! thank you very much

 

[XOR LX]

You're welcome!

Cheers

 

[spreadsheetmaker]

Hey, I didn't realized that my home PC has Office 2010. Is there any way you could modify the formula to work on both 2010 and 2013? Thanks in advance!

 

[XOR LX]

Ok, then use (still an array formula):

=XIRR(IF(N(ROW(INDEX(A:A,1):INDEX(A:A,1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")))<1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")),INDEX(C:C,N(IF(1,MODE.MULT(IF(B2:B16=F2,IF(C2:C16<>0,{1,1}*ROW(B2:B16))))))),G6),IF(N(ROW(INDEX(A:A,1):INDEX(A:A,1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")))<1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")),INDEX(A:A,N(IF(1,MODE.MULT(IF(B2:B16=F2,IF(C2:C16<>0,{1,1}*ROW(B2:B16))))))),TODAY()))

Regards

 

[spreadsheetmaker]

Ok, then use (still an array formula):

=XIRR(IF(N(ROW(INDEX(A:A,1):INDEX(A:A,1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")))<1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")),INDEX(C:C,N(IF(1,MODE.MULT(IF(B2:B16=F2,IF(C2:C16<>0,{1,1}*ROW(B2:B16))))))),G6),IF(N(ROW(INDEX(A:A,1):INDEX(A:A,1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")))<1+COUNTIFS(B2:B16,F2,C2:C16,"<>0")),INDEX(A:A,N(IF(1,MODE.MULT(IF(B2:B16=F2,IF(C2:C16<>0,{1,1}*ROW(B2:B16))))))),TODAY()))

Regards

And once again you save the day!

 

[XOR LX]

Glad to help!

Cheers