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.

Step

Open a new Microsoft Excel 2010 spreadsheet.

Step

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."

Step

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.

Step

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.

Step

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.