IRR function

Question

Solved8.97K viewsJune 12, 2018Formulas and Functions

0

carlob 50 November 5, 2014 0 Comments

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.

Attachments

IRR.model

14 Answers

0

rlopez 391 Posted September 11, 2015 0 Comments

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

Attachments

XIRR-Lopez.model

score 0 · Answer 1 · 2015-09-14T21:08:00+00:00

Hi James,

Though the previous model you have sent is easy, this does the work.

Thank you

Regards,
Vishnu

score 0 · Answer 2 · 2015-09-14T19:17:34+00:00

Hi Vishnu,

My apology. I built this using a newer version of Quantrix where we had introduced a different way of doing this.

I have attached a solution to your problem using Quantrix 5.3.4

Regards,

James

score 0 · Answer 3 · 2015-09-14T18:27:54+00:00

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.

score 0 · Answer 4 · 2015-09-14T15:43:19+00:00

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

score 0 · Answer 5 · 2015-09-14T14:51:17+00:00

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

score 0 · Answer 6 · 2015-09-11T18:53:43+00:00

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

score 0 · Answer 7 · 2015-09-11T17:33:10+00:00

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.

score 0 · Answer 8 · 2015-09-11T15:29:49+00:00

I’ll keep thinking about this; in the meantime to get the last day of the month using timelinedate() try this: LastDayofMonth = eomonth( timelinedate() ,0)

score 0 · Answer 9 · 2015-09-10T21:12:29+00:00

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

score 0 · Answer 10 · 2015-09-10T16:05:23+00:00

This is a merely a suggestion. Try creating a helper item calling it "CashFlowUse" have the following formula for it be

CashFlowUse = if(sum(Cash flow) = 0, .009, Cash flow)

Then in the xirr function for the values argument enter CashFlowUse.

I hope this helps.

–Rich Lopez

score 0 · Answer 11 · 2015-09-10T15:52:57+00:00

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.

score 0 · Answer 12 · 2015-09-10T13:35:52+00:00

How can we calculate XIRR in this example? XIRR will give an incorrect value if there are any blanks or zeroes in the beginning. Can some one suggest a solution

score 0 · Answer 13 · 2014-11-13T08:13:44+00:00

If you specify the year:month if your formula it will force the IRR function to look at the entire range as one range. Try this:

IRR 1 = irr(Cash flow 1::Cash flow:Year:Month)

IRR function

Attachments

14 Answers

Attachments

Attachments

Attachments

Attachments

Latest Questions

Connect