Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
Article Categories

Posts Tagged ‘Internal Rate of Return’

Excel Financial Functions–Calculating IRR

This week, guest blogger Nick Williams discusses calculating the Internal Rate of Return for a regular cashflow. Nick is an Access tutor based in the U.K.

IRR – Internal Rate Of Return

IRR returns an interest rate. It is the interest rate that would need to be applied to all of the cashflows in the calculation to give an NPV of zero for the project (see above). In short it tells you the return on the money invested in a project and is often used as an investment decision tool. It is designed to work with a set of regularly spaced cashflows only.

IRR is the same as interest rate of savings or loan, however there is it applied to a very straight forward situation.

The syntax of the IRR function in Excel is IRR(values, [guess])

values is a required parameter. It is an array or reference to a set of cells that contain the cash flow values for the project.

guess is an optional parameter. It is a number that you are guessing is close to the result of IRR. If this parameter is omitted, it is set to 0.1 (10%) by default.

Continuing with our example of above we can see the function in use below.


Intuitively we know that that is right. Above we discovered that the project had a negative NPV if we applied a 3% discount rate. Therefore we knew that the discount rate to get an NPV of zero had to be below 3%.

Again, as above, lets add two more years of income and see what happens to the result. We got a positive NPV using a 3% rate before so we know that the answer will be above 3% before we start.


Note: Microsoft Excel uses an iterative technique for calculating IRR starting from guess. If IRR can’t find a result that works after 20 tries/iterations, the #NUM! error value is returned. If this happens, or if the result is not what you expected to see, try again with setting a different value for guess.