Locked lesson.
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.
- 00:03 Hi, this is Ray Sheen.
- 00:04 It's now time to talk about the ROI technique called internal rate of return.
- 00:09 This technique helps us to understand the quality of the investment
- 00:12 represented by the project.
- 00:16 The Internal Rate of Return, or IRR, will bring an investment focus to ROI.
- 00:21 This technique can be used to judge the investment value.
- 00:25 You can think of the IRR as the interest rate.
- 00:27 That would be needed for the yield on an investment.
- 00:29 The size of the project to be equal to the project benefit.
- 00:33 The other ROI techniques have given answers in time units and money.
- 00:37 With this technique, the answer is in equivalent interest rate.
- 00:41 The IRR rate will use the annual cash flows from the ROI spreadsheet and
- 00:45 the discounting methodology to determine the rate of return.
- 00:49 The cash flows from some set term of years are discounted
- 00:52 to the point that their total is zero.
- 00:54 The discount rate that creates that condition is the IRR.
- 00:58 The IRR is often used by investors when considering whether
- 01:01 to invest in a company's project.
- 01:03 Since most business projects are internally funded, it is not used as much
- 01:07 within a company, however if a company must borrow money to do a project, or
- 01:11 if the business is one division of a large holding company, you can expect that those
- 01:15 reviewing and approving the release of funds will want to see the IRR.
- 01:19 It is an investment decision on their part, not an operational decision.
- 01:22 IRR is.
- 01:24 Is another ROI technique that uses the principle of discounted cash flow.
- 01:28 Discounted cash flow considers the opportunity cost of the money invested in
- 01:31 the project.
- 01:33 The opportunity cost is the income or savings that the money invested in
- 01:36 the project could have earned and the best alternative that could be considered.
- 01:40 Discounting cash flows will convert the money in future years
- 01:43 to a discounted level based upon the discount rate used.
- 01:46 In the NPV ROI method, the discount rate is specified by finance.
- 01:51 In the IRR method, we solve the equation to find the discount rate.
- 01:55 Discounted cash flow is used for projects with long-term cost and benefits.
- 01:59 It is unlikely that you would need to calculate an IRR for short,
- 02:03 simple projects.
- 02:04 You will probably be using payback period or break-even point for
- 02:07 that type of projet.
- 02:08 Longer projects in excess of two years
- 02:11 are more likely to rely on a discounted cash flow ROI technique.
- 02:15 The two ROI techniques that use net discounted cash flow
- 02:18 are net present value and internal rate of return.
- 02:21 So let's look at the ROI calculation.
- 02:23 Like with our other techniques, create an ROI worksheet, and spread the costs and
- 02:28 benefits through the number of years, For the IRR calculation.
- 02:31 The cost of benefits for each year are summed as the period totals.
- 02:35 Here's the equation.
- 02:36 It is essentially the same equation as used by MPV
- 02:40 only in this case instead of specifying the discount rate and
- 02:43 solving for MPV total, the MPV total is set at zero.
- 02:47 And the equation is solved to find the internal rate of return, or
- 02:51 equivalent discount rate.
- 02:53 Now, even though the other ROI calculations are often done in
- 02:56 a spreadsheet, they can be done by hand with a little bit of effort.
- 02:59 This one is a different story.
- 03:01 If you are calculating a six year IRR,
- 03:04 you must solve a sixth-order partial differential equation by hand.
- 03:08 Good luck.
- 03:09 So we definitely want to use the spreadsheet IRR function to
- 03:12 solve this one.
- 03:14 Fortunately, in Excel it is an easy formula.
- 03:16 Just include all the annual cash flows inside the function,
- 03:19 and the spreadsheet will take care of that partial differential equation.
- 03:23 One caution, the IRR function will not solve unless the first year period
- 03:27 total is a negative number.
- 03:29 If the project pays back so fast that the first year is already positive,
- 03:33 there's no need to do a discounted cash flow.
- 03:36 Let's look at our example again.
- 03:37 The project costs for the new sales order management system is $450,000.
- 03:42 This project starts to provide benefit in year two with both productivity savings,
- 03:46 and new sales.
- 03:48 We'll do a six year IRR in this project.
- 03:51 Plugging the numbers into the equation gives the answer of 70% IRR,
- 03:55 a pretty good return rate.
- 03:57 At least it's better than what my bank pays me in my money market account.
- 04:01 The IRR technique will provide an indication of the quality
- 04:06 of the investment.
- 04:08 If reviewing your ROI with an outside investor, they will want to see your IRR.
Lesson notes are only available for subscribers.
PMI, PMP, CAPM and PMBOK are registered marks of the Project Management Institute, Inc.