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.
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.
Enter the numbers 1 through 10 in the "Year" column.
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.
Enter "=irr" in Row 12.
Select the "Cash Flow" column values for the calculation.
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.
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.
Tips & Warnings
- The above example uses Microsoft Excel 2007. However, the formula and concept are the same for other versions of Excel.
- Compare the rate of return on a project with rates of return on other projects and use this as a factor in budget/expense decisions.
- The rate of return on a project or investment may not always be positive. In those cases, the IRR will be a negative percentage.