How to Use Excel's Internal Rate of Return (IRR) Calculator (Detailed Guide)

 IRR, or internal rate of return, is a discount rate that yields a net value of 0 for a sequence of future cash flows. When choosing assets based on their returns, both the IRR and the net present value (NPV) are employed. Excel has three functions for calculating the internal rate of return: Internal Rate of Return (IRR), Modified Internal Rate of Return (MIRR), and Internal Rate of Return with Time Periods.

How to Use Excel's Internal Rate of Return (IRR) Calculator (Detailed Guide)

The IRR function computes the internal rate of return for a sequence of cash flows, the MIRR function calculates interest rates for borrowing and investing, and the XIRR function computes a more accurate internal rate of return by taking time periods into account.

How Does Net Present Value Work? How is it determined?

The present value of cash inflows minus the current value of cash withdrawals over time is the net present value (NPV). The discount rate utilized determines the net present value of a project. When comparing two investment prospects, the discount rate, which is frequently predicated on a degree of uncertainty, will have a significant impact.

How to Use Excel's Internal Rate of Return (IRR) Calculator (Detailed Guide)

Using a 20% discount rate, investment #2 is more profitable than investment #1 in the example below. When a 1% discount rate is used instead, investment #1 yields a higher return than investment #2. The order and weight of the project's cash flows, as well as the discount rate used to calculate those cash flows, all have an impact on how profitable it will be.

What Is the Difference Between IRR and NPV?

The fundamental distinction between the IRR and the NPV is that the NPV is an actual sum, whereas the IRR is the predicted interest return as a percentage of investment. Typically, investors choose projects with an IRR larger than the cost of financing. However, choosing projects based on IRR rather than NPV may result in inferior economic consequences.

Only when the project generates zero intermediate cash flows or when those investments may be invested at the existing IRR does IRR represent the real yearly return on investment.

Excel IRR Calculation

IRR is the discount rate that has the potential to reduce an investment's NPV to zero. When the IRR has only one number, evaluating the profitability of different investments becomes more intriguing.

IRRs for investments #1 and #2 in our case are 48% and 80%, respectively. This indicates that in the example of Investment #1, a $2,000 investment in 2013 will generate an annual return of 48%. For investment #2, the yield will result in an annual return of 80% with an investment of $1,000 in 2013.

How to Use Excel's Internal Rate of Return (IRR) Calculator (Detailed Guide)

Excel starts experimenting with IRR values differently for the entered series of cash flows if no parameters are specified and stop as soon as a rate is chosen which makes the NPV zero. Excel displays the error "#NUM" if it is unable to locate a rate that would reduce the NPV to zero.

• Because Excel only shows the first-rate it finds that brings the NPV to zero, we won't notice if the second parameter is not used and the investment has multiple IRR values.

Excel did not discover the NPV rate lowered to zero for investment #1 in the illustration below, hence there is no IRR. Investment #2 is also depicted in the graphic below. If the second argument is not utilized in the function, Excel returns a -10% IRR. If the second parameter is used (i.e., = IRR ($ C $ 6: $ F $ 6, C12)), the IRRs for this investment is -10% and 216%, respectively.

How to Use Excel's Internal Rate of Return (IRR) Calculator (Detailed Guide)

The investment will have a unique IRR if the cash flow sequence only includes one cash component with one sign change (from + to - or - to +). Most investments, however, begin with a negative flow followed by a sequence of positive flows when the initial investments arrive. Profits should subsequently begin to decline, as in our first scenario.

The IRR is calculated in the figure below. We just utilize the Excel IRR tool to accomplish this:

How to Use Excel's Internal Rate of Return (IRR) Calculator (Detailed Guide)

MIRR Calculation in Excel

The modified internal rate of return (MIRR) applies when a corporation utilizes variable borrowing rates for reinvestment. The IRR of the investment is calculated as in the preceding example in the graphic below, but we also account for the fact that the firm will borrow money to reinvest in the investment (negative cash flows) at a rate different from the rate at which it will reinvest the earnings (positive cash flow). Cells C5 through E5 show the cash flow range of the investment, while cells D10 and D11 reflect the corporate bond and investment rates.

How to Use Excel's Internal Rate of Return (IRR) Calculator (Detailed Guide)

How to Use Excel's Internal Rate of Return (IRR) Calculator (Detailed Guide)

Excel XIRR Calculation

Various periods are taken into account by the XIRR function. Excel requires both the cash flow amounts and the dates on which those cash flows are paid in order to utilize this function.

The financial flows in the next example are not distributed at the same time each year, as in the previous cases. Rather, they are occurring at distinct times. To solve this computation, we utilize the XIRR function shown below. We first choose the cash flow range (C5 to E5) and then the date range on which the cash flows are realized.

How to Use Excel's Internal Rate of Return (IRR) Calculator (Detailed Guide)

Excel does not have functions that can be used in situations where investments have cash flows that are received or cashed at different points in time for a company that has different borrowing rates and reinvestments, despite the fact that these situations are probably more common.