IRR – How To Calculate the IRR in Excel

The internal rate of return or most commonly known as the IRR is a metric utilized in capital budgeting to determine the profitability of potential ventures. It is the discount rate that turns the NPV or the net present value of all cash flows equal to zero. Therefore, the Excel calculation of the IRR heavily relies on the same formula as the NPV does. Here, we will discuss how to calculate the IRR in Excel, formula, significance, and uses as well as example templates that you can use as a reference.

How to Calculate the IRR: Formula

There isn’t really a concrete way of calculating the IRR, especially when calculating it by hand, you will have to go through countless trials and errors just to arrive at whatever rate which turns the NPV equal to zero. As mentioned above, how to calculate the IRR relies on the calculation of the NPV. Hence, we will then apply the same formula to determine the NPV equals zero.

See below to see the IRR Formula.

Where:

CFₒ = Initial investment
CF₁, CF₂, CF₃,…CFₓ = Cash Flows
x = Each period
N = Holding period
NPV = Net Present Value
IRR = Internal Rate of Return

To calculatee the IRR using the formula shown above, the value of NPV is automatically set to zero. And as you can see, due to the nature of the formula, the IRR can’t be assessed analytically, hence, you will either need to go through trial and error or make use of tools such as MS Excel which has a featured function to compute the IRR.

How to Calculate the IRR using the Formula in Excel

The Excel IRR function is one of the financial functions available in Excel that helps to determine the IRR easier compared to doing it manually. The IRR function, basically, returns the IRR for a series of cash flows that occur at regular intervals.

Syntax / Function Formula: = IRR ( values, [guess] )

Where:

Values – stands for an array or reference to cells that contain values that represent the series of cash flows and include the investment as well as the net income values. It is required that you will input or provide these data for the function to work.
Guess – an estimate for the expected IRR or the number you assumed that is closer to the expected IRR. It is, however, optional to input data here since the function can simply take a default value of 10%.

Using the IRR Function in Excel, there are two scenarios in which you can evaluate the IRR.

  1. If the expected cash flows are constant
  2. If the project has a compounded growth rate

The internal rate of return is known to be a time value of money metric which represents the annual rate of earnings of a venture, therefore, it is dependent on the cash flows of the project. To understand the logic of how to calculate IRR in Excel, refer to the examples below.

Scenario 1: Let us assume Project A has an initial investment of $100 000 and expects a constant cash flow of $25 000 for 5 years.

The first step would be to put the cash flows in a standardized format. Once the Table of cash flows is prepared, we will then use the IRR function to determine the IRR with the assumption of a 10% discount rate as shown below.

The initial investment is negative since it is considered an outgoing payment. The values after that are the cash inflows that are expected for up to 5 years of operation. By applying the formula as shown in the illustration above, calculating the IRR became easier. Though this isn’t actually perfect since it is not designed to calculate compounded growth rate cash flows, you can still calculate the Internal Rate of Return as described in the next scenario example.

Scenario 2: Assuming the initial investment is $50 000 with expected varied cash flows $15 000, $17 000, $18 000, $10 000, and $10 000 for each corresponding year.

Following the format of scenario one, you will first need to format your cash flows into a table in Excel and then apply the IRR formula for a compounded annual growth rate or also known as the CAGR.

CAGR = ( End Value / Start Value ) ^ ( 1 / Periods ) – 1

Where:

End Value – Last cash inflow
Start Value – First positive cash inflow
Periods – Number of Years

Here is what it looks like in Excel:

As you noticed, both scenarios returned with an IRR value of less than 10%. This means that the investment or project is not feasible or attractive. It is important that before deciding on investing, you must determine if the resulting IRR is greater than the expected 10% which is the normal rate for a feasible investment.

Now, see another example if we apply this to a financial model to conduct an IRR Analysis but this time with a project that is feasible and attractive to investors. Refer to the screenshot below which shows an overview of an IRR Analysis.

As shown in the example above, the IRR is equal to 27.3% and as you recall, the IRR is the discount rate used to turn the NPV equal to zero. It is important to take note that we only arrived at a resulting discount rate of 27.3% by going through iterative trial and error until the value of NPV equals zero. This can be also derived by using the goal seek function in Excel or applying the IRR formula and applying it to the range of Projected Free Cash Flows to Firm to determine the discount rate.

What is the Significance of Calculating the IRR and its Uses

To calculate the IRR is one of the most used profitability measures since it is easy to understand and easy to compare to the required return. Many companies tend to take on various projects to either increase revenues or cut down costs and with the help of calculating the IRR, they will be able to determine which projects they should invest in and which projects’ budgets will be deducted. Thus, the IRR became a metric mostly used for capital budgeting.

It is evident that to calculate the IRR is a much more useful tool when used to conduct comparative analysis. Basically, the greater the value of IRR of a project, the more desirable it is to undertake it. Due to this, it is easier to determine which projects will be feasible and will gather more earnings in the future. Of course, the IRR is also very useful to calculate when deciding to start a new project or to end a project. Though the IRR result may vary, as long as it is higher than the expected return rate then it is a worthy project to invest in.

But, it is also important to take note that this is just an assumption and not actual. There could be certain assumptions that drive the resulting value to calculate the IRR into a higher or lower percentage. Thus, this doesn’t always mean that the project will be feasible due to the timing and size of cash flows, leveraged used, and other differences in the return assumptions.

Despite the disadvantages to calculate the IRR, conducting an IRR Analysis can be used in many cases such as the following:

  • Determining the profitability of a project or investment.
  • Maximizing the Net Present Value as an indication of profitability, efficiency, quality, and yield of an investment or project.
  • Calculating the Fixed Income (calculating yield to maturity and yield to call)
  • Measuring new debt in terms of yield to maturity (used often when new debts are raised for funding new projects).
  • Evaluating share issues and stock buyback programs.
  • Measuring the General Partner’s performance (Limited Partner’s perspective).
  • Analyzing projects or investments for P&E and Venture Capital with multiple investments.
  • Determining the returns of the projects or investments for Comparison Analysis.
  • Understanding risk tolerance, investment needs, and avoiding bad/risky investments or projects.

References and Example Financial Model Templates with IRR Excel Calculation

If you want to integrate the calculation of IRR with Excel into your business plan, it is always best to present it in a complete financial model with an IRR Analysis section. Though this can be a time-consuming task as well as requires you to be skilled in financial modeling, this doesn’t mean that you can’t do it yourself. Try using available tools that are in Excel instead such as financial model templates with IRR Analysis.

Not only are the templates can be used as a starting base for your own financial model but they can also be used as a reference if you plan to build a financial model in the future that needs an IRR calculation. These templates are also designed by expert financial modelers with the right know-how and experience in building financial models for different kinds of users such as business executives, CFOs, entrepreneurs, and many other entities from countries such as the USA, Germany, Australia, Canada, Japan, and many more that are in need of assistance when it comes to financial modeling.

If you are looking for financial model templates with IRR Analysis, simply check out the list below:

Feel free to read the article about the differences between IRR and NPV in the article: IRR vs NPV.

Was this helpful?