How to Calculate XIRR in Excel
The Internal Rate of Return, or IRR, is a way to evaluate an investment to see if it is worth your time. Calculating an IRR requires that money coming in and going out are at periodic intervals. If you have money coming in and going out at non-periodic intervals, you'll need to calculate XIRR instead. Both of these calculations can be complicated, so you can use Excel 2010 to quickly give you the number you are looking for.
Open a new Microsoft Excel 2010 spreadsheet.
Click on cell "A1," and enter in your first cash flow value. Money coming in should be entered as a positive number, while money going out should be entered as a negative number. The XIRR function requires that at least one positive and one negative value are included. Select cell "A2," and enter the second value. Continue to enter your cash flow values into column "A."
Select cell "B1," and enter the date that the cash flow value in cell "A1" took place. Continue down the column, placing the date that cash came in, or went out, into these cells.
Click on cell "C1," and enter the following formula, without quotes: "=XIRR(A1:A10,B1:B10)". Change "A10" and "B10" to the last cells in each respective column, then press "Enter" and a number will show up in the cell.
Right-click cell "C1," and choose "Format Cells" from the menu. Select "Percentage" from the list on the left side of the window that appears. Click the up and down arrows next to "Decimal places" to set the number of decimal places you want your XIRR value calculated to. Click "OK" and the number in cell "C1" will be your internal rate of return expressed as a percentage.
Tips & Warnings
- If the XIRR function returns the "#NUM" value, you may need to add a third argument to the XIRR function. This third argument is called the "guess," and it is an approximation of your rate of return. By default, the guess is 0.1, or 10 percent, but you can change this guess if the XIRR function isn't returning a number, as the formula uses the guess as a starting point for its calculations.