How to Calculate IRR in Excel? 3 Important Functions

• Facebook
• Twitter
• YouTube

(Note: This guide on how to calculate IRR in Excel is suitable for all Excel versions including Office 365

Microsoft Excel is a very efficient data handling and management tool. When you have to store calculations and data related to income and expenditure from an individual to a business standpoint, MS Excel is the way to go. The built-in functions it houses help perform a variety of financial-related calculations with ease. 

Among the financial operations that can be performed in Excel, one particular function is the IRR function.

In this article, you will learn how to calculate IRR in Excel using the IRR function.

You’ll Learn:

• What Is the Internal Rate of Return (IRR)?
• What Is the IRR Function in Excel?
• Syntax
• How to Calculate IRR in Excel?
  • Including the Guess parameter 
• Additional Functions
  • MIRR Function in Excel
    • Syntax
    • How to Calculate MIRR in Excel?
  • XIRR Function in Excel
    • Syntax
    • How to Calculate XIRR in Excel?

Related Reads:

How to Calculate Time in Excel? 2 Distinct Types

How to Calculate Correlation Coefficient in Excel? 2 Useful Ways

How to Calculate Standard Deviation in Excel? 2 Simple Ways

What Is the Internal Rate of Return (IRR)?

Before we learn how to calculate IRR in Excel, let us see what IRR is first.

IRR stands for Internal Rate of Return. This value is particularly used to know the net value of the cash flow when investing based on the returns (in terms of percentage). 

To understand the benefit of IRR in real-time, consider an example. You are an investor and are asked to choose between two companies to invest in. In such a case, you can use the IRR function to calculate the internal rate of return of both companies and invest in the company which has a positive and greater IRR. This means that the greater the IRR, the greater the yield will be compared to the initial investment, which in turn results in increased profits. 

What Is the IRR Function in Excel?

You can use the IRR function to calculate the internal rate of return in Excel. However, there are some criteria to take into account when using the function. 

While calculating IRR in Excel, the cash flow values can or cannot be even, but must occur at regular time intervals. 

The basic format of data when calculating the IRR is that the initial investment should be a negative value and the income at regular intervals should be a positive value. To calculate the IRR in Excel, the data must contain at least one negative value. Else, Excel throws a #NUM! error.

And more importantly, the IRR function calculates the IRR by analyzing the flow of data. So,  any change in the order of data affects the IRR. 

This function is used with the NPV function while selecting investments depending on the returns. While the IRR function returns the percentage of interest gained/lost over some time, the NPV gives you the current value of an investment by using the interest rate and the series of cash inflow/outflow. 

Syntax

The syntax to calculate the IRR is:

=IRR(values, [guess])

Where, 

• The values argument is a mandatory value. This denotes the array or a range of cells that contain the income values to calculate the internal rate of return. The values argument only takes numerical values, so make sure the cells are formatted as “Numbers”. 

• The guess argument is an optional value and should be formatted as a Percentage or Decimal. You can guess and enter a value you think is close to the IRR. If you don’t specify any value, the guess value is automatically assumed to be 0.1 or 10%. 

How to Calculate IRR in Excel?

Let us now get to calculating the IRR of the investment after 5 years. 

• To calculate the IRR, first, choose a destination cell. 

• Enter the formula =IRR and choose the range of cells.

• In this case, we want to calculate the IRR for the first 5 years, so we have selected the range B4 to B9. Since the guess argument is optional, let us leave it empty for now. 

• Press the Enter key.

• This gives you the internal rate of return at the end of the fifth year. 

That is, the IRR value after the fifth year is 22%.

Including the Guess parameter 

In the above example, we saw how to calculate IRR in Excel without specifying any value for the guess parameter.

Let us now see how the result varies when you include the guess argument. 

• In the same syntax, add the guess parameter after the comma and press the Enter key. 

• This gives you the IRR after the seventh year (33%).

Note: Adding the guess parameter next to the value parameter does not have much of a significant value even when you omit the parameter. In case you get a #NUM error, enter or change the value for the guess parameter. 

Additional Functions

From the above explanation, we saw how to calculate IRR in Excel. In addition to IRR, Excel has another two functions to calculate the internal rate of return called the Modified Internal Rate of Return (MIRR) and Internal Rate of Return with Time Periods (XIRR).

Both functions are similar to the IRR function. The MIRR function deals with interest percentages including borrowed and invested value. The XIRR function calculates the IRR accurately since it takes the time of each cash flow into account. 

MIRR Function in Excel

The MIRR function in Excel is used to calculate the internal rate of return taking both the investment and the interest received on reinvestment. 

Syntax

=MIRR(values, finance_rate, reinvest_rate)

Where, 

• values represent the range of cells or an array that contains the expenditure or income and is a mandatory field. These values must contain at least one negative value (investment) and the rest are positive values (income). These values can only take numbers and also should be formatted as “Number”.

• finance_rate is a mandatory field that denotes the interest rate in cash flow.

• reinvest_rate is also a mandatory field that denotes the interest you receive as you reinvest them. 

How to Calculate MIRR in Excel?

Consider an example, where you have details regarding the initial setup to begin a business and the income that it makes for 10 years. You have initiated a loan for the initial investment and also have reinvested the profits at a certain rate.

Let us now calculate the MIRR after six years. 

• Choose a destination cell and enter the formula =MIRR( in the cell.

• Now, pass the arguments. Enter the cell range in the place of values, and then enter the parameters for the finance rate and reinvest rate. You can either choose the cell which contains the values or you can directly enter the percentage value for the arguments. 

• Press Enter.

This gives you the MIRR value after the sixth year taking the investment interest and reinvestment interest into account. 

Suggested Reads:

How to Calculate SUBTOTAL in Excel? 2 Useful Methods

How to Calculate Percentile in Excel? 3 Useful Formulas

How to Calculate Factorial in Excel? Along with 2 Easy Examples

XIRR Function in Excel

The XIRR function in Excel returns the initial rate of return of the cash values even if they are not periodic and do not need to have a common time difference. This is the main difference between the IRR function and the XIRR function.

Syntax

The syntax of the XIRR function is similar to that of the IRR function. 

=XIRR(values, dates, [guess])

Where, 

• The values argument is a mandatory field and contains the cash flow values which will be used to arrive at IRR. The investments are marked by a negative symbol whereas the benefits are marked by a positive value. The values argument should be formatted as “Numbers” and must contain one negative value and one positive value. 

• The dates argument is also a mandatory field and this denotes the corresponding dates to the income values. It is always better to use the DATE function when entering dates or the dates will be formatted as text. To enter the dates, use the format DATE(2021,4,15) for the 15th of April, 2021. More importantly, the dates should be sequentially numbered. When the corresponding dates are not sequential, the function throws a #NUM! error. 

• The guess argument is optional. This lets you guess the value you think is close to the IRR. If you leave this argument empty, the value 0.1 (or 10%) is assumed by default. 

How to Calculate XIRR in Excel?

Calculating the XIRR in Excel is similar to calculating the IRR and MIRR. 

• To use the XIRR function, select the destination cell. 

• Enter the formula =XIRR in the destination cell and pass the arguments. Enter the range of cells that houses the cash flow values, the corresponding dates, and the guess value. 

This gives you the corresponding XIRR value after ten months even though the values are not periodic. 

Also Read:

How to Convert Excel to CSV File? 2 Different Approaches

How to Insert PDF in Excel? 5 Easy Ways

How to Convert Word to Excel? 3 Easy Ways

Frequently Asked Questions

Closing Thoughts

IRR, MIRR, and XIRR all help in calculating the internal rate of return which can prove to be very beneficial when making crucial investment decisions. 

In this article, we saw how to calculate IRR in Excel. We also saw how to use the MIRR and XIRR functions to calculate the internal rate of return based on different conditions. Use the function depending on the criteria. 

Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.

Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.

Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. 

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!