IRR Function

This function represents the internal rate of return, which measures the profitability of an investment project.

Author: Kevin Henderson
Kevin Henderson
Kevin Henderson
Private Equity | Corporate Finance

Kevin is currently the Head of Execution and a Vice President at Ion Pacific, a merchant bank and asset manager based Hong Kong that invests in the technology sector globally. Prior to joining Ion Pacific, Kevin was a Vice President at Accordion Partners, a consulting firm that works with management teams at portfolio companies of leading private equity firms.

Previously, he was an Associate in the Power, Energy, and Infrastructure Investment Banking group at Lazard in New York where he completed numerous M&A transactions and advised corporate clients on a range of financial and strategic issues. Kevin began his career in corporate finance roles at Enbridge Inc. in Canada. During his time at Enbridge Kevin worked across the finance function gaining experience in treasury, corporate planning, and investor relations.

Kevin holds an MBA from Harvard Business School, a Bachelor of Commerce Degree from Queen's University and is a CFA Charterholder.

View Profile
Reviewed By: Matthew Retzloff
Matthew Retzloff
Matthew Retzloff
Investment Banking | Corporate Development

Matthew started his finance career working as an investment banking analyst for Falcon Capital Partners, a healthcare IT boutique, before moving on to work for Raymond James Financial, Inc in their specialty finance coverage group in Atlanta. Matthew then started in a role in corporate development at Babcock & Wilcox before moving to a corporate development associate role with Caesars Entertainment Corporation where he currently is. Matthew provides support to Caesars' M&A processes including evaluating inbound teasers/CIMs to identify possible acquisition targets, due diligence, constructing financial models, corporate valuation, and interacting with potential acquisition targets.

Matthew has a Bachelor of Science in Accounting and Business Administration and a Bachelor of Arts in German from University of North Carolina.

View Profile
Last Updated:January 30, 2024
In This Article

What Is the IRR Function? 

The IRR function represents the internal rate of return, which measures the profitability of an investment project. The IRR function estimates the discount rate at which future cash flows' net present value (NPV) equals zero. 

IRR is important because it helps investors and managers compare different projects and decide which ones are worth pursuing. A higher IRR means a higher return on investment (ROI). 

However, IRR is not a perfect indicator of a project's profitability. It has some limitations and disadvantages, such as

  1. It may not be available for some projects with unconventional cash flows (e.g., negative-positive-negative).

  2. It may not be accurate if they have different sizes, maturities, or timings of cash flows (in such cases, the net present value method is preferable).

  3. It may be affected by accounting methods and depreciation rates that affect cash flow estimates.

Therefore, IRR should be used cautiously and in conjunction with other criteria, such as NPV, payback period, profitability index, etc., to evaluate an investment project.

Key Takeaways

  • The IRR function may return more than one or no value if the cash flows have multiple sign changes or are not ordered chronologically. You may need to use other functions, such as XIRR or MIRR, to find a unique value.

  • The IRR function may not converge to a result within 20 iterations if the guess is too far from the true IRR. You may need to try different guesses or other methods to find the IRR in such cases.

  • The IRR may also give misleading results when comparing mutually exclusive projects with different initial outlays, timings, or durations.

  • The Excel IRR function helps in evaluating the profitability of investments and projects based on their cash flows.

  • Net present value helps investors compare different options and choose the one that maximizes their wealth.

What is NPV and how can we use it to Evaluate Investment projects?

Net Present Value (NPV) is a financial metric that helps investors and managers calculate the profitability and feasibility of an investment project. NPV is just one of many metrics that should be considered and should not be referenced alone for financial advice.

NPV is calculated by discounting all future cash flows by a suitable discount rate. Net present value helps investors compare different options and choose the one that maximizes their wealth.

Because the money is losing its value over time, future cash flows must be discounted at the cost of capital rate.

The risk of an investment opportunity is the uncertainty or variability of future cash flows (the higher the risk, the higher the discount rate required to invest in a project). The discount rate reflects the minimum acceptable rate of return for an investor or company.

Why is NPV so important?  Because it helps investors and managers compare different projects or investments based on their net profitability and return on investment.

  • A positive NPV means that the project or investment generates more cash inflows than cash outflows and has a higher rate of return than the discount rate.

  • A negative NPV means that the project or investment will result in a net loss and the return will be less than the discount rate.

How to calculate NPV?

To calculate NPV, after determining the timing and amount of future cash flows from a project or investment, choose a discount rate that reflects your required return or cost of capital.

You can use the following formula:

Where:

  • Rt = net cash inflows-outflows during a single period t

  • r = discount rate or return that could be earned in alternative investments

  • t = number of periods

Let us go through an example of NPV calculation. Imagine you’re considering investing in a project that requires an initial outlay of $100,000. The project will generate annual cash inflows of $20,000 for 10 years. 

To calculate the NPV of this project, you can use the following formula:

NPV = -100,000 + 20,000/ (1 + 0.12)1 + 20,000/ (1 + 0.12)2 + 20,000/ (1 + 0.12) + … + 20,000/ (1 + 0.12)10 = $13,004.46

This means that investing in this project will make us $13,004.46 over 10 years.

How to calculate IRR?

To calculate IRR, you need to use a trial-and-error method. You start with an initial guess for the IRR and plug it into the formula for net present value (NPV). 

If the NPV is positive, you increase your guess. If the NPV is negative, you decrease your guess. You repeat this process until you find an IRR that makes the NPV close to zero.

For example, suppose you invest $10,000 in a project that will generate $3,000 in cash flows for four years. The initial guess for the IRR is 10%. The NPV formula is

NPV = -C0 + C1/ (1 + r) + C2/ (1 + r)2 + ... + Cn/ (1 + r)n

where 

  • C0 is the initial investment 

  • C1, C2, ..., Cn are the cash flows in each year 

  • r is the interest rate 

  • n is the number of years

Plugging in the values, we get:

NPV = -$10,000 + $3,000/ (1 + 0.1) + $3,000/ (1 + 0.1)2 + $3,000/ (1 + 0.1)3 + $3,000/ (1 + 0.1)4

NPV = -$10,000 + $2,727.27 + $2,479.34 + $2,253.95 + $2,049.04 = -$490.41

Since the NPV is negative, we need to lower our guess for the IRR. Let's try 8%. We get:

NPV = -$10,000 + $3,000/ (1 + 0.08) + $3,000/(1 + 0.08)2 + $3,000/ (1 + 0.08)3 + $3,000/ (1 + 0.08)4

NPV = -$10,000 + $2,777.78 + $2,572.02 + $2,381.50 + $2,205.09 = -$63.62

The NPV is closer to zero but still negative. We need to lower our guesses for the IRR a bit more. Let's try 7.5%. We get

NPV = -$10,000 + $3,000/ (1 + 0.075) + $3,000/ (1 + 0.075)2 + $3,000/ (1 + 0.075)3 + $3,000/ (1 + 0.075)4

NPV = -$10,000 + $2,790.70 + $2,596.00 + $2,414.88 + $2,246.40 = $47.98

The NPV is very close to zero now. The IRR of this investment is approximately 7.5%.

How To Use The IRR Function In Excel?

The IRR function in Excel stands for Internal Rate of Return. Its syntax has the following arguments: As shown in the picture below, a project needs an initial investment of $10,000 and pays a different amount yearly.

To identify whether it is profitable or not, we can use the IRR() function.

Values Required: An array or a reference to cells containing numbers for which you want to calculate the internal rate. (Source: Microsoft)

Let us give an example of IRR calculation in Excel. Imagine you have a data set showing the initial cost and net revenue of a business project for five years. To use the IRR function, follow the steps as shown below:

Spreadsheet showing that how to use IRR Function in Excel.

  • Press SHIFT + F3 together to open up the Insert Function dialog box. 

  • In the search box type “IRR” and click on Go. 

  • You will see the IRR function in the box below. 

  • Click OK. 

  • Alternatively, you can simply type =IRR()

After following these steps we should choose the arguments for the formula. As you can see, the formula needs two arguments:

  1. Values: Free Cash flows of following years,

  2. Guess (Optional): Used to optimize the process. We choose 5%

    As you can see, to calculate the IRR of this project, you can use the following formula:

=IRR(F3:K3,0.05)

Spreadsheet showing the calculation of IRR by using the formula.

This gives 0.0812 or 8.12%, which means that the project has an annual return of 8.12% based on cash flows.

The IRR function is useful to evaluate the profitability of investments and projects based on their cash flows.

Alternatively, you can test the formula without input in the “Guess” argument and check if the answer is the same. You can see in the image below that the answer is the same.

Spreadsheet showing that how to test the formula without input in the “Guess” argument and check if the answer is the same.

Pros and Cons of using IRR Function

As mentioned above, IRR allows us to compare different projects with different cash flows and durations. The higher the IRR, the more profitable the project is, but using IRR has more benefits, such as:

  1. Time value of money: The IRR considers the time value of money, which means that it accounts for the fact that money received or spent in the future is worth less than money received or spent today.
  2. Simplicity: The IRR is simple to interpret and understand. It shows the annualized rate of return that an investment generates, which can be easily compared to other investments or a required rate of return.
  3. No need for additional information: The IRR does not require a hurdle rate or a cost of capital, which can be difficult to estimate and may vary over time.  The IRR can be used to rank projects based on their merits as long as they have the same scale and duration.
    • As we said, The IRR ignores the size and scale of the project, which means that it may favor smaller projects with higher returns but lower absolute cash flows. 

The IRR also does not reflect the total value or wealth a project creates. Using IRR has more constraints to say:

  1. Future costs: The IRR ignores the future costs and risks that may affect the cash flows of a project.
  2. Uniqueness: The IRR may not be suitable or may not be unique for some projects, especially those with unconventional cash flows (such as negative cash flows followed by positive cash flows). The IRR may also give misleading results when comparing mutually exclusive projects with different initial outlays, timings, or durations.
Excel Modeling Course

Everything You Need To Master Excel Modeling

Banner that leads users to the Excel Modeling Course landing page.

Learn More

Researched and authored by Mehdi Hoghoughi | LinkedIn

Reviewed and edited by Mohammad Sharjeel Khan | Linkedin

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources: