# IRR function

Hi,

I’m trying to use this function (irr), but doesn’t work when I use it with a matrix with more than two dimensions (appears "#NUM" in the cell).

So I don´t know if that formula doesn’t work in this cases or I´m doing something wrong.

I attached an example for more explanation.

Thanks.

vishnuyarlagadda,

I think I figured it out using sublist(), first(), last(). See the attached model and the Answer Item formula. It solved for Cash1.

Let me know if this is what you were looking for. It seems to be slightly off of what excel is giving you.

Good Luck.

Rich

Hi James,

Thanks for posting the solution. Could you please tell why the result changes to # value for XIRR formula.

I am pressing F2 on formula and with out any changes i am pressing Enter. The results changes to # value.

This happens in excel too for XIRR formula and Ctrl+Shift+Enter will give a result as it is an array but that does not work here.

Hi Vishnu,

Sorry I didn’t get a chance to look at your problem sooner.

Here is a solution where I use the selects function to select the list of values where a cash flow exists and then I do the same for the eomonth(timelinedate(),0).

You end up with quite an elegant solution for each cash flow.

Let me know if you have any other problems.

James

Thank you Lopez,

It works perfectly when each individual cash flow is considered , but when it is replicated for overall category i am getting error values i.e first( Cashflow::E,1) returns only first value of category not first-value of every cashflow.

I would like to know if there is a way to calculate XIRR for all Cashflows.

Regards,

Vishnu

Sublist() Which was made available in version 5.3 see the release notes should do the trick for you for OFFSET (http://www.quantrix.com/media/334961/qu … es_5.3.pdf)

Also consider the first() function. I continue to look at the XIRR.

Good Luck

–Rich

Thanks Lopez,

Using eomonth() is helpful. I would also like to know if there is any function in Quantrix similar to offset in excel.

I am trying to find first non zero value in a row and move 120 locations from there to insert costs value. If condition can be used to find not null value but to move 120 locations from that position is a challenge. I have also tried using match to obtain position of first non zero value but could not index 120 cell from that.

Hi Lopez,

Thanks for the reply, In this case XIRR is very sensitive on starting date and to get an exact value it has to point to start value and date for every cash flow. I tried your suggestion and by adding negligible values (0.09) in the beginning will result in XIRR to be 0.

This has an easy solution in excel by using Offset and pointing to the starting location and date, but i am trying to find solution in Quantrix.

I also have an issue in getting date values from timeline to include in XIRR formula.

timelinedate() returns start date of every month but i would like to have last date of every month to be input to XIRR.

XIRR(values, dates(End date of every month obtained from time line-02/28/2015), 0.1)

Can you please see the model attached in the above post for better understanding.

Thank you

To explain in detail about the issue i posted earlier, I have attached the following XIRR model.

In this model there are 4 cash flows starting and ending at different time periods.I would like to calculate XIRR for these cashflows using Quantrix. I have also added XIRR values calculated in excel for these cash flows.

[b][u]Generalized Excel Formula used in this scenario

=XIRR(OFFSET(N4,0,MATCH(TRUE,$N4:$OG4<>0,)-1,1,COUNT($N4:$OG4)), OFFSET($N$3,0,MATCH(TRUE,$N4:$OG4<>0,0)-1,1,COUNT($N4:$OG4)),0.1)

Can some one please suggest how to use Quantrix XIRR function in this scenario. I could not find any information regarding this in Quantrix help.