XIRR function considering data from different columns - new post

[hali]

Trying to get more insight from the response submitted by p5cal

https://chandoo.org/forum/threads/x...data-from-different-columns.48073/post-284566

The formula in column D refers to arr, val, n and k. However I do not see having been them defined under Name Manager. So I am unclear as to how the formula is reading them. The file attached in the original thread is re-attached here.

Thank you.

 

[p45cal]

in a lambda formula using the keyword LAMBDA on the sheet, the values (in this case) arr and val are from the arguments in brackets after the LAMBDA function's closing parenthesis; colour coded:
=XIRR(LAMBDA(arr,val,LET(n, ROWS(arr),k, SEQUENCE(n+1),IF(k<=n,arr,val)))(B$2:B4,C4),LAMBDA(arr,val,LET(n, ROWS(arr),k, SEQUENCE(n+1),IF(k<=n,arr,val)))(A$2:A4,A4))

n and k are defined within the LET function:
n = ROWS(arr)
k = SEQUENCE(n+1)

 

[hali]

Thanks a lot. My data is structured bit differently. Please refer attached excel sheet. I need to calculate IRR on quarterly basis. However, I am struggling to get a formula wherein for each asset/asset category the formula should calculate XIRR taking into account all previous cash flows and the valuation for current quarter which means it should skip valuations for previous quarters. Also it should exclude transaction type "Commitment" because it is not a cash flow. In the example it is at the bottom of the data but new investment can be added with a line for commitment as the data keeps growing. Also for simplicity, I have kept one asset per category but in real data there are more than one asset per category. I am looking for a formula which can sustain for data over 5000 lines. I have done few manual calculations for Asset A as an example on various dates. Obviously I cannot do this manually for approx. 200 assets with 10 asset categories on a quarterly basis. Thank you for your help.

 

[p45cal]

First, the attached is unfinished (I only re-read your last message just before posting this and realised you were wanting this for asset/asset category combination and the attached only looks at Asset#). I will have a go later.
The finished formula is in column AI
It may be of interest to you to see a little on how it was derived; see the yellow cells in columns N:AD.
The formula expects to see a Valuation transaction type on the On Date.
I don't know what the Commitment transaction type is and have excluded them because your manual calculations excluded them.
Note that the formula has hints:

 

[hali]

Thanks so much. This was really really helpful.

 

[p45cal]

Updated.
One cell (M7) has the derivation of the formula - it can be deleted. Otherwise, see column K.

 

[hali]

Thank you Once again. I put the formula in my actual data and it is working fine but the problem is the guess for XIRR. While it is working correctly with positive XIRR (without putting a guess) but in case of negative IRR sometimes the formula result is correct but in certain cases it is not. I had read in some other post to use a very small number as a guess. someone had suggested 10^-99. However if I do that then formula does not work where positive IRR is expected. Any thoughts on that?

 

[hali]

please read -10^-99

 

[p45cal]

sometimes the formula result is correct but in certain cases it is not.

OK, because it's 'sometimes', I need to see instances of both correct and incorrect with negative IRR. Can you attach a workbook with both and what the result should be?

 

[hali]

Thanks once again. I have included three examples in the attachment. First 2 (Investment A and B) should have a negative IRR and the third one (Investment C) has a positive IRR. For Investment A, the XIRR formula result is incorrect without including a guess. Investment B also has a negative IRR like Investment A but the formula gives correct result without including a guess. Investment C has a correct formula result (positive IRR) but the moment I include the guess -10^-99, it throws a #NUM. With three different results with same formula, I am looking for a consistent result.

 

[p45cal]

Starting to look at this, in the meantime, is it OK that cells H32 and H33 have the same date? (I know little of these financial functions.)

 

[hali]

Yes, it is correct.

 

[p45cal]

I have not abandoned this thread, just been busy. I have some ideas. Will come back soon. (XIRR has some shortcomings.)

 

[joeu2004]

I had read in some other post to use a very small number as a guess. someone had suggested 10^-99 [Later: please read -10^-99]

You have crossposted to answers.microsoft.com ( https://answers.microsoft.com/en-us...-results/bf981dd4-d456-466d-b38d-74cca5da384f' ).
I will probably post subsequent comments there. But I thought it might be helpful if I duplicate my response to this comment here.

That made no sense to me. So I tracked down the "other post" ( https://chandoo.org/forum/threads/calculating-xirr-on-certain-entries-in-a-list.39636' ).

And as it turns out, you misunderstood.

The suggestion was not to use -10^-99 as a guess.

Instead, it was suggested to avoid zero as a first value. Here is the context.

The original user wrote the formula
{ =XIRR(IF(--(L6:L16=J18), K6:K16, 0), IF(--(L6:L16=J18), J6:J16, 0), 0.2) }

The responder wrote: ``The first value from your cash flows have to be negative, when you check against B the first value (A) is set to 0, giving wrong results [....] Instead of using 0 in your IF, try to use an extremely small negative number (-10^-99)``

Note that in the XIRR expression, the guess is 0.2. So that cannot be the 0 that the responder is referring to.

Instead, he is referring to the 0 in the first IF expression, which provides the first value conditionally.

(FYI, there are several incorrect details in that response. But there is no point in expanding on that because it has nothing to do with your problem.)

 

[hali]

Thank you for paying attention. So what is the ultimate solution. What guess to used as 0 does not give the consistent results based on my 3 examples.

 

[p45cal]

@joeu2004 , I'm glad you jumped in since I was going to quote you left, right and centre. I hope this doesn't make you groan too much!; I was looking at some of your other posts on this subject and in trying to get a solution for @hali I was exploring what you said here: https://answers.microsoft.com/en-us...function/889dd119-a1be-424c-9960-2eebf91813e7

But a more methodical way to determine a reasonable "guess" is to look at the NPV curve.

In the attached, is my experimentation and development towards a single-celled formula incorporating a guess derived from the change in sign of the NPV and I'd like to ask your advice/opinion on a few things.

First a few pointers to what's what in the attached.
Sheet2 is just an implementation of your suggestion in that thread - just to make sure I was doing it right, so it's not important.
Sheet1 is copy of @hali 's workbook where I've been looking only at Investment A.
C24 and below is your SumProduct formula for NPV, and next to it Excel's XNPV function for confirmation, which of course doesn't work for negative rates.
In E24, a single-celled formula to return the same array of results, and to the right of that a simple formula to show the Sign of those results.Then, in cell I24, a formula to come up with an average of the 2 rates either side of the NPV crossing 0. That value is then used in the XIRR formula in cell C19 as a guess. [Just for silliness, I used that result as a guess in another XIRR formula, with the difference showing in cell D20.]

So my questions:
1. Will the values in column F always transition from +ve to -ve? (I see them doing so on Sheet2 with +ve IRRs.) If so I won't have to develop more complicated ways of identifying a change of sign.
2. Do you think there's any chance such an NPV curve can cross the zero value twice? (I noticed that the NPV curve doesn't always have a -ve or +ve gradient throughout (see graph on Sheet1))
3. Is this a silly approach to solving @hali 's problem?! And is there maybe a simpler algorithm (I know nothing of financial functions)?

I'm hoping by using Excel 365's LET and LAMBDA functions, with their improved efficiency of having only to calculate values/arrays once, to be able to give @hali a single-celled formula solution which isn't too resource hungry.

 

[p45cal]

@hali , it's only fair to tell people where you've cross posted to. Have you posted about the same topic elsewhere?

 

[joeu2004]

@p45cal .... I'm busy preparing my own response. (I actually posted and deleted it several times in another forum.) But I'll address your questions briefly.

1. Will the values in column F always transition from +ve to -ve?

Perhaps, if the first cash flow is negative. But contrary to MSFT docs and much online misinformation, the first cash flow does not have to be negative.

If you multiply all of the cash flows by -1 (i.e. flip their signs consistently), you will see that the NPV curve initially transitions from -ve to +ve. And might be only a "sufficient", but not "necessary" condition. (TBD)

2. Do you think there's any chance such an NPV curve can cross the zero value twice?

Absolutely! That's the problem with the mathematical IRR: there can be multiple IRRs. Also, there might not be any IRRs.

3. Is this a silly approach to solving [the] problem?

I try not to judge. But it might be simplistic and incorrect, in general. At the very least, it must be arbitrary by definition when there are multiple IRRs. (Unless your implementation can return a possible array of discovered IRRs.)

I'm hoping by using Excel 365's LET and LAMBDA functions [...] to be able to [provide] a single-celled formula solution

By choice, I don't speak LAMBDA and the other new Excel 365 features. So I cannot comment on the feasibility of a single-cell solution.

But if all your algorithm does is (try to) choose an appropriate guess, I think it is a futile effort with respect to the Excel XIRR function per se.

Excel XIRR is unreliable. Even with a "best" guess to 15 significant digits, XIRR might return #NUM or another indicator of failure. Or it might return the "wrong" IRR; that is, not the closest IRR to the guess.

And IMHO, that failure "cannot" be due to the documented limitations on the internal algorithm. Often, my own "XIRR" implementation succeeds within those limitations when Excel XIRR fails.

Moreover, oddly, Excel IRR is more reliable. Often, it succeeds when Excel XIRR fails, even when the XIRR dates are equally separated by 365 days (the divisor that XIRR uses). A contrived condition, admittedly. But in that case, both Excel implementations should be working with the same NPV formula, mathematically.

I think that demonstrates that there are inherent flaws in the XIRR implementation.

 

[hali]

@hali , it's only fair to tell people where you've cross posted to. Have you posted about the same topic elsewhere?

My apologies. I have also posted in Microsoft Community.

 

[vletm]

Moderator Note for hali
Please reread Forum Rules. Those are for You too.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[hali]

Here is the link of cross post in Microsoft community
https://answers.microsoft.com/en-us...-results/bf981dd4-d456-466d-b38d-74cca5da384f

 

[joeu2004]

Thank you for paying attention. So what is the ultimate solution. What guess to used as 0 does not give the consistent results based on my 3 examples.

I assume those comments are addressed to me. In this forum, it is difficult to tell unless either you quote part of the posting that you are responding to, or you write something like @joeu2004 .

First, note that "no guess" is a default guess of 10%, not zero.

I do not believe there is a single guess that "works" for all situations, especially for Excel XIRR.

(See my comments about XIRR unreliable in posting #18.)

And note that sometimes there are multiple IRRs for the same cash flow model. Which IRR is the "correct" one?

Some people expect a negative IRR when the sum of the cash flows is negative, and a positive IRR when the sum is positive.

But as appealing as that might seem, it is based on a fallacy.

Consider a loan of $100,000 with 12 payments of $8900. In this case, we can use Excel RATE to calculate the IRR. But I'll stick with Excel IRR to avoid confusion.

The cash flows can be either {-100000,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900}
or {100000,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900}.

Arguably, it depends on your point of view: lender or borrower. But actually, the choice of signs is arbitrary, as long as inflows and outflows have opposite signs.

SUM(-100000,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900) returns 6800.

SUM(100000,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900) returns -6800.

So perhaps you would expect the IRRs to have opposite signs.

But in fact, both IRR({-100000,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900})
and IRR({100000,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900}) return the same sign.

(In this case, their values differ in the last decimal place.)

-----
PS.... For your 3 examples, the following seems to work.

=XIRR(values, dates, IF(SUM(values)<0, -10%, 10%))

As I said, that is based on a fallacy. And it is not a panacea, since even those guesses do not work in all cases.

Perhaps you will get lucky, and that works for all of your projects.

 

[p45cal]

I'm hoping by using Excel 365's LET and LAMBDA functions, with their improved efficiency of having only to calculate values/arrays once, to be able to give @hali a single-celled formula solution which isn't too resource hungry.

While I don't intend to post them because they'll be no more valid than @joeu2004 's =XIRR(values, dates, IF(SUM(values)<0, -10%, 10%)) , I did manage to produce lambda versions of XIRR based on the NPV curves crossing 0 for both of @hali 's attached files.

re:

Unless your implementation can return a possible array of discovered IRRs.

I might get the inclination to try such a thing!

re:

Often, my own "XIRR" implementation succeeds within those limitations when Excel XIRR fails.

May I see that implementation?

Perhaps a vba user defined function would be acceptable to @hali ?
I would need an algorithm though, and I'd struggle to interpret MS's:

and it might be pointless if it gives the same results as XIRR!
It would, for example, be easier to implement an iterating algorithm to find multiple crossings of zero of the NPV curve in vba than in a sheet formula. A simplistic approach could be to do many NPV (SUMPRODUCT) calculations with small increments of rates, but I feel sure there'll be a better way.

 

[hali]

I assume those comments are addressed to me. In this forum, it is difficult to tell unless either you quote part of the posting that you are responding to, or you write something like @joeu2004 .

First, note that "no guess" is a default guess of 10%, not zero.

I do not believe there is a single guess that "works" for all situations, especially for Excel XIRR.

(See my comments about XIRR unreliable in posting #18.)

And note that sometimes there are multiple IRRs for the same cash flow model. Which IRR is the "correct" one?

Some people expect a negative IRR when the sum of the cash flows is negative, and a positive IRR when the sum is positive.

But as appealing as that might seem, it is based on a fallacy.

Consider a loan of $100,000 with 12 payments of $8900. In this case, we can use Excel RATE to calculate the IRR. But I'll stick with Excel IRR to avoid confusion.

The cash flows can be either {-100000,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900}
or {100000,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900}.

Arguably, it depends on your point of view: lender or borrower. But actually, the choice of signs is arbitrary, as long as inflows and outflows have opposite signs.

SUM(-100000,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900) returns 6800.

SUM(100000,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900) returns -6800.

So perhaps you would expect the IRRs to have opposite signs.

But in fact, both IRR({-100000,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900,8900})
and IRR({100000,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900,-8900}) return the same sign.

(In this case, their values differ in the last decimal place.)

-----
PS.... For your 3 examples, the following seems to work.

=XIRR(values, dates, IF(SUM(values)<0, -10%, 10%))

As I said, that is based on a fallacy. And it is not a panacea, since even those guesses do not work in all cases.

Perhaps you will get lucky, and that works for all of your projects.

Thank you. The suggestion XIRR(values, dates, IF(SUM(values)<0, -10%, 10%)) works correctly for my three examples. I hope, it will work for my rest of data as well.