Have you ever wondered how does Excel calculate internal rate of return (IRR)? I never gave it second thought until I wrote a post on the issues with NPV calculation in Excel.

Our reader, Deyan, pointed that similar type of issue can exist with the IRR calculation in Excel.

IRR calculation in Excel is rather simple; it follows the following syntax:

Internal Rate of Return = IRR(values, [guess])

Where values are the cash flows. Values must contain at least one positive number and one negative number. Also note that it uses the order of values to interpret the order of cash flows (time).

Guess is an optional argument. I have never provided any guess values and in most cases you also do not need to provide guess argument. However, if guess is omitted, it is assumed to be 10%!

Most important thing is to note here is that the IRR formula is related to the NPV formula in Excel. The rate of return calculated by IRR formula is the interest rate corresponding to a 0 (zero) net present value.

So;

NPV(IRR(values),values) = 1.79E-09; then it will solve this equation for IRR.

It is very interesting that it is equated to 1.79E-09 and not 0, Excel says that within the accuracy of the IRR calculation, the value is effectively 0. It is 0.00000000179 but not 0.

The question is why does excel equate NPV to 0.00000000179 to calculate IRR and not to 0?

I have no idea at this moment, but I will update this once I find an answer.

Going back to our example of previous post (download link at the end of post), we have seen that Excel NPV formula assumes that the first time period of cash flow is 1 and not 0. We will take the same examples and calculate the IRR to see the difference.

Below is the IRRs of the 3 examples we discussed:

The IRRs for example 1 and Example 3 are exactly the same.

We can test the accuracy of this by equating the discount rate to the calculated IRR for all the three examples.

The NPVs in all the cases are effectively zero but not zero.

Excel IRR calculation output seems right, I do not find any issues of time period here as we found in the NPV calculation.

Do you recall the issue of time period while calculating NPV?

If we equate the discount rate to IRR, the difference between manual NPV calculation and Excel formula NPV calculation is effectively zero (Example 1), i.e. the NPV is same in both the cases. The issue of time period disappears when the discount rate is equal to IRR.

Hope you enjoyed this post on IRR calculation in Excel. If you have any questions, let me know through the comment section below.

You can also download the IRR calculation in Excel workbook FREE!