## About this lesson

The Internal Rate of Return is a Return on Investment analysis that determines an “equivalent interest rate” that if applied to the investment would yield a similar return as the project is forecasted to return over a set time period.

## Exercise files

Download this lesson’s related exercise files.

Internal Rate of Return (IRR).xlsx18.9 KB Internal Rate of Return (IRR) - Solution.xlsx

18.2 KB

## Quick reference

### Internal Rate of Return

The Internal Rate of Return is a Return on Investment analysis that determines an “equivalent interest rate” that if applied to the investment would yield a similar return as the project is forecasted to return over a set time period.

### When to Use Internal Rate of Return

Like all project ROI techniques, this analysis is done as part of the preparation of the business case used to justify a project. Internal Rate of Return (IRR) analysis is focused on the quality of the investment. This technique can be used with all types of projects from small to large and those focused both on sales benefits and productivity benefits. The only down side with this technique is that it is virtually impossible to calculate by hand, but spreadsheet software has the IRR formula and can do the calculations.

### Instructions

This ROI technique requires the use of a spreadsheet. The columns for spreading the costs are always a year in duration. Also, most companies set these columns based upon their fiscal year. Therefore, unless the project starts on the first day of the fiscal year, the project costs in the first column will represent less than a year’s worth of project effort. The IRR is always done for a predetermined number of years and the project costs and the benefits must be spread appropriately throughout those years.

The IRR technique provides a long term impact for the project because it looks beyond the point of Breakeven or Payback. The number of years to use in the analysis is determined by your business and the project type. For instance, I often use three years for IT projects because the technology changes so fast. I used seven years for new product development projects for a company that made products for use in the construction industry, and I have used 15 to 20 years for large utility projects.

This leads to one of the characteristics of IRR. It does its calculation using a Discounted Dash Flow (DCF) analysis. Discounting is a technique for reducing the value of money in the future and transforming it back to the value of money today. By doing this effects like inflation and the cost of money can be accounted for in the analysis. The IRR takes into account this effect. That is why I referred to it as an “equivalent interest rate.” The IRR includes the interest rate component but it also includes the discounting component. Once the IRR has been determined, Finance will use their estimates for inflation and cost of capital to determine if the IRR rate is adequate.

So let’s talk about the IRR formula. It is identical to the NPV formula only instead of knowing a discount rate and solving for the amount of NPV, in this case we set the amount of NPV to zero and solve for the discount rate. This formula will require two types of information. First is the number of years in the analysis. Second is the annual or period total from the spreadsheet for each of those years (do not use the cumulative).

This analysis can be done using the ROI spreadsheet that we illustrated in the module on ROI. The IRR formula in Excel works great for this calculation. One caution, the total for the first year (column) must be negative for the formula to work. So if you have a very short project that pays back in less than one year, the IRR calculation may not work. So in that case use Payback Period or NPV. The formula in Excel is:

The IRR is often used by a company on large project that require external funding. Banks and investors want to see an IRR to ensure that the project return will be adequate to yield them a profit and keep up with inflation and other business expenses. As on operating manager, I don’t find it to be as useful as the other techniques. But once the spreadsheet is created for NPV or Payback Period, it is very easy to add the formula for IRR and have that value also.

### Hints and Tips

- This technique provides a long range view of the project. The best project from an IRR perspective may not be the best one from a Payback Period or Breakeven Point perspective. Find out from your stakeholders which is more important to them, near term profit or long term profit.
- Do not use the cumulative totals, use the annual totals. This is the most common mistake made when using this technique.
- Some business will want to spread the investment portion of the project costs based upon the annual depreciation values instead of the actual cash flow. This will inflate the IRR slightly, but it will provide a value that is more likely to align with the impact of the project on the Net Income and other financial reports. Therefore if the company is looking for external funding, they will almost always use this approach.

Lesson notes are only available for subscribers.

PMI, PMP and PMBOK are registered marks of the Project Management Institute, Inc.