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

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

So, How does Excel calculate IRR?

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 a guess argument. However, if the 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 the previous post (download link at the end of the post), we have seen that the 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.

Example on How does Excel calculate IRR?

Below are 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 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 the 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 the same in both cases. The issue of the time period disappears when the discount rate is equal to IRR.

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

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

Check out more useful articles on Our Blog Page

Are you someone involved with real estate feasibility?

We are excited to launch the next generation of real estate feasibility software by April 2023.

claim your 15% early-bird discount

Know more about the product features

Learn More