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?

 

[iliace]

My Excel Jeanie is broken, so I apologize for the crudeness here.

First, here's my sample data in A1:C10

28-Jan-09   -7812  X 
16-Jul-09    1342  X 
 1-Jan-10   -2677  
19-Jun-10    4197  
 5-Dec-10    4877  
23-May-11    -749  
 8-Nov-11    4887  X 
25-Apr-12    1320  X 
11-Oct-12   -1660  X 
29-Mar-13    4176

In E1, I have the following formula:

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

It's array-entered; Ctrl+Shift+Enter.

Next I have a control set of values, in E5:F9

28-Jan-09 -7812
16-Jul-09  1342
 8-Nov-11  4887
25-Apr-12  1320
11-Oct-12 -1660

Then I use this formula:

=XIRR(F5:F9,E5:E9)

Both return the same result (-12.45%).

Note also, that if I change a cashflow, such as the Nov 8, 2011, to negative (-4887) I'll get a #NUM error in either formula, because now NPV is always <0, therefore there is no IRR.

 

[liz23]

thank you very much! i figured out the problem...it think my data set has issues because they are formulas. if i paste them as values everything works.

 

[liz23]

Can I ask two follow-up questions?

1) Does the first entry have to fit the criteria? In other words, if there is not "x" in the beginning of the range, does the formula not work?

2) Can you have multiple conditions?

Thanks!

 

[iliace]

I don't understand the first question. The X thing was in the original example.

As far as number of conditions, you can have as many as you want so long as you can express them in an array that will return all numeric values and use zeroes for conditions that don't match.

 

[geminidodo]

Can I ask two follow-up questions?

1) Does the first entry have to fit the criteria? In other words, if there is not "x" in the beginning of the range, does the formula not work?

2) Can you have multiple conditions?

Thanks!

This post is very helpful!

However, I did have the problem mentioned in your question 1. It seems that XIRR requires the first cash flow to be negative. If it is zero, it will return an IRR of 0.00%.

I modified the formula as below, and it worked! Just set the power high enough so that the result is extremely close to the actual IRR.

=XIRR(IF(C1:C10="X",B1:B10,-Power(0.1,50)),A1:A10)

 

[gwen1]

Hi,

I am really struggling with the XIRR function at the moment. When the return is negative, the XIRR function returns zero.

I paste the example below:

=XIRR(IF($E$4:$E$1453=$J5,$H$4:$H$1453,-POWER(0.1,50)),$G$4:$G$1453) where column E is a country parameter which gets chosen depending which country I am looking at, H column is cashflows and G column is dates. I am trying to calculate the pooled IRR. So for certain countries when I take the data from the table and manually calculate the IRR, it returns a negative IRR, but the formula above returns zero. I do not know how to fix this and yet calculating this manually is too painful as I have a lot of data.

Really appreciate your help.

 

[joeu2004]

I am really struggling with the XIRR function at the moment. When the return is negative, the XIRR function returns zero.

You correctly "reposted" your question as a new thread. See http://www.mrexcel.com/forum/excel-questions/895686-xirr-returns-zero-when-returns-negative.html for responses.