How to Use the IRR Function to Calculate Internal Rate of Return in Excel
How to Use the IRR Function to Calculate Internal Rate of Return in Excel: The IRR function is used to calculate the internal rate of return of a cash flow series to evaluate the profitability of an investment or project. This article will guide you on how to use the IRR function in Excel.
1. IRR Function Syntax
The syntax of the IRR function is as follows: =IRR(values, [guess])
Where:
- Values: Required argument. It is an array or references to cells containing cash flow data. The initial investment value should be negative. The subsequent values represent annual profits of the project. Note that these values should be in chronological order.
- Guess: An estimated percentage close to the IRR result, usually defaulted to 10%.
Note:
- Values must contain at least one negative value and one positive value.
- The IRR function uses the order of values as the order of cash flows. Therefore, it’s important to enter payment or income sequences correctly.
- The IRR function only calculates numeric values within arrays or references of values; empty cells, logical values, text, or error values will be ignored.
- Excel uses iteration in the IRR calculation. Starting with the guess, IRR iterates until the result is accurate within 0.00001%. If IRR cannot reach a result after 20 iterations, it returns the #NUM! error value.
- The IRR function has a close relationship with the NPV function because the interest rate returned by IRR is the rate at which NPV equals zero.
2. Using the IRR Function
For example, let’s consider an investment project with an initial cost of 100 million at the beginning of the project. The revenue, expenses, and profits of the project over 5 years are shown in the project analysis table below. We need to calculate the IRR for each year of the project.
In the analysis table, we can see that the initial investment cost of the project is 100,000,000. In the IRR calculation, this value should be negative because it represents an outgoing cash flow.
The annual profit of the project is positive numbers. In the IRR calculation, these values should be positive as they represent incoming cash flows.
The IRR formulas for each year are as follows:
- Year 1: =IRR(G5:G6) = -73%
- Year 2: =IRR(G5:G7) = -32%
- Year 3: =IRR(G5:G8) = -8%
- Year 4: =IRR(G5:G9) = 6%
- Year 5: =IRR(G5:G10) = 15%
Therefore, after 4 years, the project becomes feasible when the IRR is positive.
How to Use the IRR Function to Calculate Internal Rate of Return in Excel
That’s it! This article has guided you on how to use the IRR function to calculate the internal rate of return in Excel. Good luck with your calculations!
