How to Calculate IRR

Calculate internal rate of return

There are many ways to evaluate whether it is worth proceeding with a business project. The internal rate of return method allows you to compare the return on your cash outlays for a project with the return you would get investing that money somewhere else. For example, suppose you have $5,000 to invest in your business. You are considering whether to purchase a new piece of equipment that would save time and money in the manufacturing process, or to simply invest the money. If the investment gives you an annual return of 6 percent, you would instead invest the $5,000 in new equipment if the project saved you more than 6 percent. You can also use the IRR to compare two competing projects to determine which will provide the better return on your cash investment. Calculating the IRR is difficult to do manually, but is easy with a spreadsheet program like the one we've taken as our example, Microsoft Excel.

Things You'll Need

• Microsoft Excel

Instructions

1. Set up a new spreadsheet with each row showing the cash inflows and outflows by year. For example, Row 1 would have the cost of the new equipment at time zero (the date of purchase). Row 2 would have the expected manufacturing cost savings in year 1. Row 3 would have the savings in year 2, etc.

2. Choose "Insert/Function" from the main menu. Select "All Functions" and choose "IRR" from the list.

3. A box will appear that asks you to define the values, and for a guess. Highlight your cash inflows and outflows in your spreadsheet to be the relevant values. For a guess, choose the default of .10. There will almost never be a need to make another, different guess, unless you are dealing with multiple IRRs.

4. The formula calculates your internal rate of return (IRR) in percentage format and inserts the answer into your chosen cell. If you do not wish the formula to round the percentage, choose "Increase Decimal" on your toolbar to add decimal places.

5. Compare the calculated internal rate of return (IRR) to your benchmark rate for investments. For example, if your other alternative is to invest that initial capital outlay in another investment, you would compare the IRR to the rate you would earn on another investment. If the rate of return is higher than your benchmark rate, you would choose to invest in the project. If you are calculating IRRs on multiple potential projects, you would choose the project with the highest IRR.

Tips & Warnings

• Your values have to contain at least one positive value and one negative value in order to calculate the IRR.

References

• Microsoft Office Online: IRR