What is an Internal Rate of Return?
An internal rate of return (IRR) is a metric that measures the profitability of an investment project. It is the annualized rate of return that makes the net present value (NPV) of all cash flows from the project equal to zero. In other words, it is the discount rate that makes the initial investment equal to the sum of the discounted future cash flows.
The IRR is useful for comparing different investment projects and choosing the ones that have the highest returns. However, it also has some limitations and assumptions, such as:
- It assumes that all cash flows are reinvested at the same rate as the IRR, which may not be realistic in some cases.
- It may not exist or be unique for some projects, especially those with non-conventional cash flows (such as negative cash flows followed by positive cash flows).
- It may not reflect the true cost of capital or the risk of the project, as it does not account for factors such as inflation, taxes, or opportunity costs.
How to Calculate an IRR in Excel
There are two main ways to calculate an IRR in Excel: using the IRR function or using the goal seek tool. Both methods require a series of cash flows and an initial guess for the IRR.
Using the IRR Function
The IRR function is a built-in function in Excel that returns the IRR for a series of cash flows. The syntax of the function is:
=IRR(values, [guess])
where:
- values is a range of cells that contain the cash flows of the project, starting with the initial investment (which should be negative) and ending with the final cash flow (which can be positive or negative).
- guess is an optional argument that specifies an initial estimate for the IRR. If omitted, Excel uses 0.1 (10%) as the default guess.
The IRR function uses an iterative process to find the IRR that satisfies the NPV equation. It starts with the guess and then adjusts it until the NPV is close to zero. The function returns an error if it cannot find a valid IRR within 20 iterations or if the cash flows do not converge.
Using the Goal Seek Tool
The goal seek tool is a what-if analysis tool in Excel that allows you to find the value of an input cell that makes a formula cell equal to a desired value. You can use it to find the IRR by setting the NPV formula as the formula cell and the IRR as the input cell. The steps are:
- Enter the cash flows of the project in a range of cells, starting with the initial investment (which should be negative) and ending with the final cash flow (which can be positive or negative).
- Enter a formula that calculates the NPV of the cash flows using the IRR as the discount rate. For example, if the cash flows are in cells A1:A5 and the IRR is in cell B1, the formula can be:
=NPV(B1,A2:A5)+A1
- Select the cell that contains the NPV formula and go to the Data tab, then click on What-If Analysis and choose Goal Seek.
- In the Goal Seek dialog box, enter the following settings:
- Set cell: the cell that contains the NPV formula (e.g. C1)
- To value: 0 (the desired value for the NPV)
- By changing cell: the cell that contains the IRR (e.g. B1)
- Click on OK and Excel will find the IRR that makes the NPV equal to zero.
An Example of Calculating an IRR in Excel
To illustrate how to calculate an IRR in Excel, let us consider the following example:
Suppose you are considering investing in a project that requires an initial outlay of $10,000 and generates the following cash flows over the next five years:
| Year | Cash Flow |
|---|---|
| 0 | -$10,000 |
| 1 | $2,000 |
| 2 | $3,000 |
| 3 | $4,000 |
| 4 | $5,000 |
| 5 | $6,000 |
What is the IRR of this project?
To answer this question, we can use either the IRR function or the goal seek tool in Excel. Here is how the spreadsheet would look like using both methods:
| A | B | C |
|---|---|---|
| Year | Cash Flow | NPV |
| 0 | -$10,000 | =NPV(B1,A2:A6)+A1 |
| 1 | $2,000 | |
| 2 | $3,000 | |
| 3 | $4,000 | |
| 4 | $5,000 | |
| 5 | $6,000 | |
| IRR | =IRR(A1:A6) |
Using the IRR function, we enter the formula =IRR(A1:A6) in cell B7 and get the result of 0.2548 or 25.48%.
Using the goal seek tool, we select cell C1 and go to the Data tab, then click on What-If Analysis and choose Goal Seek. We enter the following settings:
- Set cell: C1
- To value: 0
- By changing cell: B1
We click on OK and Excel finds the value of 0.2548 or 25.48% for cell B1 that makes the NPV equal to zero.
Both methods give the same result, which means that the IRR of this project is 25.48%. This means that the project has a high profitability and can generate an annualized return of 25.48% on the initial investment.
Other Approaches to Calculate an IRR
Besides using Excel, there are other approaches to calculate an IRR, such as:
- Using a financial calculator, such as the TI-83 or the HP-12C, that has an IRR function or a solver feature.
- Using an online IRR calculator, such as this one, that allows you to enter the cash flows and get the IRR instantly.
- Using a trial and error method, where you manually plug in different values for the IRR in the NPV formula until you find the one that makes the NPV close to zero. This method is tedious and time-consuming, but it can help you understand the concept of IRR better.
