LinkBack URL
About LinkBacks
I am getting a value of 0.00000030% when using XIRR formula with no guess. Any help is appreciated
I am getting a value of 0.00000030% when using XIRR formula with no guess. Any help is appreciated
It's the formatting of your cell.
The result is 2.98023223876953E-09 which is a zero I guess but you get this value potentially due to floating point calculation.
To 'fix' floating point errors, you can use ROUND:
=ROUND(XIRR(A2:GR2,A1:GR1),2)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you!
So, 0% is an acceptable output? I was under the impression that 0 as an output for XIRR meant that there was an error.
Well, things get a bit complicated here and maybe someone with a bit more math background can step in.
You have a polynome of a very high order here; a polynome of degree n = 200 can have up to 200 zeros, i.e. IRR if I remember correctly.
But it may have as well only 1 or 2 (or none) which are usually the desired real-life cases.
Looking at your figures, the sum of all numbers is around -225,796.65 which points to a serverly negativ IRR/zero, and potentially infinitely large negative.
However, at the same time you initial value is -0.00000001.
The guess of an IRRyields a #NUM!-error which might indicate that there's no solution/zero/IRR to your number pattern.
Please Login or Register to view this content.
And the 2.98023223876953E-09 result that you see may be more like the Excel result of 'giving up', i.e. not being able to find a solution within a reasonable amount of iterations (which I think follows the Newton or similar approach).
Thank you! I felt like I was going crazy because I even tried getting the NPV to put a guess into the XIRR formula and was getting 0 there too.
Attachment 866005
Selecting attachment 866005 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
I'm not knowledgeable in financial modeling, but I understand that XIRR() attempts to determine a rate that results in an XNPV() of 0. If I open the file in the OP and enter =XNPV(A6,$A$2:$GR$2,$A$1:$GR$1) into B6, and then enter different rates in A6, it appears that the XNPV() function for this data set approaches 0 asymptotically as the rate approaches infinity (rate=-99%, xnpv=-infinity; rate=10,000,000,000%, xnpv=-1.8E-5). For the data given, are you certain there is a solution? It appears to me that there is not a solution for this data set, and XIRR() returns that 3E-9 result as an indicator that it failed to find a valid solution (I'm not sure why Excel doesn't return an error value here).
Whenever I encounter these kinds of questions in the forum, the first thing I do is explore the behavior of the XNPV() function. That usually gives an indication of what the solution might be, and when no solution exists.
Originally Posted by shg
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks