How to Calculate Internal Rate of Return (IRR) in Excel

This guide will explain how to calculate the internal rate of return or IRR of a business.

The internal rate of return (IRR) is a key financial metric you can use to assess the profitability of investments or projects. Calculating the IRR is a common practice d in project appraisals, risk assessment, and decision-making in investing.

The IRR is compared to the hurdle rate, which is the minimum rate of return an investment needs e to be considered a worthwhile investment. A project or investment with a  higher IRR than a predetermined hurdle rate, is more likely to gain approval by a company or investor.

We can calculate the internal rate of return using the built-in IRR function in Microsoft Excel. In this guide, we will provide a step-by-step tutorial on how to calculate the internal rate of return of a project or investment.

The Anatomy of the IRR Function

The syntax of the IRR function is as follows:

=IRR(values, [guess])

Let’s look at each argument to understand how to use the IRR function.

  • IRR() refers to our IRR function. This function returns the internal rate of return for a series of cash flows. This value is the interest rate received for an investment with payments and income that occur at regular periods.
  • values refer to the array or range of cells containing numbers for which you would like to calculate the internal rate of return. The values must contain at least one positive value and one negative value.
  • The guess argument is an optional input that should be a number that you guess is close to the result of IRR. When Excel calculates the IRR, it will start with this guess and perform an iterative calculation until the function finds a result. In most cases, you will not need to provide a guess. By default, the value is set to 0.1.
  • Each cell in values must be positive if it represents income from the perspective of the investor or negative if it represents payments. 

A Real Example of Calculating Internal Rate of Return in Excel

The IRR function tries to find the internal rate of return by solving for some discount rate, which will make the sum of the present value of cash flows equal to zero. In other words, this is the rate at which an investment breaks even.

Let’s explore a simple example where we’ll need to calculate the internal rate of return of a project.

set inflow and outflow transactions

In the example above, we have a table containing the cash inflows and outflows of a specific project. These cash flows include the initial cost of the project and the net income over the next five years. Suppose we want to find out if the project meets our required rate of return of 10%.

We can use the following Excel formula to find the internal rate of return:

=IRR(B2:B7)
calculate internal rate of return

Using the IRR function, we find out that the internal rate of return is 8%. Since this value is lower than our required rate of return, we may need to dismiss the project for other opportunities.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to calculate the internal rate of return of a project or investment.

How to Calculate Internal Rate of Return (IRR) in Excel

  1. First, input the positive and negative cash flows of your investment.
    sample transactions
    In our example above, we’ve listed down the cost and income associated with our investment.
  2. Next, type the IRR function in a new cell and provide the range containing the positive and negative cash flows
    use IRR function
  3. Hit the Enter key to evaluate the IRR function. Excel should now return the internal rate of return of your investment.
    calculate internal rate of return with IRR function

These are all the steps you need to know to start calculating the internal rate of return of an investment.

FAQs

  1. Can I use IRR for both simple and complex cash flow scenarios?
    Yes, IRR is versatile and works for a variety of cash flow scenarios, whether the cash flows are consistent or irregular. Excel’s IRR function accommodates a wide range of financial scenarios.

  2. How do I interpret the IRR result?
    If the calculated IRR is higher than the required rate of return or hurdle rate, the investment is potentially attractive. A lower IRR indicates that the project may not be considered financially viable.

  3. Why is my IRR function returning a #NUM! Error?
    The IRR function returns a #NUM! Error if the IRR does not solve after 20 iterations. You may need to change the guess argument in your function to ensure that the function finds the IRR value.

To learn more about using Excel for financial modeling, you can read our post on how to perform sensitivity analysis in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks! 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like