Don’t forget to share this post

The internal rate of return (IRR) is a concept in financial modeling commonly used in combination with net present value (NPV). The formula in Excel is used to estimate the rate of return an investment would give based on a series of cash flows, usually presented as a percentage (‘3%’). The NPV, on the other hand, is expressed as a cardinal number (‘3’). Both play a key role in making investment decisions when presented with more than one opportunity, as we will show here.

This article will provide step-by-step guidance on how to use the IRR formula to effectively calculate the IRR for investment decisions.

How to use the IRR function in Excel?

We will first explain how to use the IRR function in Excel to calculate the rate of annual return for an initial investment. If you need to calculate return rates for different times periods or a future value, Excel also offers a variety of financial functions, i.e. XIRR, or MIRR.

What is the formula for calculating IRR?

The syntax for the IRR formula is:

=IRR(values,[guess])
  • values: This argument is required. It refers to the cash flows, the initial investment (as a negative figure), and net income (as a positive figure).

IMPORTANT: It is important that the initial investment and cash flows maintain the same sequence in the IRR formula will interpret in this very order. Moreover, for the IRR function to work properly, the argument should be a number. If it is a logical value, text, or an empty cell, it will be ignored.

  • [guess]: This argument is optional. It is an estimation of the number closest to the expected IRR (as there can be two). If not included, the argument will show the default value of 0.1 (=10%).

Let’s now apply the IRR formula, to the following scenario:

You are a business owner and you are presented with two investment opportunities, A and B. The first has an initial investment of $2,500,000.00, whereas the second has one of €2,800,000.00. The net income for each is presented on a yearly basis. The IRR function can help you get an idea of what the return rate would be based on the initial investment and the yearly cash inflows:

  1. 1. Option A has an initial investment of $2,500,000.00 with the following yearly cash flows.
How do I calculate IRR in Excel Option A
  1. 2. Type in ‘IRR’ in the cell you wish to showcase the IRR value.
How do I calculate IRR in Excel IRR Formula
  1. 3. As soon as you select from the drop-down menu, you will automatically be prompted to select the cell range that you wish to perform the IRR calculation on.
How do I calculate IRR in Excel Select cell range
  1. 4. Once you press the Return key you will be shown the IRR percentage. Remember that IRR is represented as a percentage, whereas NPV is a cardinal number. Here, we get a return of 5%.
How do I calculate IRR in Excel Press enter
  1. 5. Now, let’s calculate the IRR for Option B with an initial investment of $2,800,000.00 and the following yearly cash flows.
How do I calculate IRR in Excel Investment B
  1. 6. We would input the formula in the same way we did previously in steps 2-3.
How do I calculate IRR in Excel Investment B IRR
  1. 7. Here, we get an IRR of 8%.
How do I calculate IRR in Excel Investment B IRR Result

The information obtained so far would help us decide solely based on the IRR; it is common practice to go for the higher percentage. Considering that Option A provides an IRR of 5% and B of 8%, we would opt for investing in project B. However, if the cash flows were on a monthly basis, our decision-making could be more precise. By following these steps, you can easily learn how to calculate IRR in Excel for monthly cash flow.

How to Share an Excel File for Multiple Users
How to Share an Excel File for Multiple Users?

There are different ways to share an Excel file. Here's how to share an Excel file with multiple users for easy collaboration

READ MORE

How to calculate IRR using NPV in Excel?

As mentioned in the Introduction, the NPV is expressed as a cardinal number, as opposed to the IRR which is expressed as a percentage. It is used to calculate the difference between current cash inflow and outflow over a period of time. Make note that the “rate” referred to in the NPV formula is not the same as the rate obtained using the IRR formula shown previously:

=NPV(rate, values)
  • rate: refers to the discount rate for the investment period. This discount rate is a generic term used to refer to any rate that is established, subjectively or objectively, to discount future cash flows with. Moreover, for more reliable decision-making, we should always apply a lower and a higher discount rate for all investment options.
  • values: refer to the initial investment (represented as a negative value) and the cash inflows over the given time periods.

Let’s say that we apply a 5% discount rate (this could represent the lower rate) to both investment opportunities.

  1. 1. Type in ‘NPV’ in the cell you want to obtain the NPV formula.
How to calculate IRR using NPV in Excel Enter NPV
  1. 2. As the first parameter corresponds to the discount rate, we first need to add cell B13 followed by the sum of cash flows, and finally add all to the initial investment.
How to calculate IRR using NPV in Excel NPV Full Formula
  1. 3. Press the Return key to obtain the NPV value ($50,196.47).
How to calculate IRR using NPV in Excel NPV A
  1. 4. To calculate the NPV for investment option B, follow steps 1-3.
How to calculate IRR using NPV in Excel NPV result B

As mentioned before, we would now need to calculate the NPV for a higher discount rate (8%) for both investment opportunities. Once again, let’s start with option A.

  1. 5. We have included a second row where we will calculate the NPV applied to the same values as before, but this time include cell B15 as the discount rate.
How to calculate IRR using NPV in Excel Higher discount rate
  1. 6. Proceed to apply values to the NPV formula and press the “return” key to obtain the result:
How to calculate IRR using NPV in Excel NPV for Higher DR
  1. 7. For investment option B, we have followed steps 6 and 7 and obtained the following:
How to calculate IRR using NPV in Excel Press return key

Based on the obtained results, we decide on Option B. Why? Because both the NPV and IRR are higher in both discount rate scenarios:

How to calculate IRR using NPV in Excel Best option

The IRR formula can only be used in instances where the cash flow distribution occurs over the same time periods (yearly, monthly, etc.). If you want to calculate the rate of return for different time periods, use the XIRR formula in Excel. This selects the cash flow range and then the range of dates on which the cash flows are made.

How to Use GOOGLEFINANCE Function in Google Sheets?

Import current or historical financial market data from Google Finance & monitor real-time. Here's how to use the GOOGLEFINANCE function in Google Sheets

READ MORE
How to Use GOOGLEFINANCE Function in Google Sheets

How do you calculate IRR manually?

If you are advanced in math or aren’t interested in calculating IRR in Excel, this is what you would need to do to calculate IRR manually:

  1. 1. Estimate two discount rate values (i.e., 4% and 8%) to try and set the NPV to zero.
  2. 2. Calculate the NPVs using 4% and 8% and try to get as close to zero as you can.
  3. 3. Once you determine your two discount rate values and the two NPVs, you can use this formula to calculate the IRR:
IRR = R1 + ( (NPV1 * (R2 - R1)) / (NPV1 - NPV2) )
  1. 4. R1 corresponds to the lower discount rate (4%) and R2 is the higher one (8%). NPV1, on the other hand, represents the higher NPV, and NPV2 the lower.

How to manage spreadsheet data with Layer?

Layer adds productivity features to your Google Sheets. Share parts of your spreadsheet, request input, and accept or reject changes to make collaboration seamless and more efficient while keeping full control over your data.

Using Layer, you can:

  • Manage Access: Give spreadsheet access to relevant stakeholders on a tab or cell level.
  • Review & Track: Consolidate input, and easily track changes.
  • Collaborate: Define, assign, and automate tasks and set deadlines.

Sign up for early access and start automating your Google Sheets workflows with Layer!

Conclusion

This article has explained the concepts of IRR and NPV, followed by a step-by-step process showing how to calculate IRR in Excel, how to use the IRR function in Excel in combination with NPV function, and finally broken down the use of IRR by explaining how to calculate IRR manually.

Whether you wish to explore further on financial functions or apply the functions seen in this post to real-time investments, you will find the article on How to use the GOOGLEFINANCE Function in Google Sheets very useful.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 26 2022, Updated Sep 23 2022