Wonky XIRR Return

Originally Posted by shg

The forum rules explain how to attach a file.

Thanks! I uploaded the spreadsheet to my original post.

Originally Posted by Drymr

the attachment button above isn't working

Yeah, I don't why the forum owners don't get that fixed.

To attach a file, click Go Advanced, scroll down, and click Manage Attachments.

Originally Posted by Drymr

iterative calculation is checked

Don't do that. I know: you probably saw the suggestion online; maybe even in a MSFT article. It is a misdirection; it causes more trouble than it's worth. Enabling Iterative calculation allows unintentional circular references to go undetected.

Originally Posted by Drymr

I have a long XIRR calculation that keeps returning 0.00% even though the negatives are significantly outweighed by the positives (-2,607,471 and +4,661,616).

If you format the cell as General or Scientific, you will probably see that XIRR returns about +/-2.98E-09. Although that is close to zero, it is an arbitrary result that XIRR uses (or the algorithm degenerates to) to signal an error or noncomputable IRR. Sometimes, we should interpret 2.98E-09 to mean the same as #NUM or #DIV/0, to wit: a "guess" parameter is required.

Often, the situation arises because the cash flow model is wrong due to a misunderstanding. We would need to see the cash flow data (values and dates) along with an explanation of the cash flows in order to get some insight. That might be self-evident if and when you attach an example Excel file that demonstrates the problem.

But if the cash flows are correct and XIRR simply requires a "guess" parameter, the following demonstrates a procedure that I use to determine a "reasonable" guess.

Please Login or Register  to view this content.

With the cash flow model in columns A and B, create a table of discount rates from -99% to 100% (or more) in column E, and calculate the corresponding NPV in column F.

Unfortunately, Excel XNPV does not permit negative discount rates; a design flaw. So must use SUMPRODUCT to calculate the NPV.

Where the NPV sign changes, it is possible that NPV=0 is in between. Use the average discount rate as the "guess" parameter.

This is demonstrated in C3.

But note that C4 calculates a very different IRR. Yet, we can use Goal Seek to find the appropriate IRR in E24. This demonstrates one of the limitations of the Excel XIRR implementation. Consequently, even with a "reasonable" guess, sometimes Excel XIRR does not work.

However, in this case, Excel XIRR stumbled upon a third valid IRR, as demonstrated by the fact that the corresponding NPV in F25 is nearly zero.

The example demonstrates one of the issues with the mathematical IRR, to wit: for some cash flow models, there might be zero or multiple IRRs.

How do we choose the "right" one? That is a subjective exercise. There is no "right" answer.

Originally Posted by Drymr

Thanks! I uploaded the spreadsheet to my original post.

The first cash flow is zero. There should be nothing wrong with that. But Excel XIRR apparently does not like it. It returns about 2.98E-09 in that case.

(That's all I have time to say at the moment.)

Originally Posted by shg

If you change the formula to start with the first non-zero value,

=XIRR(E2:DP2, E1:DP1, 0.1)

... it returns 15.04%, which is comfortingly close to what =(1 + IRR(E2:DP2))^12 - 1 returns.

Huh... I just did that and it worked. Does that mean XIRR calculations always need to start with a non-zero number?

Originally Posted by Drymr

Does that mean XIRR calculations always need to start with a non-zero number?

Yes. But that is only a limitation (defect, IMHO) of the Excel XIRR implementation. Note that Goal Seek can find an IRR for the original cash flow model (first CF=0) using the following formula:

=SUMPRODUCT(A2:DP2/(1+A5)^((A1:DP1-A1)/365))

GS finds an IRR at 15.0416756768281%.

(As noted previously, I avoid using Excel XNPV just in case the IRR is negative, which Excel XNPV does not permit.)

Originally Posted by joeu2004

Yes. But that is only a limitation (defect, IMHO) of the Excel XIRR implementation. Note that Goal Seek can find an IRR for the original cash flow model (first CF=0) using the following formula:

=SUMPRODUCT(A2:DP2/(1+A5)^((A1:DP1-A1)/365))

GS finds an IRR at 15.0416756768281%.

(As noted previously, I avoid using Excel XNPV just in case the IRR is negative, which Excel XNPV does not permit.)

On my spreadsheet A5 is blank. Why is A5 in your formula?

Originally Posted by shg

There nothing obvious to me about why it shouldn't work, but it doesn't make sense to me that an IRR calculation would with start with zeros -- the period before any cash flow occurs in either direction.

It starts with zeros and ends with zeros because it's part of a larger spreadsheet where payment dates depend on a calendar I've created in excel so if I change an important date in the calendar a payment date would change. For example, if I were to say that I'm purchasing a property in August 2017 instead of the current December 2017 then A2 would have $1,325,289 instead of E2.

If you're telling me that an XIRR calculation can't start with a zero then that means I can't create an organic spreadsheet where changing the calendar dates will change the IRR. I'm going to have to manually input the first XIRR cell to correspond to the calendar date selected. That's pretty disappointing considering everything else on the spreadsheet works perfectly.

Originally Posted by macropod

Cross-posted at: https://www.mrexcel.com/forum/excel-...rr-return.html

Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html

Sorry about that. I was not aware of the policy. Thanks for pointing it out.

Originally Posted by joeu2004

Note that Goal Seek can find an IRR for the original cash flow model (first CF=0) using the following formula:
=SUMPRODUCT(A2:DP2/(1+A5)^((A1:DP1-A1)/365))
GS finds an IRR at 15.0416756768281%.

Originally Posted by Drymr

On my spreadsheet A5 is blank. Why is A5 in your formula?

Sorry. I had one foot out the door (I have v-e-r-y long arms :->), so I didn't have time to explain the details.

A borrowed A5 to be the "by changing" cell for Goal Seek. I put the SUMPRODUCT formula into B5. So the Goal Seek set-up is:

Set cell: B5
To value: 0
By changing cell: A5

I am not suggesting that you use Goal Seek as a work-around. It was just a quick-and-dirty way to demonstrate the concept.

There is no conceptual problem with initial zero net cash flows. It would make a difference if we were calculating NPV with a given discount rate other than an IRR because the first cash flow determines the present-value date.

When the discount rate is an IRR and NPV is exactly zero, we can demonstrate and show mathematically that it makes no difference. It is debatable whether or not to rely on that when NPV is infinitesimally close to zero, which is usually the case.