How does xirr work

The Internal Rate of Return indicates the profitability of an investment and therefore is commonly used in business, when choosing between investmentments. This calculation uses a schedule of payments including an initial investment and a series of net income payments , to calculate the compounded return, assuming the Net Present Value of the investment is zero.

The value of the XIRR is calculated as the value of rate that satisfies the following equation:. An array of values or a reference to a range of cells containing values representing the series of cash flows. A series of dates, corresponding to the supplied values.

An initial guess at what the IRR might be. This is an optional argument, which, if omitted, takes on the default value of 0. By default, January 1, is serial number 1, and January 1, is serial number because it is 39, days after January 1, In most cases you do not need to provide guess for the XIRR calculation. If omitted, guess is assumed to be 0. Excel uses an iterative technique for calculating XIRR. Using a changing rate starting with guess , XIRR cycles through the calculation until the result is accurate within 0.

The rate is changed until:. Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? 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.

As you can see in the below screenshot, the IRR goes from 7. The difference seems too high to be justified by the time value of money factor alone:. As an additional check, let's use the IRR function on the same cash flows.

Please keep in mind that IRR will also compute an approximate rate because it assumes all the time periods to be equal:. As the result of these calculations, we get a monthly XIRR of 7. To quickly get the internal rate of return for different projects, you can create a versatile XIRR calculator for Excel.

Here's how:. Where Sheet1 is the name of your worksheet, A2 is the first cash flow, and B2 is the first date.