Share
Written by: Richard Frykberg
Internal Rate of Return (IRR) is a measure of profitability of investment opportunities. In theory, a project should be undertaken if its future cash flows are expected to return a positive net present value (NPV). In practice, due to funding and resourcing constraints, not all positive NPV projects can be successfully completed, and you will need to prioritize your candidate projects when selecting your project portfolio.
IRR is a way of identifying the most profitable initiatives given capital constraints. Using both IRR and NPV is important, however, IRR calculations can be grossly misleading, and other much simpler metrics, such as good old-fashioned payback period, may provide a more meaningful financial calculation.
Defining Internal Rate of Return (IRR)
Internal Rate of Return (IRR) is the effective discount rate that would cause a series of cashflows to produce a NIL net present value. In essence, IRR is the discount rate that makes the NPV of a capital project equal to zero. This implies that an Internal Rate of Return greater than the required discount rate would produce a positive NPV.
Hurdle Rate
IRR is thus often compared to a ‘hurdle rate’. Many organizations mandate that all investment projects must achieve an IRR exceeding a pre-defined hurdle rate to be considered for inclusion in the capital budget.
This required hurdle rate should be relative to both an organization’s cost of capital and the risk profile of the initiative. Cost of capital is the return demanded by debt and equity stakeholders and incorporates a risk premium relative to the environment within which an organization operates. An additional risk premium is normally applied when an initiative falls outside the regular operating environment, for example when a traditional physical business invests substantially in digital products with high opportunity costs.
IRR Calculation and Formula
Mathematically, IRR is the rate(s) that satisfies the following formula:
Where:
IRR = Rate of return per Period
N = is the number of Periods
CFn = Cash Flow in Period n
Practically, most of us use Spreadsheets to calculate Internal Rate of Return quite easily as per below:
Where:
IRR = Rate of return per Period
N = is the number of Periods
CFn = Cash Flow in Period n
Practically, most of us use Spreadsheets to calculate Internal Rate of Return quite easily as per below:
Figure 1 – IRR Function in Microsoft Excel can be used to easily calculate IRR for a series of cashflows
Note that the Guess value is optional, but is required when multiple IRRs are possible, see below.
Normal IRR Calculation
IRR can be calculated for a series of cashflows by simply applying the built-in Microsoft Excel® IRR formula.
For example, an investment in a new vehicle that is expected to deliver positive returns over the following six years is illustrated below:
Figure 2 – IRR can be easily calculated in a spreadsheet
No IRR
It is not always possible (or practical) to calculate an Internal Rate of Return. This occurs when all cashflows are either negative or positive.
For example, it is not normally meaningful to prepare a benefit analysis for a project to replace an essential component in a manufacturing plant. In these cases, the cashflow demand is normally just specified in terms of cost, as per the example below:
Figure 3 – IRR on a single cashflow outflow is indeterminate
Similarly, if a project is likely to produce positive cashflows from commencement, an IRR can also not be calculated.
For example, the purchase of a SaaS software solution that provides immediate benefits produces only positive cashflows:
Figure 4 – IRR on a series of positive cashflows is indeterminate
Negative IRR
Not all capital investments will produce a positive IRR. Where nominal outflows exceed anticipated inflows, there is no positive IRR that can be calculated. In these cases, organizations may calculate a negative IRR, and use this as the basis for selecting the ‘least-worst’ option when faced with a mandatory investment, such as regulatory compliance.
Figure 5 – Negative IRR as a result of insufficient return
Multiple IRR’s
In addition, there may be multiple IRR’s that produce a zero net present value.
The following cashflows may represent a project with a terminal payment, for site rectification as an example:
| Total | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 |
| -25,000 | -150,000 | 85,000 | 85,000 | 85,000 | 85,000 | 85,000 | -300,000 |
The cashflows resolve to a zero NPV with the following internal rates of return:
| IRR | 8% |
| IRR | 25% |
In general, an IRR can be determined whenever the Net Present Value of a series of cashflows flips signs as illustrated below:
Figure 6 – NPV at various Hurdle Rates
This NPV curve can be explained as follows:
- At very low hurdle rates, the NPV tends towards the nominal total of negative 25,000.
- The NPV of the residual payment is reduced at a discount rate of circa 15%, maximizing the value of the investment.
- At a very high hurdle rate (greater than 25%), even the early returns are heavily discounted, leading towards a negative NPV overall.
IRR Formula and Calculation Methods
Key decisions when calculating IRR are to determine which cashflows to consider, and whether to consider monthly or annual amounts.
Nominal Investment and EBITDA Return
IRR can be calculated on the nominal investment amounts and earnings before interest, tax, and depreciation (EBITDA) per period. This is a very simplified approach that ignores the effect of taxation.
Cashflow Basis
IRR can be calculated on the nominal cashflows per period considering the impact of taxation. Normally, the tax shield attributable to an investment is based on the useful life of the asset, as it is normally only the depreciation that is deductible for taxation purposes. For consistency, any related returns are also evaluated after tax.
This is the most commonly utilized series of cashflows for calculating IRR on project investments.
Discounted Cashflow
IRR can be calculated on future net returns after tax, discounted to reflect organizational cost of capital and a risk premium.
Monthly or Annual Returns
Depending on the available data, IRR can be calculated on monthly or annual figures.
Where IRR is calculated on a monthly basis, IRR can be converted to an annualized rate in two ways:
- Simple interest annualization: IRR (annual) = IRR (per month) * 12
- Compound interest annualization: IRR (annual) = (1 + IRR (per month)) ^12-1
Payback Period
Before we examine an example of the different IRR calculation methods, let’s first refresh the Payback Period financial metric.
The payback period is quantifying the number of years required to generate a positive return on an investment. In other words, payback period is the amount of time between the initial costs of a project and the break-even point (BEP).
Similar to IRR, there may never be a payback period if the series of cashflows never turns positive. Also, like IRR, there may be multiple payback periods calculated where the series of cumulative cashflows flips signs multiple times.
Payback Period Calculation Methods
The Standard Payback Period Formula
Payback Period = Initial Investment ÷ Cash Flow Per Year
EBITDA, Cashflow or Discounted Cashflow
Like with IRR, payback period can be calculated on earnings before interest, tax, and depreciation (EBITDA). Payback period can also be calculated on cashflow after accounting for depreciation and taxation.
Payback period can be calculated on nominal cashflows (as with IRR), but it is normally calculated based on discounted cashflows, using an organization’s required cost of capital plus a risk premium as the discount rate.
Monthly vs Annual Payback Calculation
Payback period can be calculated by month when monthly expected cashflow is available, or on an annual cash flow basis. Where payback is calculated per on month, it is normally converted into an annual payback by dividing the number of months by 12 (months in a year).
Comparing IRR vs Payback Period with an Example
The impact of the various calculation methods is best illustrated with a simple example.
Consider an asset investment of 180,000 made on 1 Jan 2025, expected to save 10,000 per month for its useful life of 3 years. Assuming a tax rate of 30%, and a discount rate of 10% per annum, the monthly and annual movements can be calculated.
Monthly Movements
The impact of this investment by month is presented below:
Figure 7 – Example cost saving investment example – monthly impact
The EBITDA return is based on nominal, pre-tax, monthly benefits of 10,000.
The cashflow is shown based on returns of 10,000, offsetting depreciation of 5,000 and tax paid of 1,500.
Discounted cashflow is the NPV of each month’s cashflow at an IRR annual discount rate of 10%.
Annual Movements
The same data can be shown on an annual basis as follows:
Figure 8 – Cost saving investment example – monthly impact
Payback Period Analysis
The Payback period calculated on all potential bases is presented below:
Figure 9 – Payback period calculations using different methods
As can be seen from the chart, the results are intuitively consistent. This investment is clearly advantageous, and the savings will pay back the original investment in 1.5 to 2 years, depending on the calculation method. We expect the payback period to be slightly longer if we discount the future savings.
Because of the compounding effect of monthly discounting, future cashflows are worth less when discounted by month than by year. For example:
NPV of 1000 in 2 years time at 12% annual discount rate=1000/(1+12%)^2 = 797
NPV of 1000 in 24 months time at 1% monthly discount rate=1000/(1+1%)^12 = 788
Consequently, it makes sense that the payback period calculated based on monthly amounts is slightly longer than the annual equivalent.
IRR Analysis
In the following chart, we show IRR as calculated on monthly EBITDA, Cashflow, and Discounted Cashflow. We convert the monthly IRR’s to annualized IRRs based on simple annualization (multiplying monthly rate by 12) and by applying the compound interest formula.
Figure 10 – Example of IRR calculations using different methods
As can be seen from the chart, subtle differences in the method of IRR calculation can radically impact the calculated value.
Using the same basic scenario assumptions, we get IRR’s ranging from 31% to 173%. Clearly this is a worthwhile investment. But are these results intuitive?
With such significant variations possible in the calculation of IRR, it is obviously critical that identical methods are consistently applied when IRR is used as basis of project evaluation and ranking. Where sponsors are utilizing spreadsheets to justify investments, the formulae contained therein should be carefully scrutinized to ensure accuracy and consistency.
While IRR is useful is a filter for project selection, it is a poor metric to apply by itself in project ranking and prioritization. Given that small changes in the timing of cashflows, or in the calculation method, can significantly impact IRR, IRR should only be used as one measure of project benefit assessment. A more robust method of benefit scoring is to combine NPV, IRR and payback period to provide a more balanced assessment.
Modern capital budgeting and project portfolio management theory tends to dismiss payback period as a valid project evaluation metric. However, perhaps one of the reasons it remains the most relied upon project assessment metrics is its very simplicity, robustness, and intuitiveness. Few people have an intuitive understanding of an IRR of 74%. By contrast everyone can understand a payback period of between 1.5 and 2 years – no matter how you choose to calculate it!
Financial Metrics for Effective Capital Project Evaluation
Effective capital project evaluation and prioritization is essential for sustained long-term success. IRR is a key metric and should be evaluated and considered when evaluating candidate projects. However, great care should be taken to ensure the consistency of the calculation method, as variations in approach can lead to significantly different results.
The optimal project scoring and ranking methodology considers a variety of benefit metrics. NPV is valuable as it indicates absolute return, and naturally projects with the greatest potential value should rank highly. IRR is valuable as an indication of relative return, and naturally projects with a high IRR utilize scare capital more efficiently. Executives are also concerned about risk, and the longer the investment timescale, the greater the inherent risk that the projected return will not be realized to changes in the operating environment, including technological, competitive, market and regulatory disruption.
Payback period is a sensible and valid measure that embodies the key concepts of positive net present value, capital efficiency, and risk. The simplicity of calculating payback period makes it a more reliable metric. The intuitive understanding of payback period makes it the most common executive assessment tool.
