How does the xirr formula work

Cells C2-C7 show the dates for the cashflows. Note that, in the above example, the initial investment is a negative value as this is an outgoing payment , and the income payments are represented by positive values.

For further information and examples of the Excel Xirr function, see the Microsoft Office website. Excel Functions. XIRR values , dates , [guess]. 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. Was this article helpful? Have a query? ITR Resources. Mutual Fund Resources. Terms Privacy Legal. It shows the average rate earned by every cash flow that you invest during a period.

Useful for measuring the return from lumpsum investment in mutual funds. Clear instructions. Easy to follow. No jargon. Pictures helped.

Didn't match my screen. Incorrect instructions. Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. Thank you for your feedback!