Excel IRR function

How to use the Excel IRR function to determine the Internal Rate of Return for a series of cash flows.

• 1:54
• Skill Level: 5
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Excel IRR function

In this example we have a series of cash flows starting with an Initial investment, a negative number in Period 0, and then a whole bunch of cash inflows over the next five years. We’d like to work out what the internal rate of return of these cash flows are. For this we can use the Excel IRR function;

• If we click in the cell where we want the result
• and activate the Function Wizard
• we can go to the Financial category,
• we go to IRR
• and when we say ok, we are asked for these arguments
• the first one is the Values, which is basically an array of reference to cells that contain the numbers for which we want to calculate the IRR, so in that case it would be this list here,
•  and then you are asked for a Guess, and in some cases the IRR cannot come up with a number, you need to put in a guess, and generally I put in .1 or 10%
• and when you click ok
• You will see that the IRR is calculated.

In order for the IRR to work you have to understand two issues. The first is that your series of cash flows must contain at least one negative number, and at least one positive number in order for it to work, otherwise it’ll deliver an error message. The second issue is that unlike the NPV function, the IRR function assumes that the first value it is given occurs in period 0 and after that it goes to Period1,2; whereas the NPV function assumes that the first number it is given occurs at the end of Period 1. This is extremely important especially if you are trying to reconcile your NPV’s to your IRR numbers.