How To Calculate IRR – Step-by-Step Walkthrough

Often misconstrued as a very “complicated” investment appraisal technique, the Internal Rate of Return (IRR) is actually one of the easiest and most intuitive tools to evaluate an investment opportunity.

This post will take you from zero to pro, showing you how to calculate IRR after giving you a simple explanation of what it is.

You’ll also learn what its limitations are, and what else it can be (and is) used for.

What is Internal Rate of Return (IRR)?

The Internal Rate of Return (IRR) is an investment appraisal tool that helps us evaluate if we should invest in a project.

Specifically, the IRR is the point at which the Net Present Value (NPV) is equal to zero.

In other words, it’s a ‘discount rate’ that gives us a 0 NPV.

Intuitively, there are 2 interpretations of the Internal Rate of Return:

1. It shows us the maximum amount of cost of capital we can afford to pay, and
2. It shows us the amount of money we’ll earn (expressed in %), given the time value of money and other risks.

Remember that the Net Present Value and the discount rate have a negative relationship, so that the NPV decreases as the discount rate increases, and increases as the discount rate decreases.

If we plotted Net Present Value and the discount rate, we’d have something like this…

How to use the IRR?

Notice the graph above… As long as the firm’s / project’s cost of capital (discount rate (“r”)) is lower than the Internal Rate of Return, it’s earning a positive NPV.

Any cost of capital that is greater than the IRR clearly results in a negative NPV.

This leads to the appraisal rule for IRR:

• If , accept the project.
• If , reject the project.
• If , accept* the project.

The last instance, where means that the project will earn a .

In practice, it would be unwise to accept such a project.

From an academic standpoint however, projects with , and , tend to be accepted.

That’s because these projects do not lose money.

Generally speaking, the Internal Rate of Return and Net Present Value will end up giving you similar appraisal results.

Thus, IRR analysis is somewhat similar to the NPV, at least in terms of outcomes.

But the main difference between the two is that the IRR is a percentage based result that ignores the magnitude of an investment opportunity.

Whereas the NPV is a “dollar based tool”, which factors in the magnitude of an investment opportunity.

---

Related Course

This Article features a concept that is covered extensively in our course on Investment Appraisal Mastery.

If you’re interested in mastering the NPV and other investment appraisal / capital budgeting techniques, then you should definitely check out the course.

---

How to calculate IRR?

The IRR calculation is typically done with some algebraic manipulations by hand, or by using something like Excel® or Google Sheets.

Let’s take a look at the algebraic / manual method first.

Consider this example.

UE Inc. expects to earn $5,800 next year if it invests $5,000 today.

What is the IRR of this investment?

We start with the fundamentals, beginning with the equation for the Net Present Value:

   

Where refers to the Present Value of future cash flows. And reflects the initial investment.

   

In UE Inc’s case, there’s only one cash flow (CF); strictly, there’s only one cash inflow. So t = n = 1. The equation then becomes:

   

Since is the point at which , it is the point at which . Because when , must be equal to zero!

Using this logic, and the numbers from the question including an initial investment of $5,000, we have:

   

   

   

Multiply both sides by (1+IRR) and divide both sides by $5,000 to get the final answer.

   

   

   

So there you have it! It really is that simple.

Now, this is not an IRR formula by any means. Rather, we’ve just solved for the IRR using a little bit of algebra.

How to calculate IRR using Excel?

Excel® makes life a lot easier. There are two functions you could use to calculate IRR using Excel®, creating your very own IRR calculator:

1. The “IRR” function, or
2. The “RATE” function.

The Excel IRR function takes a bit more work in that you need to lay out the stream of cash outflow and cash inflow first, and only then apply the formula. Like this…

Notice that we’ve laid out -5,000 in one cell, and then 5,800 right beside it.

You could of course place them one below the other, but the important thing is that the two are right next to each other, failing which the Excel IRR function won’t work!

In this particular example (UE Inc.), using the IRR function / RATE function requires about the same amount of time and effort.

But when you’ve got larger future cash flow streams (e.g. 5, 10, 20, 100 years) then you’ll want to use the RATE function, like so:

Notice that we just left the “pmt” option blank, hence the double commas.

You’ll want to use the “pmt” option when you’ve got an annuity stream of cash flows.

While a very useful technique, the IRR is not without limitations.

For instance, the IRR cannot handle “unconventional” (read, real-life) cash flows / uneven cash flow.

Consider the text-book / “conventional” cash flow stream…

Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
-$100,000	$35,000	$42,000	$27,000	$62,000	$71,000

By conventional we mean a stream that has one initial outflow (the initial investment), followed by a stream of inflows (the cash inflows from Year 1 – Year 5).

The IRR for this is 32.24%. See if you can calculate it!

Now consider a more real-world, “unconventional” cash flow stream…

Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
-$100,000	$52,000	-$60,000	$39,000	$84,000	$91,000

By unconventional, we mean a stream that has more than 1 negative cash flow.

Or in simple English, it means that an investment is made on more than one occasion.

In the example above, $100,000 is invested today (Year 0), and a further $60,000 is invested 2 years later.

In an unconventional / uneven cash flow setting, multiple IRRs can exist, and this is perhaps the greatest limitation of the Internal Rate of Return.

Here, you’ll have up to 3 IRRs! And there’s no way of really knowing which one is the “true” IRR.

So even though the Excel IRR calculation returns an IRR = 20.43% for this stream, one has to take it with a pinch of salt.

And this goes to show why it’s important to understand things, rather than just blindly inputting numbers / functions in software.

How do we know we’ll have up to 3 IRRs?

Easy – just count the number of times the ‘sign’ changed.

From Year 0 to Year 1, the sign changed once (negative cash flow of -$100,000 to a positive cash flow of $52,000.

Then there was another change from Year 1 to Year 2 (positive cash flow to a negative one).

And finally, another change from Year 2 to Year 3 (negative cash flow to a positive one).

The cash flows from Year 3 to Year 5 are all positive, meaning the number of ‘sign changes’ is zero.

Other uses of the Internal Rate of Return

Apart from investment appraisal, the Internal Rate of Return is also useful for valuing bonds!

In fact, a bond’s discount rate (aka Yield to Maturity, or more simply, Yield) is its Internal Rate of Return!

So if you know how to calculate the IRR, then you’re pretty much 60% on your way to knowing how to value bonds.

---

Related Course

Do you want to become a PRO at Investment Appraisal / Capital Budgeting?

Explore the Course