Why does xirr return 0

I look at the NPV curve for varying discount rates. For example:. But it might not be! And However, sometimes the NPV curve is so steep or variable that we need to choose a very close "guess". Was this reply helpful? Yes No. Sorry this didn't help. Thanks for your feedback. How satisfied are you with this reply?. The XIRR function calculates the internal rate of return for a series of cash flows that occur at irregular intervals. Payments are expressed as negative values and income as positive values.

If the first value is a cost or payment, it must be a negative value. Subsequent payments are discounted based on a day year. To calculate the internal rate of return for a series of regular, periodic cash flows, use the IRR function.

Starting with guess which defaults to 0. The XIRR function takes three arguments : values , dates , and guess. Values represent a series of cash flows. The first value is optional and corresponds to a cost at the beginning of the investment.

If the first value is a cost or payment, it must be a entered as a negative number. It might also be helpful if you "show your work" for the manual calculation that you believe returns the correct negative IRR. That is, show the data values and dates and formula that you use. For example, To avoid msunderstandings, I suggest that you format the cell as Scientific with 14 decimal places. Nevertheless, those negative value still might not be the negative IRRs that you expect, based on manual calculations.

It is a defect, IMHO. I read somewhere on on this forum that XIRR doesn't work properly when the cashflows are zero, hence, they suggested this power function to make it work.

I just adopted it. Joined Jul 2, Messages 4, You need to reduce the array of values to be considered to only those which match your criterion. Thank you so much. It works. I don't quite understand this function that well especially mode. XOR LX said:. To make it easier to explain, using the workbook you linked let's change the entry in J5 from "Kazakhstan" to "Poland", and also amend the upper row reference in the formula to just 20, i.

Unfortunately, however, it also returns an error if no one value within that range occurs more than once. Hence the "artificial" doubling of our values, which ensures that our array is of a valid form to pass to MODE. And, importantly, no extraneous entries surrounding those values.

I prefer to use edate when adding dates. Are you certain your formatting is right for the cash flows? Seems to work for other calculations just not this stream of cash flows.

Does this seem like the right way to finding the answer? Any help would be great, thanks. IRR is only useful for comparing two mutually exclusive projects.