Xirr

[liz23]

Is it posssible to use the xirr function with conditions. For example, an array formula that will only calculate xirr if a column meets a certain criteria or if the dates are within a range?

 

[Jonmo1]

Welcome to the board

I'm not familiar with te xirr function, but you can make an array formula that will execute the xirr only on rows that meet criteria..

something like this

=XIRR(IF(A1:A100="X",B1:B100))

That would be the same as
=XIRR(B1:B100) - but only count rows where column A = X

Array formula entered with CTRL + SHIFT + ENTER

Hope that helps...

 

[liz23]

thanks! i actually tried that, but the xirr function requires two inputs, the dates as well as the cash flows. so when i add an additional comma for the cash flows, it assumes i am applying the value if false.

do you have any suggestions to fix this?

 

[Jonmo1]

OK, this is due to my non familiarity with xirr...

You would need the IF in BOTH arguments of the xirr function...

so say the xirr formula is
=xirr(a1:a100,b1:b100)
and your criteria is column C = X

=XIRR(IF(C1:C100="X",A1:A100),IF(C1:C100="X",B1:B100))

I think that will work...

 

[liz23]

thanks for you help...it did not work. i am not sure if it is even possible.

 

[Sneech#7]

why not put the xirr within the if rather than the other way around?

 

[iliace]

Leave the dates alone and use a 0 value for inflows/outflows in the IF statement. Here I have dates in A1:A10, values in B1:B10, and X or blank in C1:C10

=XIRR(IF(C1:C10="X",B1:B10,0),A1:A10)

Use Ctrl+Shift+Enter.

 

[liz23]

i tried putting the xirr within the if statement, also tried the last formula as well (=XIRR(IF(C1:C10="X",B1:B10,0),A1:A10))...but neither work.

thanks again for everyone's help. i am getting #NUM! errors with the last formula and the same answer for the xirr within the if statement.

 

[iliace]

If you get a #NUM error, that means set does not converge. In other words, given the input values, XNPV is always less than 0 or always more than 0, regardless of the discount rate.

 

[liz23]

but the odd thing is if i do it manually...i get a value.

also...if i do it manually by actually choosing the dates, then refresh, the formula works sometimes.