The internal rate of return (IRR) is the discount rate that provides a net zero value for a future series of cash flows. IRR and Net Present Value (NPV) are used to screen investments based on returns.
Difference between IRR and NPV
The key difference between IRR and NPV is that NPV is an actual amount while IRR is the expected interest return percentage of an investment.
Investors generally choose projects whose IRR is higher than the cost of capital. However, selecting projects based on maximizing IRR as opposed to NPV could increase the risk of achieving a return on investment in excess of the weighted average cost of capital (WACC) but below the current yield on existing assets.
The IRR represents the actual annual return on the investment only if the project does not generate interim cash flow – or if these investments can be made at the current IRR. Therefore, the goal should not be to maximize NPV.
What is Net Present Value?
Present value is the difference between the present value of cash inflows and the present value of cash outflows over time.
The present value of a project depends on the discount rate used. Thus, when comparing two investment opportunities, the choice of discount rate, which is often associated with a degree of uncertainty, will have a significant impact.
In the example below, investment #2 shows a higher return than investment #1 at a discount rate of 20%. However, at a discount rate of 1%, investment #1 shows a higher return than investment #2 The order and magnitude of the project’s cash flows and the discount rate applied to those cash flows.
What is the internal rate of return?
The IRR is the discount rate that can reduce the present value of an investment to zero. If the IRR has only one value, this criterion becomes more interesting when comparing the profitability of different investments.
In our example, Asset #1 has an IRR of 48% and Asset #2 has an IRR of 80%. This means that in the case of investment #1 with a $2,000 investment in 2013, the investment is generating a 48% annual return. For investment #2 with a $1,000 investment in 2013, the return is 80% per year.
If no parameters are entered, Excel will start testing the IRR values differently for the entered cash flow series and stop as soon as a rate that brings the NPV to zero is selected. If Excel doesn’t find a rate that reduces the present value to zero, it displays the “#NUM” error.
If the second parameter is not used and the investment has multiple IRR values, we will not notice this because Excel only shows the first rate found, which brings the NPV to zero.
In the image below, for investment #1, Excel cannot find the zeroed NPV rate, so we have no IRR.
The image below also shows investment #2. If the second parameter in the function is not used, Excel finds an IRR of 10%. On the other hand, if the second parameter is used (ie =IRR($C$6:$F$6,C12)), two IRRs are returned for this investment, namely 10% and 216%.
If the sequence of cash flows contains only one cash component with a change of sign (from + to – or from – to +), then the investment has a clear IRR. However, most investments start with a negative inflow and a series of positive inflows as the first investments come in. The gains then vanish, as in our first example.
Calculate IRR in Excel
In the figure below we calculate the IRR.
To do this, we simply use Excel’s IRR function:
Modified Internal Rate of Return (IRR)
When a company uses different reinvestment rates for debt, the modified internal rate of return (MIRR) applies.
In the image below, we calculate the IRR of the investment as in the previous example, but take into account that the company borrows money to reinvest in the investment (negative cash flows) at a different rate than it reinvests money made (more positive cash flow). The range C5 through E5 represents the cash flow range of the investment, and cells E10 and E11 represent the corporate bond rate and the investment rate.
The image below shows the formula behind the Excel MIRR. We calculate the internal rate of return (MIRR) found in the previous example, using MIRR as the actual definition. This gives the same result: 56.98%.
(−NPV(rRate, Values[positive])×(1rRate)nNPV(Rate, Values[negative])×(1frate))1n−1−1begin{aligned}left(frac{-text{NPV}(textit{rrate, valeurs}[textit{positive}])times(1+textit{rrate})^n}{text{NPV}(textit{frate, values}[textit{negative}])times(1+textit{frate})}right)^{frac{1}{n-1}}-1end{aligned}
(NPV(frate, values[negative])×(1frate)−NPV(rrate, values[positive])×(1rrate)n)n−11−1
Internal rate of return at different points in time (XIRR)
In the example below, the cash flows are not paid out at the same time every year – as in the examples above. On the contrary, they occur at different times. We use the XIRR function below to solve this calculation. First we select the cash flow range (C5 to E5), then the period of cash flow realization (C32 to E32).
.
For investments whose cash flows are received or cashed at different times for a company with different borrowing and reinvestment rates, Excel does not provide functions that apply to these situations, although they are more likely to occur.
https://www.investopedia.com/articles/investing/102715/calculating-internal-rate-return-using-excel.asp