Why does xirr not work

To check the validity of this equation, let's test it against the result of the XIRR formula. As shown in the screenshot below, the result is very close to zero. To find the internal rate of return for this investment, use this formula:. Where A2:A8 are cash flows and B2:B8 are the dates corresponding to the cash flows:.

To judge the profitability of this investment, compare the XIRR output with your company's weighted average cost of capital or hurdle rate. If the returned rate is higher than the cost of capital, the project can be considered a good investment. When comparing several investment options, please remember that a projected return rate is just one of the factors that you should estimate before making a decision.

For more information, please see What is the internal rate of return IRR? In case you know what kind of return you are expecting from this or that investment, you can use your expectation as a guess. For starters, please remember this — whatever cash flows you are calculating, the Excel XIRR function produces an annual rate of return.

This might not be a good assumption. IRR works best when you trying to measure efficiency. I find XIRR to be problematic if the figure returned is negative. For instance if you push the date of the last postive cash flow further into the future then the XIRR will increase become less negative which is the opposite of what should happen.

The other method you can try with uneven cash flows is to change your time periods to make them short enough that your cash flows are no longer uneven. For instance if your cash flows are once quarterly, but unevenly spaced such as Feb, June, July, then Dec you can shorten your time periods to monthly instead of quarterly and use zero for the months with no cash flows. Related functions. Excel PV Function.

The Excel PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant Excel NPV Function. Excel IRR Function. Email HP. Thank you SO much for putting all of this information on the web!!! This is incredibly helpful for me, who is currently looking for a job, but hasn't kept up to date with Excel for a while.

This is refreshing my memory, teaching me things I should've figured out before, and helping me feel better about myself and my job search!!

Excel video training Quick, clean, and to the point. Log in. Hi All Please note that at the Chandoo. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post. JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding. Problem with XIRR formula. Thread starter Caravishah Start date Jun 2, Caravishah New Member.

Hi, I'm trying to calculate XIRR for individual bets and at the overall portfolio level in the attached sheet. XIRR should only consider the visible range and not complete range. To see overall performance of a portfolios. I'm not sure if it works correctly. Especially if there is a loss i.

XIRR formula. Hi Ravi, Sorry it took me a while to make some sense of your impressive formula in J Never had to use XIRR myself.