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 capital budgeting tools to evaluate an investment opportunity. This post will take you from zero to pro, showing you how to calculate IRR step by step manually and on Excel® 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.

Advanced learner and just looking for the IRR formula? Here it is:

   

Here, represents the Internal Rate of Return. reflects the cash flow that occurs at time , and denotes the initial investment.

Note that this formula to calculate IRR only works for a single cash flow.

To calculate IRR for multiple cash flows, you’d need to use interpolation. This post is largely intended for beginners, and interpolation is a tad bit more advanced.

We do, however, cover the estimation of IRR by interpolation extensively in our Investment Appraisal course.

Beginner learner? Don’t let the equation above freak you out – we’ve got your back.

Stay with us until the end of this article to learn how to calculate IRR step by step manually and on Excel®.

What is Internal Rate of Return (IRR)?

Firstly, 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.

RELATED: How to Calculate NPV

Here’s a video explanation to help you get your head around the concept:

Yes, we are on YouTube. No, we won’t ask you to “like”, “subscribe”, or click this button and that.

But people like you do seem to be subscribing to our channel increasingly 😉 – it’s great to have you all on board. We’re grateful.

How to interpret the Internal Rate of Return

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…

This is often called an IRR graph or an IRR chart. Let’s now take a look at how we read this chart by exploring how to use IRR.

How to use the IRR? (IRR Rules for Acceptance of Projects)

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.

---

Related: Investment Appraisal 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.

---

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 investment.

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

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.

Let’s now explore how to calculate IRR on Excel quickly and easily.

How to calculate IRR on Excel

Excel® makes life a lot easier. There are two functions you can use to calculate IRR on 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 layout 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.

How does Excel calculate IRR?

Okay, so now you know how to calculate IRR on Excel. But how exactly does Excel calculate IRR itself?

Excel calculates the IRR using interpolation when it’s working with multiple cash flows.

Like we said at the start of this article, interpolation is a tad bit of an advanced concept. And since this article is largely intended for beginners, we won’t be going into it here.

That being said, we do cover it extensively in our course on Investment Appraisal so do take a look there.

And while we’re on the subject of courses, know that you can get unlimited access to all our finance and investing courses via the Super Learner All Access Pass.

Okay, coming back to the IRR…

Now that you know how to calculate IRR step by step by hand (manually) and on Excel, let’s think about its limitations.

Limitations of the Internal Rate of Return (IRR)

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 textbook / “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 or 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.

Determining the number of IRRs

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.

On another note, fun fact, Vanguard uses the IRR to display investment performance for its investors!

Okay, but hopefully, you now know how to calculate IRR and you understand the process and intuition behind the Internal Rate of Return.

If you’d like to learn more about other investment appraisal techniques like the Net Present Value or Profitability Index, feel free to check out our other articles.

Alternatively, do check out the course below to gain a solid command of Investment Appraisal / Capital Budgeting.

---

Related Course: Investment Appraisal Mastery

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

Explore the Course