EDUCBA

Home Excel Excel Resources Excel Tips Excel IRR Formula

Excel IRR Formula

By Arun GuptaArun Gupta

Excel IRR Formula

Excel IRR Formula (Table of Contents)

Excel IRR Formula

MS Excel provides several inbuilt functions, and one of them is the IRR function, which returns the internal rate of the return for given cash flow data. This cash flow data should be on a regular basis like monthly, yearly, etc.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

It is one of the Financial function members used to calculate the interest rate of the investment (in negative value) and the generated revenue (as positive).

Syntax of the IRR Function:

IRR () – It will return an internal rate of the return depending on the output from the given parameter. It will return the value in the percentage term.

Syntax IRR Formula

The Argument in the Function:

  • Values: It is a mandatory field, which a user wants to calculate the internal rate of the return. Initially, all investment will be in negative, and all generated revenue will be in positive.
  • Guess: It is an optional field; it is provided by the user, which he thinks is closer to the internal rate of return. If a user is not providing any value, then the function will take the default value, which is 0.1 or 10%.

How to Use Excel IRR Formula?

Excel IRR Formula is very simple and easy. Let’s understand how to use the Excel IRR Formula with some examples.

You can download this IRR Formula Excel Template here – IRR Formula Excel Template

Example 1

A user has some investment and income generation details from a company where he wants to calculate the internal rate of the return. The company owner invested one lakh as the initial investment on top of that cash flow generated by the company given in the table. So, a user wants to calculate the internal rate of the return of the company for the given 5-year data.

Let’s see how the ‘IRR’ function can solve his problem to calculate the company’s internal rate for the given 5-year data.

  • Open MS Excel, go to sheet1 where the user wants to calculate the company’s internal rate for the given 5-years data in the table.

IRR formula in excel example 1-1

  •  Create a header column with IRR Output in Column C, in which we will function the result.

IRR formula in excel example 1-2

  • Merge cell from B2 to B7. Now apply the IRR function in cell C2.

IRR formula in excel example 1-3

  • Now select the value from the B2 cell to B7.

IRR formula in excel example 1-4

  • Click on the Enter key. The result will be shown to the user in the percentage term.

IRR formula in excel example 1-5

Summary of Example 1:

As the user wants to Calculate the Internal Rate of the Return for a company, where 5-year investment and income details were given. So, as a result, we can see the result is 12%. In 5-year, time frame 12 % is the rate of the return for the company.

Example 2

A user has some investment and income generation details from a company where he wants to calculate the internal rate of the return after 3 years and 7 years of the initial amount invested. The company owner invested 1.5 lakh as the initial investment because whatever cash flow is generated by the company given in the table. So, a user wants to calculate the company’s internal rate after 3 years and 7 years of the initial amount invested (2012-2019).

Let’s see how the ‘IRR’ function can solve this problem to calculate the company’s internal rate for the given 7-year data.

  • Open MS Excel, go to the sheet where the user wants to calculate the Internal Rate of the returned of the company for the given 5-years data in the table.

irr example 2-1

  • Create a Header Column with IRR Output in the E column which we will function result.

irr example 2-2

  • Now apply IRR Formula in the cell E2 for a 3-year calculation.

irr example 2-3

  • Now select the value from cell C2 to C5.

irr example 2-4

  • After applying this Formula, the IRR Output is given as below.

irr example 2-5

  • Now apply the IRR formula in the cell E6 for a 7-year calculation.

irr example 2-6

  • Now select the value from the C2 cell to C9.

irr example 2-7

  • Click on the Enter key. The result will be shown to the user in the percentage term.

irr example 2-8

Summary of Example 2:

As the user wants to calculate the internal rate of the return for a company after 3 years and 7 years of initial 1.5 lakh investment, where 7-years investment and income details were given.

So, we can see the result -25%, which is the initial rate of the return in a 3-year time frame, and 11 % is the initial rate of the return in 7 years for the company.

Things to Remember About Excel IRR Formula

  • The value Parameter in the function is a mandatory field, which a user wants to calculate the internal rate of the return.
  • Initially, all investments will be negative, and all generated revenue will be in positive.
  • If a user does not provide any value for Guess, then the function will take the default value, which is 0.1 or 10%. IRR function will try max 20s time if after that also it is not finding then it will return a #NUM error. Then a user can try with some different number.
  • The IRR” function will return the value in the percentage term.
  • If a user provides any text, blank or logical, the function will ignore the value.
  • All values provided in the function should be in sequential order in time.

Recommended Articles

This has been a guide to Excel IRR Formula. Here we discussed How to use Excel IRR Formula along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Excel ISNUMBER Formula
  2. LEN Formula in Excel
  3. SUMIF Formula in Excel
  4. Sort by Date in Excel