If you are working on a cost-benefit analysis, you will almost inevitably come across ROI and IRR. Return on Investment (ROI) and Internal Rate of Return (IRR) are among the most popular success measures for projects and investments. They are also mentioned in the Project Management Institute’s Body of Knowledge (source: PMBOK®, 6th ed., part 1, ch. 126.96.36.199, p. 34) In this article, we will introduce both methods, incl. their calculation, and compare their pros and cons.
- What Is Return on Investment (ROI)?
- What Is the Internal Rate of Return (IRR)?
- How to Calculate the IRR
- How Is the IRR Calculated (Using Excel)?
- What Are the Advantages and Disadvantages of the Internal Rate of Return Method?
- What Is the Difference between IRR, Single- and Multi-Year ROI?
- Example of the Use of ROI and IRR
What Is Return on Investment (ROI)?
ROI is a ratio (or a rate, if annualized) that sets the net returns generated by an investment or project in relation to the investment. Thus, ROI makes options and alternatives comparable even if they have different investment amounts.
Besides the simple ratio, there is a modified return on investment indicator for multiple year investments. This method involves annualizing the total returns and calculating an average return rate per year.
Learn more about the different ROI methods and their calculation in our dedicated article:
What Is the Internal Rate of Return (IRR)?
The IRR is technically determined in an iterative calculation, i.e. applying different interest rates to a series of cash flows multiple times until it is solved for an IRR with a net present value (NPV) equal to 0.
A similar yet for the user less visible approach is also taken by calculators and calculation applications such as Excel.
How to Calculate the IRR
The IRR is the discount rate that leads to an NPV of 0. The IRR is therefore calculated as a compounded discount rate for cash flows. It is usually determined in an iterative computation in which the correct IRR is approximated by re-performing the NPV calculation over and over again until the NPV is 0.
You can read on to learn how to perform this calculation in Excel or visit our IRR Calculator:
How Is the IRR Calculated (Using Excel)?
The IRR is typically calculated using a calculation program with a respective function. Excel and Google Sheets are typical examples of spreadsheet applications that are able to determine the IRR.
The calculation of the Internal Rate of Return in Excel is comparatively straight forward:
1) Create and Format the Cash Flow Projection Spreadsheet
Create a spreadsheet in Excel and copy the cash flow forecast into the sheet. Columns typically represent the periods or years while rows contain the cash flows.
The IRR function in Excel requires net cash flows for every period to calculate the internal rate of return, hence this is the minimum granularity of the estimates needed.
2) Calculate the Net Cash Flows for Each Period
In practice, cash flow projections often come in various levels of granularity. To make sure that Excel is able to calculate the IRR, you need to determine the net cash flows.
If the raw data does not yet contain the net cash flows, add a new row and subtract the projected outflows (or costs) from the projected inflows (or benefits and earnings) for each period.
3) Calculate the IRR Using the Built-In Excel Function
Select an empty cell and write “=IRR(”. Excel will automatically provide advice on the parameters needed to perform this function.
For the internal rate of return function, the input is an ordered list of net cash flows. It starts with the investment, typically a negative cash flow, in year 0 and is followed by positive (inflows; at least one required) and negative (outflow) cash flows for all subsequent periods (source).
You can also add a “guess” to facilitate the application’s iterative process, however, this is not a required parameter.
4) Check the Resulting IRR (optional)
If you prefer to be on the safe side, check the results by calculating the present value of each net cash flow, using the calculated IRR as the discount rate.
Add a row under the net cash flow row, link the year 0 value and calculate the present value for all subsequent periods using the formula: net cash flow / (1+IRR)^period, where IRR refers to the calculated internal rate of return.
The sum of all discounted cash flows (the net present value) should be 0. You might use a “round” function for the sum to avoid confusing presentation on insignificant small numbers.
If the sum deviates significantly from 0, an error has occurred and you will need to review the previous data input.
What Are the Advantages and Disadvantages of the Internal Rate of Return Method?
- Return rate allows for comparisons irrespective of the investment amounts, amortization schedule etc.,
- IRR considers value and preference of time,
- calculation leverages the preparation work performed for the NPV assessment,
- IRR is Easy to understand and communicate,
- it can be used to compare project options and investment alternatives with different characteristics.
- IRR has the implicit assumption that inflows and liquidity can be reinvested at the IRR,
- it is also assumed that the discount rate should be identical for all periods and cash flow types,
- differences in the riskiness of investment alternatives and project options not considered,
- it does not indicate the magnitude of investments or returns,
- calculation programs are needed to determine the IRR.
What Is the Difference between IRR, Single- and Multi-Year ROI?
The main difference between both approaches is the treatment of returns or inflows. The IRR method implicitly assumes that they are re-invested at the same interest rate as all other funds – the IRR – and that the same discount rate is applicable to all cash flows and for all periods.
The multiple-period ROI, on the other hand, follows a different approach:
Returns occurring during the tenor of an instrument or project are either summed up across the periods – implicitly assuming the interest rate was 0 – or subject to a separate calculation where the value at the end of the projection’s time horizon is determined. This future value can then be calculated with an interest rate that deviates from the return rate of the investment itself.
The basic ROI does not make a difference between a one-year investment and an investment over multiple periods. In the latter case, it is therefore a ratio rather than a rate.
Both return on investment measures are suitable if an investment consists of an outflow in the beginning and a single inflow (return) in the last period. If different options are to be compared, both calculations do well as long as the time horizon is identical. Otherwise, the multi-period ROI is the measure of choice in order to calculate annualized return rates for comparison purposes.
If cash flows occur between the start and end period, the IRR typically leads to more accurate results as it takes into account that earlier cash flows are more valuable than those in late periods. While the multi-period ROI and the IRR indicate the same rate if the investment has only one single inflow, IRR is the method of choice for a series of even or uneven cash flows.
Overview of Differences between Internal Rate of Return and Return on Investment
The following table summarizes the differences between the three methods.
|Internal Rate of Return (IRR)||Return on Investment for a Single Period (basic ROI)||Return on Investment for Multiple Periods (multi-period ROI)|
|Result type||Discount rate for an NPV = 0||Ratio of returns to investment||Annual or periodic rate of return|
|Calculation||Iterative process (NPV = 0, solved for the discount rate)||Return (less cost and investment) divided by invested amount||Iterative process (Future Value of an investment, solved for interest/return rate)|
|Assumptions||Constant rate of returns for all periods; re-investment assumption analogous NPV||If multiple returns occur, they are treated as a single cumulative inflow in the last period||If multiple returns occur, they are treated as a single cumulative inflow in the last period (alternatively, their future value can be determined for the last period)|
|Typical Uses||Series of even or uneven cash flows, Comparison of investment alternatives or project options||Financial instruments with a fixed tenor, comparison of investment or project options with identical time horizon and a single return in the last period||Financial instruments with a fixed tenor, comparison of investment or project options with different tenors and a single return in the last period|
Example of the Use of ROI and IRR
A project manager is asked to determine and compare the ROI and IRR of three different project options. Cash flow projections have been prepared with the input of subject matter experts.
This example is derived from the example introduced in the overall cost-benefit analysis article and has also been used to illustrate the calculation and results of other success measures.
Cash Flow Projection
The following cash flows have been estimated for the comparison of three different project options:
|Option 1||Now||Year 1||Year 2||Year 3||Year 4||Year 5||Year 6|
|Investment and Cost (outflows)||-5000||-5000||-1000||-500||-500||-1000||-1000|
|of which: investment||-5000||-4000|
|of which: cost||0||-1000||-1000||-500||-500||-1000||-1000|
|Benefits and Earnings (inflows)||0||0||3000||5000||5000||4000||4000|
|Net Cash Flow||-5000||-5000||2000||4500||4500||3000||3000|
|Option 2||Now||Year 1||Year 2||Year 3||Year 4||Year 5||Year 6|
|Investment and Cost (outflows)||-15000||-1000||-1000||-1000||-500||-500||-1000|
|of which: investment||-15000|
|of which: cost||0||-1000||-1000||-1000||-500||-500||-1000|
|Benefits and Earnings (inflows)||0||2500||5000||5000||5000||5000||5000|
|Net Cash Flow||-15000||1500||4000||4000||4500||4500||4000|
|Option 3||Now||Year 1||Year 2||Year 3||Year 4||Year 5||Year 6|
|Investment and Cost (outflows)||-3000||-3000||-2500||-1000||-500||-500||-500|
|of which: investment||-3000||-2500||-2000||-500|
|of which: cost||0||-500||-500||-500||-500||-500||-500|
|Benefits and Earnings (inflows)||0||0||3000||4000||4000||3000||3000|
|Net Cash Flow||-3000||-3000||500||3000||3500||2500||2500|
IRR and ROI Calculation for these Examples
For the detailed ROI calculation, refer to the step-by-step ROI calculation example in this article. The IRR has been determined using Excel following the above guidance. The screenshots shown above refer to Option 1 of this example.
The results of the ROI and IRR calculations are summarized in the following table:
|Calculation and Results||Option 1||Option 2||Option 3|
|Total net returns||7000||7500||6000|
|Basic ROI||ROI = 7000 / 9000 = 77.78%||ROI = 7500 / 15000 = 50.00%||ROI = 6000 / 8000 = 75.00%|
|Annualized Return on Investment (r)||r = 10.06%||r = 6.99%||r = 9.78%|
|Calculation of annualized ROI||(1 + r) ^ 6 – 1 = 77.78%||(1 + r) ^ 6 – 1 = 50.00%||(1 + r) ^ 6 – 1 = 75.00%|
|Internal Rate of Return (IRR)||IRR = 16.76%||IRR = 11.61%||IRR = 20.68%|
The calculation of the IRR can be validated by discounting all net cash flows using the IRR as discount rate – the sum of discounted cash flows (i.e. the net present value) is supposed to be 0. The respective check looks like this:
- Option 1:
NPV (with IRR as discount rate) = -5000 – 5000*1/(1+16.76%)^1 + 2000*1/(1+16.76%)^2 + 4500*1/(1+16.76%)^3 + 4500*1/(1+16.76%)^4 + 3000*1/(1+16.76%)^5 + 3000*1/(1+16.76%)^6 = 0
- Option 2:
NPV (with IRR as discount rate) = -15000 + 1500*1/(1+11.61%)^1 + 4000*1/(1+11.61%)^2 + 4000*1/(1+11.61%)^3 + 4500*1/(1+11.61%)^4 + 4500*1/(1+11.61%)^5 + 4000*1/(1+11.61%)^6 = 0
- Option 3:
NPV (with IRR as discount rate) = -3000 – 3000*1/(1+20.68%)^1 + 500*1/(1+20.68%)^2 + 3000*1/(1+20.68%)^3 + 3500*1/(1+20.68%)^4 + 2500*1/(1+20.68%)^5 + 2500*1/(1+20.68%)^6 = 0
Interpretation of the IRR and ROI Calculation Results
If the ROI were the only criterion, Option 1 would be the preferred alternative. However, the IRR of the cash flow projection suggests that Option 3 is the most beneficial endeavor. Option 3 is far behind the other alternatives.
Not only the ranking but also the numbers of the annualized return on investment and the internal rate of return (IRR) differ significantly. The underlying reason is that the ROI does not consider the period in which cash flows occur. The internal rate of return, in contrast, involves discounting these cash flows. Thus, the present value impact of cash flows occurring in earlier periods is higher compared to cash flows with identical amounts in later periods (unless the discount rate were negative).
The IRR method seems more appropriate for this example given the characteristics of the 3 project options. The projection refers to a long time horizon of 6 years while significant cash flows occur throughout all periods. Under these conditions, the ROI method produces inaccurate results due to its methodological constraints.
IRR is a catchy indicator of the profitability of investments and project options. It can be calculated using a spreadsheet application or an IRR calculation tool. While it does not consider the magnitude of an investment or endeavor, it allows for the comparison of alternatives irrespective of their investment volume, repayment characteristics and tenor.
However, it implies a re-investment assumption by treating inflows as if they were subject to the IRR rate as in a compounded instrument. In practice, this may or may not be appropriate for comparing project options, depending on the overall assumptions and the individual use of the measures.
If you are conducting a cost-benefit analysis, you will also want to use additional methods to assess profitability given that other measures help evaluate an option from other angles.