Internal Rate of Return

Internal rate of return is commonly known as IRR by those in the financial industry. To understand internal rate of return, you must first know what is NPV or net present value. IRR is discounted rate of return derived based on the condition that net present value for an investment is 0. IRR is then compared to the company’s discounted rate of return. If IRR is higher than the company’s / project’s discounted rate of returns, then the investment is deemed to be worthwhile for the company or investor.

The discounted rate of return for the company is determined by the investors themselves. Discounted rate of return is derived based on a number of factors. One of them is the consideration of risk. If the investor is evaluating a more risky investment, he is likely to have a higher rate of return. This is to compensate the risk that he is taking on this project. Another factor that could influence the discounted rate of return is the general market rate of return.

To calculate the internal rate of return manually (without a financial calculator) is a very laborious process. It will take you minutes if not hours. However, using Excel, you can do it in less than a minute. Assuming that the cash flows (from year 0 to year 5) is in the range “D$3:J$3”, the formula to derive the IRR is “=IRR(D$3:J$3)” without quotes. See the diagram below:

Now that we have learnt how to calculate the internal rate of return, it is important to know that IRR can only be used under certain conditions. The best way to determine if the IRR can be used is to plot the NPV of the investment against the discount rate of return. If the NPV crosses the X-axis more than once, i.e. NPV is zero more than once, than the investment is considered to have multiple internal rate of return and should be used with caution.

It is very safe to use IRR only when the cash inflow or outflow only changes once. This means that you can have a series of outflow before the inflow comes in. Once the inflow kicks in, outflow cannot be presented again. Alternatively, you could have a series of inflow first followed by a series of outflow, but inflow of funds cannot appear again. If there are multiple IRR, then it would be difficult to determine which IRR to use. The diagram below present such a scenario:

If there are changes in the cash flows from negative to positive and back again to negative, the chances of this investment having multiple internal rate of return is very high. Here is an example where the amount is the same except for year 3 where the cash flow is reversed from positive to negative and then to positive again.

Would you like to get an Excel file that will help you calculate your own IRR? If your answer is YES, then fill in your details below and we will send you an email on the instruction to download the file.