How to compute the IRR in Excel
Jeremy PAULEN
In this article, Jérémy PAULEN (ESSEC Business School, Global Bachelor of Business Administration, 2019-2023) explains everything about the IRR function in Excel, which is used to compute the internal rate of return of a series of cash flow to evaluate the financial performance of an investment in relative terms.
What is the IRR?
The IRR represents the internal rate of return of an investment. It is closely related to the net present value (NPV) of the investment as the IRR is the discount rate that makes the NPV equal to zero.
Consider an investment represented by a series of cash flows CF0, CF1, CF2, …, CFT, which take into account the revenues and expenses of the project computed or forecasted at time 0 leading to capital inflows and outflows for the firm. The NPV of this investment is given by:
where r is the discount rate that takes into account the risk of the project.
The IRR corresponds to the value of the discount rate for which the NPV is equal to 0:
The IRR is the solution of a non-linear equation:
Note that this equation may have one solution, several solutions or no solution according to the sequence of cash flows.
The internal rate of return (IRR) is an important indicator in the decision-making process as it measures the financial performance of a project. The IRR is a relative measure as its unit is a percentage. The NPV is an absolute measure as its unit is the euro, the dollar, etc.
It makes it possible to measure the future financial performance of a project or a company. The higher the IRR is, the more interesting it is to launch the project.
The IRR can therefore be used in the case of a choice to be made between different investment perspectives, but also to evaluate the company’s share buyback programs.
A limit of using the IRR method is that it does not consider the size of a project. Cash flows are simply compared to the amount of capital outlay generating those cash flows. In other words, considering two projects A and B, the IRR of A may be lower than the IRR of B, while the NPV of A may be higher than the NPV of B.
The IRR function in Excel
How to use the IRR function in Excel?
In Excel, you can get the IRR function in the “Formulas” tab.
You can also type “= IRR (value, [guess])” in the cell where you want to compute the IRR.
The IRR function uses the following arguments:
- Values: The cash flow series. Cash flows include investment values and net income.
- Guess: a number guessed by the user that is close to the expected internal rate of return
Example
Example: consider a new factory modeled by the following series of cash flows:
- CF0 = -$50,000 (initial cost)
- CF1 = +$5,000 (net cash flow in year 1)
- CF2 = +$8,000 (net cash flow in year 2)
- CF3 = +$13,500 (net cash flow in year 3)
- CF4 = +$18,800 (net cash flow in year 4)
- CF5 = +$20,500 (net cash flow in year 5)
Excel file to compute the IRR of a series of cash flows
You can download below a short video which illustrates how to compute the IRR of a series of cash flows with Excel.
Related posts on the SimTrade blog
▶ Raphaël ROERO DE CORTANZE The Internal Rate of Return
▶ William LONGIN How to compute the present value of an asset?
▶ Rodolphe CHOLLAT-NAMY Bond valuation
▶ Léopoldine FOUQUES The IRR, XIRR and MIRR functions in Excel
▶ Sébastien PIAT Simple interest rate and compound interest rate
Useful resources
Microsoft IRR function
About the author
The article was written in November 2021 by Jérémy PAULEN (ESSEC Business School, Global Bachelor of Business Administration, 2019-2023)