IRR function

In this lesson you can learn about IRR function.

IRR function calculates the internal rate of return, which can be understood as limiting the size of interest rate at which profit on the project is equal to 0 If the discount rate is lower than the IRR, the profit is positive. If the discount rate is higher than the IRR, the project will bring a loss. It can also be understood as the highest interest rate, we can accept that the project did not bring losses.

Syntax is:

=IRR(values,[guess])

  • values - range of cells, for which you calculate the internal rate of return. Calculation of the internal rate of return requires at least one positive number and one negative number in values
  • guess - the number of presumably similar to the resulting IRR. In most cases, this argument is not required to calculate the IRR. If omitted assumptions, it is assumed that its value is 0.1 (10%).

Microsoft Excel uses an iterative technique for calculating IRR. Starting from the result, IRR repeats the calculation until the result is accurate to 0.00001%. If the IRR can not find a result after 20 tries, Excel shows the #NUM! error. If the IRR shows the #NUM! or if the result is not close to the expected value, the test should be repeated with a other value of [guess].

How to interpret the IRR?

If the value of IRR is greater than the current cost of money plus the risk premium (premium is about 3-6%), the project is worthy of implementation - came out to zero by strong discounting. Discounting the cost of the premium money will give you a positive NPV, so you might want to implement the project. If it is less - the project should be rejected, taking into account the time value of money will bring the total loss.

Example How to calculate internal rate of return?

Capital expenditures related to the ProjectX totaled $280,000 (from the beginning of first year). Net cash flows (as a result of this project) at the end of each year are as follows:

Year 1 $90 000

Year 2 $85 000

Year 3 $90 000

Year 4 $110 000

What is the internal rate of return for this investment?

 

In this case, the formula will take the following form: =IRR (A2: A6)

IRR function example

 

Thus you get the following result: IRR = 12.34%

If the cost of capital is less than 12.34%, then the ProjectX should be adopted for implementation. Of course you must also calculate if the risk premium is okay for you. If it is not you should cancel the project.