How to Calculate Internal Rate of Return (IRR)

The Internal Rate of Return (IRR) evaluates the cost of a project. It is the rate of

return at which the present value of a project’s cash outflows and the present

value of project’s cash inflows are equal (NPV is equal to 0). If the IRR is

greater than the expected rate of return then the project should be accepted as

this project will earn more benefit to the organization.

Use MS Excel to calculate IRR

For example, we’ll start by leveraging our original project’s cash flows…

From the above statistics, we’ll construct our MS Excel spreadsheet as below…

If you look at the IRR formula in cell I5, you will notice that the expected rate of

return (cell A1) is not leveraged. However, we still want to see the expected rate

of return on this sheet as a comparison point with our calculated IRR’s in cells I5

and I9.

The IRR formula just leverages the IRR function and the range of values that

represent the project’s cash flows (cells B5 through H5 and cells B9 through H9).

Since the IRR of both projects are above the 10% expected rate of return

threshold, both projects are acceptable to undertake. If you only had one choice

of project to choose from, you’d select Project A. This leads us to the below

consideration for using IRR.

Pitfall of using IRR

A pitfall of using IRR is that it does not distinguish between the timing of

negative and positive cash flows. For example, the below cash flows represent

projects C and D.

If you calculate the IRR for these projects, you will arrive at the MS Excel

Calculations as noted below...

As you can see, both projects are above the expected rate of return so both

apparently can proceed. However, let’s take a look when you calculate the Net Present Value (NPV) of these projects…

Now, project B does not look appealing at all and should NOT be undertaken

despite having the same IRR as project A. What is happening here?

What’s going on is that the IRR formula is not differentiating if the cash flow for

a particular year is positive (cash flow) or negative (payment) for a project.

If you’re interested in learning more about IT Project Finance, please see my course IT Project Budget and Cost Management where you will learn how to manage a $1M IT Software project budget.

If you're interested in learning how MS Excel can make an IT Project Manager's life easier, please see my course MS Excel for Project Managers. You will learn how to leverage Excel to create automated project artifacts that you use in your every-day project management.

81 views0 comments