What is IRR? Detailed and Easy Method to Calculate IRR in Excel

What is IRR? What is IRR used for?

Definition of the IRR index

The IRR index, or internal rate of return, is the discount rate at which the net cash flow of a project is zero.

IRR represents the return on investment based on the assumption of reinvesting cash flow in subsequent years, at a calculated interest rate. The IRR index shows the maximum profit potential of the capital invested in the project.

The higher the IRR, the more effective the project is. When IRR is higher than the initial discount rate, it means that the investment project is profitable.

What is the IRR index used for?

• IRR is applied to measure the feasibility of a project.
• It is a tool that helps investors predict profitability, thereby making investment decisions.

How to calculate the IRR index

In which:

• Co: Initial investment cost.
• Ct: Net cash flow at a specific time.
• IRR: Internal rate of return.
• t: Project implementation time.
• NPV: Net present value.

How to calculate IRR in Excel

This article is performed on the Windows operating system, Excel 2016. For macOS and other versions of Excel, the steps are similar.

IRR calculation function syntax

=IRR(values, [guess])

In which:

values: The range referencing the cells you want to calculate the internal rate of return for.

guess: A number you think is close to the result of the IRR.

Note:

• For values:

+ There must be at least one positive value and one negative value.

+ Enter the values in the order you want.

+ If an argument contains text, an empty cell, or logical values, they are ignored.

• For guess:

+ Excel uses an iterative technique to calculate the IRR index, starting with the guess value. IRR will loop and calculate until the result is accurate within a range of 0.00001%. If no result is found after 20 attempts, the IRR function returns an error value of #NUM!. In this case, you need to try a different value for the guess.

+ You are not required to provide a guess value for IRR calculation. If you leave the guess value blank, it is assumed to be 0.1.

How to use the IRR function

You can refer to the example below: Use the IRR function to calculate the internal rate of return in the following table:

Step 1: Enter the formula =IRR(C4:G4)

Explanation of the formula:

• values: The range derived from cell C4 to G4.
• guess: If you do not enter a guess value, Excel will understand it as 0.1, which is 10%.

Step 2: Press Enter to check the result.

Relationship between IRR and NPV

What is the NPV index?

NPV stands for net present value, which represents the net present value, showing the difference between the present value of cash inflows and the present value of cash outflows.

NPV helps investors measure the present value of a project, simply understood as the project’s profit.

Relationship between the two indexes

IRR is the solution to the equation NPV=0. From this, we can infer the relationship between these two indexes:

• No solution equation, no IRR: The method cannot be used.

• Multiple solutions equation, meaning multiple IRRs: You cannot determine a benchmark for comparison.

You should only use IRR to evaluate an independent project. If you want to compare between two projects, you should use NPV. Although the IRR index is quite simple and easy to understand, for long-term projects with multiple cash flows with multiple discount rates or uncertain cash flows, the NPV index will be a more appropriate choice.

Conclusion

Now you have a better understanding of the IRR index, how to calculate IRR in Excel. Hope the above information will help you complete your work easily.