How to Calculate IRR in Excel

Microsoft Excel is spreadsheet software used for everything from basic calculations to complex computations. One of the features of Excel is its built-in formulas, including the complex calculations for IRR (internal rate of return). Businesses use IRR to determine whether or not to undertake a project.

...
You can calculate internal rate of return using Excel.

Step

Open Microsoft Excel. Create a two-column table, with one column titled "Year" and the second column titled "Cash flow." For this example, assume $75,000 has been invested in a business. The return from that $75,000 will be tracked over a 10-year period. Initially, the return on the investment will be small, but over time it should grow.

Step

Enter the numbers 1 through 10 in the "Year" column.

Step

Enter the following numbers for each of the years: "-75,000, 2,000, 4,000, 5,000, 7,000, 10,000, 20,000, 100,000, 250,000, 325,000". The "-75,000" should be in the row for Year 1; 2,000 should be in the row for Year 2, and so on. The "-75,000" is entered because that is the amount spent/invested in the business. The other numbers are positive because the hope is that there will be a positive, increasing return on the investment.

Step

Enter "=irr" in Row 12.

Step

Select the "Cash Flow" column values for the calculation.

Step

Enter "0.2" as a guess. The guess is that the return on the investment is 20 percent. Then enter the closing parenthesis of the formula. If the guess portion of the formula is omitted, Excel assumes the guess is 0.1, or 10 percent.

Step

Press "Enter." The internal rate of return for the investment is 34 percent. This means that the return on the $75,000 investment is 34 percent over the course of the 10-year business.