When investing, your capital is at risk and you may get back less than invested. Past performance doesn’t guarantee future results.
Internal Rate of Return (IRR): Definition, Formula, and Calculation
Internal Rate of Return (IRR) is used to estimate the return on an investment. It’s a standardized metric that can be applied to a variety of asset classes to gauge profitability.
FORMULA
0 = ∑(t=0 to n) Ct / (1 + IRR)^t
Where:
• 0: The Net Present Value (NPV) is zero when calculating the IRR. This is the target of the calculation.
• Σ (sum): The summation symbol indicates that we are summing cash flows over multiple periods.
• n: The final period in the series of cash flows
• Cₜ: Cash flow at time 𝑡. This is the amount of money either received or paid out at each time period 𝑡.
• IRR: The Internal Rate of Return, which is the discount rate that makes the NPV equal to zero.
• (1 + IRR)⁽ᵗ⁾: This is the discount factor that adjusts the cash flow at time 𝑡 to its present value. The 𝑡 exponent reflects the period in which the cash flow occurs.
Where:
• 0: The Net Present Value (NPV) is zero when calculating the IRR. This is the target of the calculation.
• Σ (sum): The summation symbol indicates that we are summing cash flows over multiple periods.
• n: The final period in the series of cash flows
• Cₜ: Cash flow at time 𝑡. This is the amount of money either received or paid out at each time period 𝑡.
• IRR: The Internal Rate of Return, which is the discount rate that makes the NPV equal to zero.
• (1 + IRR)⁽ᵗ⁾: This is the discount factor that adjusts the cash flow at time 𝑡 to its present value. The 𝑡 exponent reflects the period in which the cash flow occurs.
Big ideas
- Internal Rate of Return (IRR) is related to the concepts of the Time Value of Money (TVM), as well as the Net Present Value (NPV) and Return on Investment (ROI).
- IRR can be difficult to calculate and it has limitations. The formula excludes factors such as risk-free return, capital costs, and inflation. Thus, returns are gauged solely on internal factors.
- It’s important to understand the factors that result in an IRR for a given company or investment. Projects with an identical IRR can have entirely different core drivers to the IRR percentage.
What is Internal Rate of Return (IRR)?
DEFINITION
IRR, or Internal Rate of Return, is the discount rate that makes the Net Present Value (NPV) of all cash flows from any given project or investment equal to zero.
It is the percentage return expected over a given time period, typically a year. As a general rule of thumb, the higher the IRR, the more profits might be generated.
The IRR has a similar purpose to the ROI (Return on Investment), i.e. both are measures of profitability.
It’s the same formula for calculating the Net Present Value (NPV) for an investment, except the NPV must be equal to zero. The NPV is closely tied to the TVM; a simple concept that a pound received today is worth more than a pound in the future, due to its earning potential in the interim period.
IRR is often used for companies but can also be used by individual investors. For instance, you can apply it to insurance policies. A policy with a higher IRR but an identical premium is preferable to the lower IRR policy.
The IRR has a similar purpose to the ROI (Return on Investment), i.e. both are measures of profitability.
It’s the same formula for calculating the Net Present Value (NPV) for an investment, except the NPV must be equal to zero. The NPV is closely tied to the TVM; a simple concept that a pound received today is worth more than a pound in the future, due to its earning potential in the interim period.
IRR is often used for companies but can also be used by individual investors. For instance, you can apply it to insurance policies. A policy with a higher IRR but an identical premium is preferable to the lower IRR policy.
What is IRR useful for?
Internal Rate of Return is useful for evaluating the profitability of investments and comparing different projects. It is particularly helpful in capital budgeting decisions, where firms need to assess the viability of potential projects.
By calculating the IRR, investors can gauge whether a project meets or exceeds their Required Rate of Return. IRR is also beneficial for comparing investments of different sizes and durations, as it provides a standardised measure of profitability.
Furthermore, IRR can be used in scenarios where quick and straightforward decisions are needed, as it summarises the potential return of an investment in a single percentage.
By calculating the IRR, investors can gauge whether a project meets or exceeds their Required Rate of Return. IRR is also beneficial for comparing investments of different sizes and durations, as it provides a standardised measure of profitability.
Furthermore, IRR can be used in scenarios where quick and straightforward decisions are needed, as it summarises the potential return of an investment in a single percentage.
Importance of IRR in investment analysis
IRR plays an important role in investment analysis. It provides a useful investment appraisal across asset classes to give an understanding of which has the most potential.
All else being equal, an IRR of 30% is better than an IRR of 20%, and the higher figure provides a better earning opportunity.
Private equity firms and oil/gas companies use it quite frequently as a very quick benchmark for assessing project viability. In fact, IRR is often referred to as the single most important benchmark for private equity funds.
While IRR is a valuable metric for comparing the profitability of investments, its application is most relevant for projects with predictable and periodic cash flows. For investments involving irregular cash flows or where timing is uncertain, IRR may not provide an accurate reflection of the potential return. In these cases, metrics like NPV or the Modified Internal Rate of Return (MIRR) can offer better insights into the investment's performance.
But it does come with drawbacks, some of which we’ll discuss below.
All else being equal, an IRR of 30% is better than an IRR of 20%, and the higher figure provides a better earning opportunity.
Private equity firms and oil/gas companies use it quite frequently as a very quick benchmark for assessing project viability. In fact, IRR is often referred to as the single most important benchmark for private equity funds.
While IRR is a valuable metric for comparing the profitability of investments, its application is most relevant for projects with predictable and periodic cash flows. For investments involving irregular cash flows or where timing is uncertain, IRR may not provide an accurate reflection of the potential return. In these cases, metrics like NPV or the Modified Internal Rate of Return (MIRR) can offer better insights into the investment's performance.
But it does come with drawbacks, some of which we’ll discuss below.
Overview of IRR calculation methods
There are several methods to calculate IRR, with the most common being the trial-and-error method and the use of financial calculators or software. The trial-and-error method involves guessing the IRR and then adjusting the rate until the NPV equals zero.
This manual method can be time-consuming - financial calculators and spreadsheet software like Excel use built-in functions to quickly and accurately compute the IRR. We will be explaining below how to use Excel and Google Sheets for IRR calculation.
This manual method can be time-consuming - financial calculators and spreadsheet software like Excel use built-in functions to quickly and accurately compute the IRR. We will be explaining below how to use Excel and Google Sheets for IRR calculation.
Data requirements for IRR calculation
To calculate Internal Rate of Return, the data you need is a series of cash flows to and from the investment. This includes the initial outlay, which is the amount invested at the start, and the subsequent cash inflows and outflows over the investment period.
Each cash flow must be recorded at its specific time period. Consistency in time intervals is crucial, whether they are yearly, quarterly, or monthly. Accurate data on all cash transactions is essential for precise calculation.
The timing and magnitude of each cash flow directly impact the IRR result. Without complete and accurate data, the IRR calculation can be misleading.
Each cash flow must be recorded at its specific time period. Consistency in time intervals is crucial, whether they are yearly, quarterly, or monthly. Accurate data on all cash transactions is essential for precise calculation.
The timing and magnitude of each cash flow directly impact the IRR result. Without complete and accurate data, the IRR calculation can be misleading.
Common mistakes in data entry for IRR calculation
Errors in data entry can lead to incorrect IRR calculations. One common mistake is an inaccurate recording of cash flows. Missing out on any cash flow or entering incorrect amounts can significantly skew results.
Another mistake is inconsistent time intervals between cash flows. If the cash flows are not recorded at regular intervals, the calculated IRR may not be reliable.
Additionally, not accounting for all investment costs, such as maintenance or additional capital outlays, can lead to an overstated IRR. Using incorrect signs for cash flows, where outflows should be negative and inflows positive, is another frequent error.
Another mistake is inconsistent time intervals between cash flows. If the cash flows are not recorded at regular intervals, the calculated IRR may not be reliable.
Additionally, not accounting for all investment costs, such as maintenance or additional capital outlays, can lead to an overstated IRR. Using incorrect signs for cash flows, where outflows should be negative and inflows positive, is another frequent error.
Calculating IRR in Excel
Excel has built-in functions that make it easy to find the IRR for a series of cash flows. This function helps investors analyse the profitability of investments. It eliminates the need for manual calculations and provides quick results. Using Excel for IRR calculation can save time and reduce errors compared to manual methods.
Example of IRR calculation in Excel
Understanding the IRR formula in Excel
The IRR function in Excel uses an iterative process to find the rate that makes the NPV of cash flows equal to zero. It starts with a guess and iteratively tries different rates until it finds the correct one. The formula considers the timing and amount of each cash flow, making it a reliable tool for evaluating investments.
Although you don't need to know the exact mathematical formula to use the IRR function in Excel, understanding the concept can be helpful.
Although you don't need to know the exact mathematical formula to use the IRR function in Excel, understanding the concept can be helpful.
Step 1: Enter cash flows for both Investments
First, list your cash flows in a column. Start with the initial investment amount, which should be a negative number, representing the outflow of money. Then, list the subsequent cash inflows and outflows for each period. Ensure that the cash flows are in chronological order. For example:
Years | Cash Flow |
Year 0 | -100,000 |
Year 1 | 20,000 |
Year 2 | 30,000 |
Year 3 | 40,000 |
Year 4 | 50,000 |
Repeat this for the second investment in another column if you are comparing two investments. This setup is essential for accurate IRR calculation.
Step 2: Use the IRR function in Excel
Once your cash flows are in place, you can use the IRR function in Excel.
- Click on the cell where you want the IRR result to appear.
- Type =IRR and then select the range of cells that contains the cash flows. For instance, if your cash flows are in cells B2 to B6, you would type =IRR(B2:B6).
- Press Enter, and Excel will compute the IRR for the series of cash flows.
If you are comparing two investments, use the IRR function for both sets of cash flows. This allows you to see which investment has a higher rate of return. In the example table above, the IRR is 12.83%.
Step 3: Use the IRR function in Excel
After pressing Enter, Excel will display the IRR as a percentage. This percentage represents the annualised rate of return for the investment. A higher IRR indicates a potentially more profitable investment.
Compare the IRR results of different investments to determine which one offers better returns. For example, if the IRR for the first investment is 12% and the second investment is 15%, the second investment is (generally) more attractive.
Compare the IRR results of different investments to determine which one offers better returns. For example, if the IRR for the first investment is 12% and the second investment is 15%, the second investment is (generally) more attractive.
Excel Calculation with XIRR Function
When dealing with cash flows that occur at irregular intervals, the standard IRR function may not provide an accurate result. This is where the XIRR function comes into play. XIRR allows you to calculate the IRR for a series of cash flows that are not evenly spaced in time. Unlike IRR, which assumes that all cash flows happen at regular intervals (such as annually), XIRR requires you to input the specific dates for each cash flow. This provides a more precise calculation by accounting for the exact timing of each cash transaction.
For example, in Excel, you would use the formula =XIRR(values, dates), where values is the range of cash flows and dates is the range of corresponding dates. This method is particularly useful for investments or projects where cash inflows and outflows do not occur on a regular schedule, providing a more realistic assessment of the investment’s return.
For example, in Excel, you would use the formula =XIRR(values, dates), where values is the range of cash flows and dates is the range of corresponding dates. This method is particularly useful for investments or projects where cash inflows and outflows do not occur on a regular schedule, providing a more realistic assessment of the investment’s return.
Example of XIRR calculation in Excel
Calculating IRR in Google Sheets
There are no material differences in the calculation of IRR between Excel and Google Sheets. You will still be applying the IRR function to a list of cells, using a similar formula such as the previous: =IRR(B2:B6).
The differences lie only at a higher level. Excel might have more advanced financial functions and analysis tools, which can be beneficial for complex financial modelling. On the other hand, Google Sheets provides the advantage of easy sharing and real-time collaboration, which can be useful for team projects.
The differences lie only at a higher level. Excel might have more advanced financial functions and analysis tools, which can be beneficial for complex financial modelling. On the other hand, Google Sheets provides the advantage of easy sharing and real-time collaboration, which can be useful for team projects.
Example of IRR calculation in Google Sheets
Interpreting IRR results
Interpreting the IRR results is crucial for making informed investment decisions. A higher IRR indicates a more profitable investment. When comparing multiple investments, the one with the highest IRR is typically considered the best.
But IRR alone should not be the sole determinant in investment decisions. Consider the IRR in the context of the investment’s risk. A high IRR might be attractive, but it is also essential to assess the risk associated with achieving those returns.
For example, when comparing mutually exclusive projects, the one with the higher IRR may not always be the best choice if the Net Present Value (NPV) differs significantly. While IRR provides insight into the rate of return, NPV indicates the actual value added by the investment. Combining IRR with metrics like NPV and the Payback Period ensures a more comprehensive evaluation and better decision-making.
But IRR alone should not be the sole determinant in investment decisions. Consider the IRR in the context of the investment’s risk. A high IRR might be attractive, but it is also essential to assess the risk associated with achieving those returns.
For example, when comparing mutually exclusive projects, the one with the higher IRR may not always be the best choice if the Net Present Value (NPV) differs significantly. While IRR provides insight into the rate of return, NPV indicates the actual value added by the investment. Combining IRR with metrics like NPV and the Payback Period ensures a more comprehensive evaluation and better decision-making.
Advantages and disadvantages of IRR
While IRR is a useful metric, it has limitations. One major limitation is that it assumes reinvestment of interim cash flows at the same rate as the IRR, which may not be realistic. This can lead to overestimating the profitability of an investment. IRR also may not be reliable for comparing projects with different durations or cash flow patterns.
IRR Advantages | IRR Disadvantages |
Time Value of Money (TVM) is considered while calculating IRR. | Economies of Scale are ignored in the IRR calculation, which can impact larger projects. |
Simple to interpret and understand once calculated, making it easy for decision-making. | Assumes unrealistic reinvestment rate, as it implies reinvestment at the calculated IRR for the remaining duration of the project. |
No requirement of a specific Hurdle Rate or Required Rate of Return for the calculation. | Overlooks dependent or contingent projects, leading to potentially inaccurate assessments. |
Managers can make a rough estimate of the Required Rate of Return without complex analysis. | Does not account for mutually exclusive projects, making comparisons between such projects difficult. |
Accounts for all cash flows during the entire life of the project, providing a complete picture. | Fails to consider different project durations or terms when comparing investment opportunities. |
IRR reflects profitability, which is useful for comparing different projects with similar risk profiles. | Does not handle mixed cash flows well, especially when positive and negative cash flows are involved. |
Widely used and accepted for project evaluation in capital budgeting. | Cannot be calculated if later cash inflows are insufficient to cover the initial investment. |
Helpful for decision-making in resource allocation when comparing projects with positive cash flows. | Does not measure wealth increase, making it difficult to determine the actual value added to the business. |
Additionally, multiple IRRs can occur if the project has alternating positive and negative cash flows, leading to confusion. In such cases, using other metrics like the Modified Internal Rate of Return (MIRR) or NPV might provide better insights.
While IRR assumes that all interim cash flows are reinvested at the IRR itself, this may not always be realistic. A more flexible approach is to use the Modified Internal Rate of Return (MIRR). MIRR assumes that interim cash flows are reinvested at the project's cost of capital, providing a more accurate picture of an investment's profitability. This method addresses one of the key limitations of IRR, offering investors an alternative metric for evaluation.
Projects that involve alternating positive and negative cash flows can lead to multiple IRRs, known as the multiple IRR problem. In such cases, IRR can become confusing, and interpreting the results becomes challenging. To address this, investors often rely on other metrics like the Net Present Value (NPV) or the Modified Internal Rate of Return (MIRR) for a clearer assessment of the project's viability. This problem highlights the importance of using IRR in conjunction with other financial metrics for accurate decision-making.
While IRR assumes that all interim cash flows are reinvested at the IRR itself, this may not always be realistic. A more flexible approach is to use the Modified Internal Rate of Return (MIRR). MIRR assumes that interim cash flows are reinvested at the project's cost of capital, providing a more accurate picture of an investment's profitability. This method addresses one of the key limitations of IRR, offering investors an alternative metric for evaluation.
Projects that involve alternating positive and negative cash flows can lead to multiple IRRs, known as the multiple IRR problem. In such cases, IRR can become confusing, and interpreting the results becomes challenging. To address this, investors often rely on other metrics like the Net Present Value (NPV) or the Modified Internal Rate of Return (MIRR) for a clearer assessment of the project's viability. This problem highlights the importance of using IRR in conjunction with other financial metrics for accurate decision-making.
Comparing IRR with other financial metrics
When evaluating investments, it is important to consider other financial metrics alongside IRR. The main ones include:
- Net Present Value (NPV) - a metric that complements IRR. While IRR provides the rate of return, NPV gives the absolute value of an investment’s profitability in today’s dollars. A positive NPV indicates that the investment adds value, while a negative NPV suggests a loss.
- Payback Period - measures the time required to recover the initial investment. While not as detailed as IRR or NPV, it provides a quick assessment of an investment’s liquidity risk. A shorter payback period is often seen as indicating a lower investment risk.
- Modified Internal Rate of Return - addresses some of IRR’s limitations by assuming reinvestment at the project’s cost of capital rather than the IRR. In this way, the profitability of an investment can be measured more realistically.
- Return on Investment (ROI) - measures the efficiency of an investment by comparing the net profit to the initial cost. It is calculated by dividing the net profit by the initial investment cost and multiplying by 100 to get a percentage.
- Profitability Index (PI) - evaluates the value created per unit of investment. In order to calculate it, the present value of future cash flows has to be divided by the initial investment cost. The PI of an investment should be greater than 1.
While IRR is a valuable tool for assessing investment profitability, it is essential to use it alongside other financial metrics. This approach provides a more balanced and comprehensive analysis.
Recap of Internal Rate of Return (IRR)
The Internal Rate of Return (IRR) is one of the most important tools for assessing a fund or company’s estimated performance. It provides a quick and neat comparison of one investment versus the other.
Once you have done this rudimentary calculation, you can then proceed to look more deeply into the asset's performance and use the IRR in tandem with other key financial metrics for a more precise and detailed overview.
Once you have done this rudimentary calculation, you can then proceed to look more deeply into the asset's performance and use the IRR in tandem with other key financial metrics for a more precise and detailed overview.
FAQ on Internal Rate of Return (IRR)
Q: Why isn't my IRR calculating in Excel or Google Sheets?
Your IRR might not be calculating because of incorrect cash flow entries. Ensure the initial investment is negative and the subsequent cash flows are entered correctly. If cash flows are not consistent or the IRR formula range is incorrect, errors can occur. Another issue could be multiple IRRs resulting from alternating positive and negative cash flows.
Q: What’s a good IRR rate per industry?
Good IRR rates vary by industry. For example, venture capital typically expects IRRs of 20-30% due to high risk. Real estate investments often aim for IRRs around 10-15%. Established sectors like utilities might have lower IRRs around 8-10%. Always compare the IRR against industry benchmarks to determine if it’s favourable.
Q: What's the difference between ROI and IRR?
ROI measures the efficiency of an investment by comparing the total profit to the initial cost, expressed as a percentage. It provides a snapshot of profitability. IRR, on the other hand, is the annualised rate of return that makes the NPV of cash flows zero. It considers the TVM, making it more complex and informative over multiple periods.
Q: Why is IRR often used with Weighted Average Cost of Capital (WACC) and NPV?
IRR is used alongside WACC and NPV to evaluate investment viability. Comparing IRR with WACC helps determine whether an investment's return exceeds the cost of capital. NPV, calculated using WACC, indicates the absolute value added by the investment. Together, IRR, WACC, and NPV provide a comprehensive analysis of an investment's profitability and risk.
Q: What is the difference between the IRR and XIRR functions in Excel?
IRR assumes equal time intervals between cash flows, which is ideal for regular annual or monthly periods. Extended Internal Rate of Return (XIRR) allows for irregular intervals, making it more flexible. XIRR requires specific dates for each cash flow, providing a more accurate IRR calculation for investments with uneven cash flow timings.
- Time Value of Money (TVM): The principle that a sum of money today is more valuable than the same sum in the future due to its potential to earn returns.
- Private equity funds: Investment vehicles that pool capital from investors to acquire and improve companies, often taking them private before aiming for a profitable exit.
- Cash flow: The net amount of money moving into and out of a business over a specific period.
