How to Figure Out IRR in Excel
IRR (Internal Rate of Return) is a term used in capital planning to describe the total rate of return a project would provide based on its anticipated stream of cash flows.
In this lesson, I’ll explain how to compute IRR in Excel, how it differs from the more widely used NPV metric, and many situations in which you may utilize the built-in IRR formulae in Excel.
This instruction explains: 1. Explanation of Internal Rate of Return (IRR) 2. Excel's IRR Function - Syntax 3. IRR Calculation for Variable Cash Flows 4. When Does the Investment Have a Positive IRR? 5. How to Compare Multiple Projects Using the IRR Function 6. IRR Calculation for Uneven Cashflows 7. Which IRR or NPV Is Better?
1. Explanation of Internal Rate of Return (IRR)
IRR is a discount rate that’s used to calculate an investment’s return based on recurring income.
It is possible to determine if a project (or investment) will be lucrative for a firm using the IRR, which is represented as a percentage.
I’ll use a straightforward example to describe IRR.
Let’s say you want to invest $50,000 in a business that will produce $10,000 annually for the next 10 years. You may use this information to determine the project’s IRR or the rate of return on your $50,000 investment.
The IRR in the aforementioned illustration is 15%. (we will see how to calculate that later in the tutorial). This indicates that it is the same as investing your money for 10 years at a 15% rate of return.
You may use the IRR number to guide your decisions once you get it. As a result, you ought to put your money into any other project where the IRR exceeds 15%.
Alternatively, if you intend to borrow money or raise money to purchase this project for $50,000 using financing, make sure that the cost of the capital is less than 15%.
2. Excel’s IRR Function – Syntax
The IRR function in Excel enables you to determine the internal rate of return. The arguments for this function are as follows:
=IRR(values, [guess])
- Values – A collection of numbers in an array of cells for which you wish to get the internal rate of return.
guess – a close guess at a number - Guess – An estimate of the IRR result that you make; it’s optional and ranges from 0.1 to 10% by default. This is used when there is a chance of receiving more than one result; in such case, the function returns the result that is closest to the value of the guessing parameter.
The following conditions must be met to use the function:
- The IRR function will only take into account numbers inside the chosen cell range. Any text strings or logical values in the array or reference parameter would be disregarded.
- The values parameter’s sums must be represented as Numbers.
- If one is given, the “guess” argument must be a percentage expressed as a decimal.
- The format of a cell that displays the function result must be a Percentage.
- The sums come up regularly (months, quarters, years)
- Other amounts should be Positive cash flows, signifying recurring revenues, but one amount must be a Negative cash flow (representing the original investment).
- Because the function bases its calculation of the outcome on the sequence of the amounts, all of the amounts must be in chronological order.
Use Excel's XIRR function, which also lets you enter the dates for each cash flow, to compute the IRR value when the cash flow occurs at various time intervals. Later in the course, this is illustrated with an example.
Let’s look at a few examples to better understand how to utilize the IRR function in Excel.
3. IRR Calculation for Variable Cash Flows
Consider the following dataset, which includes a $30,000 initial investment and fluctuating cash flow/income over six years.
The following formula may be used to get the IRR for this set of data:
=IRR(D2:D8)
The function’s output, the IRR of the cash flow after six years, is 8.22%.
Note: The 'guess' argument in the formula should be filled in if the function produces a #NUM! error. This occurs when the formula considers a range of values to be plausible and demands the estimated value to deliver the IRR that is closest to the guess we supplied. You won't typically need to utilize this, though.
4. When Does the Investment Have a Positive IRR?
Additionally, you can determine the internal rate of return (IRR) for each period in a cash flow and determine precisely when an investment starts to show a profit.
Let’s say we have the dataset below, where column C is a list of all the cashflows.
Finding the year that this investment’s IRR goes positive is the goal (indicating when the project breaks even and becomes profitable).
To achieve this, we shall determine the IRR for each year rather than the IRR for the whole project.
To accomplish this, enter the formula shown below in cell D3 and copy it to each of the other cells in the column.
=IRR($C$2:C3)
As you can see, the IRR is -80% after the first year (values D2:D3), -52% after the second year (D2:D4), etc.
This overview demonstrates that after five years, the investment of $30,000 with the specified cash flow has a positive IRR.
When you have to choose between two projects with similar IRRs, This can be helpful. It would be more profitable to select a project where the IRR turns positive more quickly because There is less chance of not making back your initial investment.
Keep in mind that the range reference in the formula above is mixed, meaning that the second reference (C3) is not locked but the first reference ($C$2) is locked by placing dollar signs before the row number and the column letter.
This ensures that the formula always takes into account the full column up to the row where it is applied when copied down.
5. How to Compare Multiple Projects Using the IRR Function
Excel’s IRR function can be used to compare the investments and returns of various projects and determine which one is the most profitable.
Consider the following dataset, which includes three projects with an initial investment (which is displayed in negative because it is an outflow) and a sequence of positive cash flows.
We must compute the IRR for each project using the following straightforward formula to determine which project is the best and has the greatest IRR:
=IRR(C2:C8)
The IRR for Project 1 will be determined by the formula above. You can determine the IRR for the other two projects in a similar manner.
You may observe that:
- IRR for Project 1 is 5.60%.
- IRR for Project 2 is 1.75 percent.
- IRR for Project 3 is 14.71%.
If the cost of capital is assumed to be 4.50%, investment 2 is unacceptable because it would result in a loss; in contrast, investment 3 is the most lucrative since it has the highest internal rate of return.
Therefore, if you must choose only one project to invest in, choose Project 3. If you have the option to invest in more than one project, choose Projects 1 and 3.
If you’re wondering what the cost of capital is, it’s the sum of money you must pay to obtain capital. Your cost of capital, for instance, would be 4.5% if you borrowed $100,000 at a 4.5% annual interest rate. Similar to this, your cost of capital would be 5% if you issued preferred shares with a 5% return guarantee in exchange for 100,000. A corporation often raises capital from a variety of sources in real-world circumstances, and its cost of capital is a weighted average of all these sources.
6. IRR Calculation for Uneven Cashflows
The requirement that the cashflows be periodic with the same gap between them is one of the restrictions placed on the Excel IRR function.
However, there may be situations in real life when your efforts only seldom succeed.
Here is a dataset where the cashflows occur at erratic intervals as an illustration (see dates in column A).
The regular IRR function cannot be used in this example, but there is another function that can—the XIRR function.
The XIRR function accepts both the cashflows and the dates, allowing it to take into account irregular cashflows and calculate the appropriate IRR.
The following formula may be used to get the IRR in this example:
=XIRR(B2:B8,A2:A8)
The cashflows are supplied as the first argument in the formula above, and the dates are specified as the second argument. If this formula gives a #NUM! error, you should enter an approximation of the expected IRR as the third input. Don't worry, it only has to be a rough estimate of the IRR you believe it would provide; it doesn't need to be accurate or even very near. This produces the result and improves the iteration of the formula.
7. Which IRR or NPV Is Better?
Both NPV and IRR are utilized for analyzing projects, however, NPV is the more trustworthy approach.
The Net Present Value (NPV) approach allows you to examine all potential future cash flows and determine their net present values.
The project is profitable if this value turns out to be higher than your initial outlay; otherwise, it is not.
The IRR calculation for a project, on the other hand, reveals the rate of return for all future cash flow, allowing you to obtain a sum equal to the present outflow. For instance, if you are investing $100K in a project today with an IRR of 10%, you will receive $100K if you discount all of the project’s future cash flows at a 10% discount rate.
Both approaches are used when evaluating projects, but the NPV approach is more dependable. When comparing a project’s NPV and IRR, it’s possible to get results that are at odds with one another.
In this situation, it is recommended to follow the advice provided by the NPV technique.
IRR approach generally has certain shortcomings, making the NPV method more trustworthy:
- Higher approach makes the supposition that all projected cash flows will be reinvested at the same rate of return (i.e., the IRR of the project). This is usually an incorrect assumption because the majority of the cash flows would be invested in other projects, many of which could have a different internal rate of return (IR), or in securities like bonds, which would typically have a much lower rate of return.
- There would be multiple IRRs for the project if there were multiple outflows and inflows. Again, this makes comparison very challenging.
Despite its drawbacks, IRR is a useful tool for project evaluation and can be combined with the NPV method to help you decide which project (or projects) to pursue.
I demonstrated how to utilize Excel’s IRR function for you in this lesson. I also went over how to use the XIRR function to figure out the IRR if your cash flows are erratic.
