How to Figure out Internal Rate of Return in Excel

Google
1

This guide explains how to calculate the internal rate of return, or IRR, for projects using Microsoft Excel.

The internal rate of return (IRR) is a crucial financial metric for evaluating the profitability of investments. It’s commonly used in project appraisals, risk assessment, and investment decision-making.

The IRR is compared against a hurdle rate, representing the minimum acceptable return for an investment. If a project’s IRR exceeds the hurdle rate, it’s generally considered a worthwhile investment.

Excel has a built-in IRR function to calculate the internal rate of return. This guide provides a step-by-step tutorial on using this function.

Understanding the IRR Function

The syntax of the IRR function is:

=IRR(values, [guess])

Let’s examine each argument:

  • IRR(): This is the function that calculates the internal rate of return for a series of cash flows. It represents the interest rate earned on an investment with regularly timed payments and income.

  • values: This refers to the array or range of cells containing the cash flows for which you want to calculate the IRR. This range must include at least one positive and one negative value.

  • guess: This optional argument is a number close to the expected IRR. Excel uses this guess as a starting point for an iterative calculation. In most cases, you can omit this argument, as it defaults to 0.1.

  • Each value in the values range represents either income (positive) or payments (negative) from the investor’s perspective.

Calculating IRR with the IRR Function in Excel

The IRR function determines the internal rate of return by finding the discount rate that makes the net present value of all cash flows equal to zero. This represents the break-even rate for the investment.

Example

Let’s calculate the IRR for a sample project.

Suppose you have a table of cash inflows and outflows, including the initial project cost and the net income over five years. You want to determine if the project meets your required rate of return of 10%. This is most effectively addressed using Excel’s built-in IRR function.

You can use the following formula:

=IRR(B2:B7)

If the IRR function returns a value of 8%, this is lower than your required rate of return, and you might want to explore other investment opportunities.

Calculating IRR in Excel

Step 1: Input the positive and negative cash flows of your investment.

For example, list the costs and revenues associated with the investment.

Step 2: Enter the IRR function in a cell, specifying the range containing the cash flows.

Step 3: Press Enter to calculate the IRR.

Excel displays the internal rate of return for your investment.

This guide has provided the steps to calculate the internal rate of return for a project using Excel. Using the IRR function simplifies financial analysis and investment decisions.