Finding Interest Rate / IRR

Your sample file does not include any of your attempts at calculating XIRR(), so it is difficult to know exactly what kind of difficulty you are having. If I guess correctly, this kind of problem shows up occasionally on the forum. I am not as knowledgeable as others (user Joeu2004 seems to know a bit about these financial calculations), but there seems to be a certain amount of "instability" in the algorithms behind Excel's XIRR() implementation. A couple of previous discussions

https://www.excelforum.com/excel-gen...rr-return.html
https://www.excelforum.com/excel-for...ributions.html

In both of these cases, the "problem" with the XIRR() function could be fixed by selecting an appropriate value for the optional "guess" parameter. You can read through Joeu2004's posts to see the algorithm he uses to come up with a suitable guess value.

Interestingly, in the one discussion, I tried using Open Office instead of Excel, and did not experience the same problem as was reportedly found in Excel. Since some of these discussions often revolves around instabilities in Excel's implementation, perhaps there is value in trying a different spreadsheet application or other programming language.

Originally Posted by ElmCityKid

I have trouble finding the IRR using the XIRR function for a stream of payments that are negative in the beginning period, positive in a middle period, and negative again in the ending period.

That is over-generalized. I have no problem using XIRR with the cash flows in rows 32:55, all of which have the pattern that you describe.

(I also have no problem with rows 7 and 56, which have different patterns.)

The problem with rows 8:31 is as "MrShorty" explained: it is due to the flawed implementation of Excel XIRR.

But that might be the point of the assignment.

-----

First, use XIRR correctly. Enter the formula =XIRR(C7:AZ7,$C$6:$AZ$6) into BA7, and copy down the column.

Note the use of relative and absolute references.

Typically, format BA7:BA56 as Percentage with 2 decimal places.

Note that BA8:BA28 appear to be 0.00%. Actually, if we format as Scientific with 2 decimal places, we see that the value is about 2.98E-09. In my experience, that has the same meaning as #NUM.

Also note that BA29:BA31 return #NUM. Usually, that means XIRR is unable to find an IRR; that is, a discount rate that causes XNPV to return nearly zero.

Often, we can correct the #NUM error by entering a "guess". As "MrShorty" suggests, there are a number of ways we might do that.

But the results might still be inappropriate.

The method that I demonstrated in the postings that "MrShorty" refers to is not easy to apply to a large number of formulas. Alternatively, I used a different method, which might not always work. Since the cash flows are nearly monthly, I calculate a monthly IRR with the formula =IRR(C7:AZ7) in BC7, and copy down the column. Then I use (1+BC7)^12-1 as a "guess" in the XIRR formulas in column BA.

------

More to the point, a "proper" implementation of XIRR does not require a "guess" in these examples.

To demonstrate, enter the VBA procedures below into a normal VBA module. In a VBA window, click Insert > Module, then copy the code below and paste into the VBA edit window.

Then enter the formula =myXIRR(C7:AZ7,$C$6:$AZ$6) into BA7, and copy down the column. Format BA7:BA56 as Percentage with 2 decimal places.

Note that an IRR is computable in all cases.

It is also prudent to check the IRR to be sure that NPV is nearly zero.

Enter the formula =XNPV(BF7,C7:AZ7,$C$6:$AZ$6) into BB7 and copy down the column. Format BB7:BB56 as Scientific with 2 decimal places.

Note that the values in BB7:BB56 are zero or between +/-2.27E-13 and +/-1.978E-11. That is "nearly zero".

This NPV double-check is less important to do when using myXIRR because my implementation correctly ensures that the NPV is nearly zero. In contrast, in my experience, Excel XIRR sometimes returns values that do not cause the NPV to be nearly zero.

My XIRR implementation uses the exact derivative, which is computable in this case.

I suspect the Excel XIRR implementation uses a method for estimating the derivative. That method does not work well in some cases.

-----

Please Login or Register  to view this content.

I had a chance this morning to try your spreadsheet with the built XIRR() function (with relative and absolute references like joeu2004 showed) in OpenOffice. I got non-zero, non-error results for each row. As another test, I copied your data into a Google sheets spreadsheet and entered the XIRR() function and also got the same non-zero, non-error results. (Check my results, is Agent 25 supposed to return 3.60%?)

As joeu2004 keeps saying, Excel's XIRR() implementation seems flawed. I see many who will quip that "friends don't let friends use Excel for statistics." Is it time to start adding, don't trust Excel for XIRR() calculations?

Originally Posted by MrShorty

Check my results, is Agent 25 supposed to return 3.60%?

More precisely, 3.59659240544724% (plus 2.78E-17). Then XNPV returns about -3.64E-12 or 4.55E-13, depending on whether or not you round to 15 significant digits. Infinitesimally close to zero in either case.

Originally Posted by MrShorty

Is it time to start adding, don't truest Excel for XIRR() calculations?

Yes. But remember: the mathematical IRR is also flawed because there can be multiple IRRs or there can be no IRRs.