LinkBack URL
About LinkBacks
Hi. I do not why but IRR returns a #NUM error. What is the problem here?
Thank you
Hi. I do not why but IRR returns a #NUM error. What is the problem here?
Thank you
It is because the negative values exceed the positive values.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
It is perfectly okay for negative values to exceed positive values, and vice versa. For example, =IRR({-15000,-10000,10000,10000}) results in -10.0788107060756%.
-----
The real problem is that the signs of the first and last cash flows are the same. They are usually different.
First, the worksheet seems to be incomplete. The PV formulas in A2:A4 reference G2, which is blank. Consequently, the PV discount rate is effectively 0%.
Second, the cash flows in an IRR model are usually "current dollars", not discounted amounts. That is the purpose of IRR: to determine the average discount rate.
In any case, without any explanation of what the values in A1:A4 are intended to represent, we really cannot help you remedy the #NUM error.
With the current cash flow sequence {-15000,10000,10000,-10000}, I believe there is no IRR.
In this case, that is what #NUM is telling you. I believe there is no "guess" discount rate that will improve the result.
-----
Perhaps this will help.
The first cash flow is usually an initial investment. You can choose plus or minus values; it's arbitrary. Let's choose minus sign.
The subsequent cash flows are additional net investments (signed the same as the initial investment) or net withdrawals (with the opposite sign). They might be zero for periods with no net cash flow.
But note that the cash flows are amounts of net change, not an account balance.
The last cash flow is the ending balance, usually signed as a net withdrawal.
Last edited by joeu2004; 11-05-2020 at 09:58 PM.
?Originally Posted by Glenn Kennedy
IRR({-2,1}) and IRR({2,-1}) are both -50%. The ONLY requirement for there to be an IRR is that there be at least one sign change in the cashflows, that is, at least one positive number and one negative number. More of one or the other is irrelevant. Also, if X is a valid array of cashflows, -X is also a valid array of cashflows, and X and -X have the same IRRs because the array of discount factors D = {d[k] = (1+IRR)^-k, k = 0..N} viewed as a vector is orthogonal to both X and -X viewed as vectors. This is pure geometry or linear algebra (in this case, they're effectively the same).
That's the math. When there are pathological cashflows like {-1.5;1;1;-1}, there are still geometric IRRs, but they're meaningless financially. In this case, IRR = -189.9211893%. For what little it's worth, LibreOffice Calc returns -1.89921189293924 and Google Sheets return -1.899211893. Excel bails out when the IRR would be less than -100%.
In this case, the #NUM! error means you have a financially meaningless result, less than 100%. The meaning of an IRR less than 100% is that one of the parties to the cashflow (viewed as between 2 people) is willing to pay extra for the opportunity of a 100% loss. Considering your cashflows, you invest 15,000 at time 0. You receive 10,000 each at times 1 and 2. Then you pay 10,000 at time 3 for the thrill of the previous 3 cashflows. Had the cashflows been {-15000;-10000;10000;10000}, the IRR would have been -10.0788107060757%. Why does {-15000;10000;10000;-10000} have an IRR less than 100%? To be blunt, IRR IS MEANINGLESS. All it is is a rate derived from a vector orthogonal to the vector of your cashflows, which means that your cashflow using the IRR as a discount rate produces a zero NPV. While NPV is meaningful for discount rates from -100% to +infinity, it's not meaningful for discount rates less than -100%.
In short, the correct economic interpretation is that no rational economic actor would want this particular cashflow.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks