How to use the Excel IRR function to determine the Internal Rate of Return for a series of cash flows.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
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.