EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Financial Functions in Excel IRR Excel Function
 

IRR Excel Function

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 2, 2023

IRR function in excel

 

 

IRR Function in Excel (Table of Contents)

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • Explanation of IRR Function in Excel
  • How to Use the IRR Function in Excel?
  • ERROR HANDLING

IRR Function in Excel

IRR stands for Internal Rate of Return, which is categorized under the Financial section of Insert Function in Excel. In any investment, IRR calculates what would be the rate of return for a series of cash flow whether that cash flow is inward or outward. To understand better, if we receive some money in 10 different transactions in any or different amounts, then with the help of IRR we can find what would be the internal rate of interest applicable on received amount in multiple transactions.

IRR Formula in Excel:

Below is the IRR Formula in Excel :

IRR formula in excel

Explanation of IRR Function in Excel

IRR Function in Excel consists of two things one is VALUES & GUESS.

  • VALUES: This is a targeted range of your cash outflows and inflows. The range consists of initial investment and a series of cash inflows (it could be positive or negative).
  • GUESS: It is a guessed number by the user, close to the anticipated nearest return. If you ignore this optional argument, the function will take the default value as 0.1, i.e. 10%. If at all IRR gives the #NUM error result, you need to change the value for ages.
Pro Tip: If you look into the second syntax, GUESS is included in parenthesis [], which means that argument is not mandatory for this formula.

Example: You can get 15% PA interest on your $2,000. So $2,000 earns $300 in one year ($2,000*15% = $300). Your $2,000 is worth $2,300 in one year. Simply put, $2,300 next year is worth only $2,000 now, or the present value of $2,300 next year is $2,000.

IRR Example 1

How to Use the IRR Function in Excel?

IRR Function in Excel is very simple and easy to use. Let us understand the working of the IRR Function in Excel by some IRR Formula examples. IRR function can be used as a worksheet function.

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

Example #1

In this IRR Function in Excel example, let us consider the below table for our illustration; Mr. X started his project with an initial investment of Rs. 10,00,000/- and the First 5 years cash inflow is 1 lakh, 2.5 Lakhs, 3 Lakhs, 3.5 Lakhs, and 2 Lakhs respectively. So now, calculate IRR for this investment made by Mr. X.

Example 1-1

For 1st year’s IRR is -90%. That means Mr. X has recovered 10% of his investment by the end of the first year.

IRR Example 1-2

So the result would be :

IRR Example 1-3

For 2nd year’s IRR is -45%. That means Mr. X has recovered 35% of his investment by the end of the second year. While applying the formula, you must select Initial Investment + First Year Inflow + Second Year Inflow.

IRR Example 1-4

Result is :

IRR Example 1-5

For 3rd year’s IRR is -17%. That means Mr. X has recovered 83% of his investment by the end of the third year. While applying the formula, you must select Initial Investment + First Year Inflow + Second Year Inflow + Third Year Inflow.

IRR Example 1-6

Result is :

IRR Example 1-7

For the 4th year, while applying the formula, you must select Initial Investment + First Year Inflow + Second Year Inflow + Third Year Inflow + Fourth Year. That will give you an IRR of 0.00%. So, Mr. X recovered his entire investment by the end of the 4th year.

IRR Example 1-8

So the result will be :

IRR Example 1-9

For a 5th year, while applying the formula, you must select Initial Investment + First Year Inflow + Second Year Inflow + Third Year Inflow + Fourth Year + Fifth Year Inflow. That will give you an IRR of 6%. So Mr. X has gained 6% ROI from this project.

IRR Example 1-10

The result will be:

IRR Example 1-11

MR x will decide to gain 6% from this project for 5 years. Is it worth an investment? Of course, it is not worth it, so Mr. X can now look for other portfolios to invest his money to make a better IRR.

Example #2

In this IRR Function in Excel example, Mr. Karanth has an investment of 1 lakh USD today, and he wants to invest in one of the projects below table is the information on Outflows and Inflows.

Example 2-1

If you look at the table, the investment is for 7 years, the initial outflow is $1,00,000, and in the 3rd year, he introduced an additional capital of $5,000. Now calculate the Internal Rate of Return from this investment.

Example 2-2

IRR from this investment is 10.75%.

Example 2-3

ERROR HANDLING

Below are certain things you need to look into before applying the IRR formula in Excel.

  • If the initial outflow value is not shown as negative, Excel will recognize it, as there is no investment for this project.

IRR Example 3-1

We will get a #NUM error.

#NUM error Example 3-2

  • If all the values are negative, Excel thinks this project has no inflow or income.

IRR Example 3-3

We will get a #NUM error.

#NUM error Example 3-4

Relevance and Uses

IRR function is a measure used in financial modeling to estimate the profitability of the investment. In simple terms, it is the rate of return on your investment. You can think of IRR as the growth a project is expected to generate.

IRR function is a financial function and is very useful in financial modeling. Corporate people often use it to compare and decide between capital projects.

Things to Remember

  • So to get an accurate result range, numbers must have a minimum of one negative and one positive value.
  • For syntax [GUESS], Excel will treat it as 0.1, equal to 10%, if the user does not provide the value by default.
  • IRR is always dependent on Net Present Value (NPV). A rate of return given by the IRR function is the return rate equivalent to a zero NPV.
  • It does not always equal the annual compound rate of return on an initial investment.

Recommended Articles

This has been a guide to IRR in Excel. Here we discuss the IRR Formula in Excel, how to use IRR Function in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. AND Function in Excel
  2. MIRR Formula in Excel
  3. XIRR in Excel
  4. MIRR Excel Function

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download IRR Function Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download IRR Function Excel Template

EDUCBA

डाउनलोड IRR Function Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW