Calculating the Internal Rate of Return Using Excel – Investopedia


The internal rate of return (IRR) is the discount rate providing a net value of zero for a future series of cash flows. The IRR and net present value (NPV) are used to decide between investments and figure which one should provide the most returns.

How IRR and NPV Differ

The main difference is that the net present value (NPV) is rendered as an actual amount, while the IRR is the interest yield as a percentage expected from an investment.

When using IRR, one generally selects projects with an IRR that is greater than the cost of capital. However, selecting the internal rate of return as opposed to net present value means that if investors focus on maximizing IRR instead of NPV, there is a risk in picking a company with a return on investment bigger than the weighted average cost of capital (WACC), but less than the present return on existing assets.

IRR represents the actual annual return on investment only when the project generates zero interim cash flows – or if those investments can be invested at the current IRR. Therefore, the goal should not be to maximize the net present value. 

What Is Net Present Value (NPV)?

Net present value is the difference between the present value of cash inflows and the present value of cash outflows over a period of time.

The net present value of a project depends very closely on the discount rate used. So when it comes to comparing two investment opportunities, the choice of the discount rate, which is often based on a degree of uncertainty, will have a considerable impact.

In the example below, using a 20 percent discount rate, investment #2 shows higher profitability than investment #1; while opting instead for a discount rate of one percent, the investment shows a return bigger than investment #2. Profitability often depends on the sequence and importance of the project’s cash flow and the rate of discount applied to those cash flows.

What Is Internal Rate of Return (IRR)?

The internal rate of return is the discount rate that can bring an investment’s NPV to zero. When the IRR has only one value, this criterion becomes more interesting to compare the profitability of different investments.

In our example, the internal rate of return of investment #1 is 48 percent and for investment #2, 80 percent. This means that in the case of investment #1, which gets invested with $2,000 in 2013, the investment will yield an annual return of 48 percent. In the case of investment #2, which gets a $1,000 investment in 2013, the yield will bring an annual return of 80 percent.

If one does not enter any parameters, Excel starts testing IRR values differently for the entered series of cash flows and stops as soon as a rate is selected that brings the NPV to zero. If Excel does not find any rate reducing the NPV to zero, it shows the error “#NUM.”

If one does not use the second parameter and the investment has multiple IRR values, we will not notice because Excel will only display the first rate it finds that brings the NPV to zero.

In the image below, one can see that for investment #1, Excel does not find the NPV rate reduced to zero, so we have no IRR. 

The image below also shows investment #2. If we do not use the second parameter inside the function, Excel will find an IRR of 10 percent. On the other hand, if one uses the second parameter (i.e: = IRR ($ C $ 6: $ F $ 6, C12)), we will notice that there are two internal rates of return rendered for this investment, which are 10 percent and 216 percent.

If the cash flow sequence has only a single cash component with one sign change (from + to – or – to +), the investment will have a unique IRR. However in reality, most of the investments begin with a negative flow and a series of positive flows, as first investments come in, and then profits hopefully subside, as was the case in our first example.

Calculating IRR in Excel

In the image below, we calculate an internal rate of return (IRR).

To do this, we simply use the Excel IRR function:

Modified Internal Rate of Return (MIRR)

When a company uses different borrowing rates of reinvestment, one must calculate the modified internal rate of return (MIRR).

In the image below, we calculate the internal rate of return of the investment as in the previous example, but taking into account that the company will borrow money to plow back into the investment (negative cash flows) at a rate different from that to which it will reinvest the money earned (positive cash flow). The range C5 to E5 represents the investment’s cash flow range, and the E10 and E11 cells represent the rate on corporate bonds and the rate on investments.

In the picture below, we have shown the formula behind the Excel MIRR. We thus calculate the modified internal rate of return found in the previous example with the MIRR as its actual definition. This yields the same result: 56.98 percent.

                                   

Internal Rate of Return at Different Points in Time (XIRR)

In the example below, the cash flows are not disbursed at the same time each year – as is the case in the above examples – but rather they are happening at different periods in time. We use the XIRR function below for solving this calculation. We first select the cash flow range (C5 to E5), and then select the range of dates on which the cash flows are realized (C32 to E32).

.

One might wonder about the case of investments with cash flows received or cashed at different moments in time for a firm that has different borrowing rates and reinvestments. However, Excel does not provide functions to handle these situations although they are probably more likely to occur.



Source link