Get help

Help center

Guides and answers

Contact support

Write to our team

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Finance

Turn manual finance work into reports that update themselves.

What Finance teams automate

  • Track payables and receivables
  • Build cash flow statements
  • Compare budget against actuals

Inventory and manufacturing

See stock levels and automate ordering.

What inventory and manufacturing teams automate

  • Track inventory across multiple sites
  • Connect sales, purchasing, and warehouse data
  • Link a barcode scanner to your sheet

HR

Run recruitment, attendance, and OKR tracking from your sheets.

What HR teams automate

  • Track recruitment and onboarding
  • Record team OKRs and results
  • Track check-ins, check-outs, and leave

Project management

Assign tasks and track hours without manual updates.

What project teams automate

  • Assign tasks and track status changes
  • Estimate effort and log hours worked
  • Track milestones across projects

Sales and CRM

Track leads and sales performance with automated reports.

What sales teams automate

  • Track and convert leads
  • Set sales goals and track team metrics
  • Monitor sales KPIs on dashboards

Education

Automate attendance, grades, and school purchasing.

What schools automate

  • Track attendance and notify parents
  • Record and compare class grades
  • Track inventory lent to students and staff

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Get started

Templates

Ready-made workflows to start from

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Help center

Guides and answers

Contact support

Write to our team

Workflows

Build processes with your team

Automations

Connect files and automate tasks

How to calculate IRR in Excel? (IRR function and formula)

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. If you work in Google Sheets instead, see how to use the IRR formula in Google Sheets.

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 time periods or a future value, Excel also offers a variety of financial functions, such as 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, as the IRR formula will interpret them 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. Option A has an initial investment of $2,500,000.00 with the following yearly cash flows.

IRR formula Excel — initial investments of -$2,500,000 for A and -$2,800,000 for B with yearly income for years 1 to 10

2. Type in ‘IRR’ in the cell you wish to showcase the IRR value.

IRR formula Excel — =IRR typed in cell B12 with a functions drop-down listing IRR, MIRR, and XIRR

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.

IRR formula Excel — =IRR(B1:B11) entered in cell B12 with the option A cash flows selected

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%.

IRR formula Excel — cell B12 returns an IRR of 5% for option A

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.

IRR formula Excel — option B column with -$2,800,000 initial investment and $425,000 yearly income, IRR cell D12 empty

6. We would input the formula in the same way we did previously in steps 2-3.

IRR formula Excel — =IRR(D1:D11) entered in cell D12 with the option B cash flows selected

7. Here, we get an IRR of 8%.

IRR formula Excel — IRR row showing 5% for option A and 8% for option B

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 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:

  • 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. Type in ‘NPV’ in the cell you want to obtain the NPV formula.

IRR in Excel with NPV — =NPV typed in cell B14 below a 5% lower discount rate, with NPV and XNPV in the drop-down

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.

IRR in Excel with NPV — =NPV(B13,B2:B11)+B1 entered for option A

3. Press the Return key to obtain the NPV value ($50,196.47).

IRR in Excel with NPV — option A NPV of $50,196.47 at the 5% lower discount rate

4. To calculate the NPV for investment option B, follow steps 1-3.

IRR in Excel with NPV — option B NPV of $481,737.34 next to option A's $50,196.47 at 5%

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.

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.

IRR in Excel with NPV — =NPV(B15 typed in cell B16 after an 8% higher discount rate row

6. Proceed to apply values to the NPV formula and press the “return” key to obtain the result:

IRR in Excel with NPV — option A NPV of -$322,993.22 at the 8% higher discount rate

7. For investment option B, we have followed steps 5 and 6 and obtained the following:

IRR in Excel with NPV — option B NPV of $51,784.59 at 8% next to option A's -$322,993.22

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

IRR in Excel with NPV — option B's IRR and NPV cells highlighted green and option A's red at both discount rates

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 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. Estimate two discount rate values (i.e., 4% and 8%) to try and set the NPV to zero.

2. Calculate the NPVs using 4% and 8% and try to get as close to zero as you can.

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) )

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.

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.

To run the same check in Google Sheets, read how to use the NPV formula in Google Sheets. For a full valuation built on discounted cash flows, start from our DCF template in Google Sheets. If your cash flow figures live in several spreadsheets, Sheetgo’s AI finance virtual employee can pull them into one up-to-date file.

You may also like…

Excel INDIRECT function — Excel logo and fx icon above =INDIRECT on a light gray background

Excel INDIRECT function: formula and examples

Use the INDIRECT function Excel provides to turn text into a cell reference. See the syntax and examples with SUM, AVERAGE, ROW, and other sheets.

Excel GROWTH function — Excel logo and fx icon with =GROWTH on a yellow background

Excel GROWTH function: how to use and examples

The Excel GROWTH function returns the predicted exponential growth for your data set. Here’s how to use the GROWTH function step by step, with examples.

How to round off numbers in Excel — Excel icon next to .00 rounded to .0

How to round off numbers in Excel with the ROUND function

Learn to round off numbers in Excel with ROUND, ROUNDUP, and ROUNDDOWN, to any number of decimal places or to the nearest whole number.