Formula for IRR in Excel

In the world of finance, decision-making is critical. The best decisions are the ones that rely on accurate data collection and computation. The Internal Rate of Return (IRR) is a powerful metric that helps measure the potential return on an investment over time. Professionals and finance enthusiasts employ the formula for IRR in Excel to calculate IRR. Review the formula and the application of this essential financial tool. 

What is the IRR Function?

IRR stands for Internal Rate of Return and is one of Excel’s financial functions. It calculates the potential profit in the investment or project where cash flow is regular at any fixed interval. 

The IRR function determines the internal rate of return by calculating the rate at which the net present value (NPV) becomes zero. It indicates the project's financial viability. Thus, the IRR function in Excel helps in determining the percentage at which the cash inflows match the cash outflows. So, when NPV = 0, there is no gain or loss, while in the case of a positive return, NPV > 0, i.e., a profitable venture.

Become an Expert in Data Visualization

With Our Top-notch Tableau TrainingGet Started
Become an Expert in Data Visualization

IRR Formula

The syntax for the IRR formula or formula for IRR in Excel is:

“=IRR(values, [guess])”

Here, ‘values’ is the required argument and represents the numbers for calculating the internal rate of return. It includes investment and net income values. Users can also include a range of cells with values. 

‘Guess’ is the optional argument and represents the expected internal rate of return. Since it is optional, leaving it blank will take a default value of 0.1 or 10%. 

Important Points to Remember: 

  • The order of values determines the cash flows. 
  • The empty texts, cells or logical values are ignored. 
  • The argument must have investment as a negative value and returns as a positive value. 

How to use the IRR Function in Excel?

Let us take the following example to understand the IRR function in Excel. 

Alt-text: Series representing cash flows 

IRR_in_Excel_1

  • Step 1: The cash flow here is organized chronologically, and investment is written on top in negative. It is -1000. The remaining ones are returns for the specific year. 
  • Step 2: Write the formula and enter the range of cells. Here the investment is in B2 and returns for three years are in B3, B4 and B5. 

/IRR_in_Excel_2

Alt-text: Picture showing entry of IRR formula 

  • Step 3: The resultant value is 9%. 

IRR_in_Excel_3

Alt-text: Picture showing generated IRR

Interpreting the results, the answer means the user with stated investments and returns will get an IRR of approximately 9%. It further means that the investment would generate a return of around 9% profit. 

IRR vs XIRR 

The XIRR vs IRR refers to the Extended Internal Rate of Return and the Internal Rate of Return, respectively. The former considers cash flow at regular intervals, while XIRR considers them at irregular intervals or at non-periodic spans. It offers higher flexibility as it intakes entries at a specific date. The syntax for XIRR is:

“=XIRR(values, dates, [guess])”

Since this function considers irregular dates, the column specifying them is also considered. 

For instance, let us take the same previous entries with specific and irregular mentioned dates. 

Alt-text: Dates and cash flow specifying the irregular period 

Step 1: Enter the dates and cash flow. 

IRR_in_Excel_5

Alt-text: Formula of XIRR for irregular periods of cash flow 

Step 2: Enter the formula. The syntax requires the input of both values and dates in the stated order. We write that first since the cash flow is given from B2 to B5. Following it is the range of cells mentioning date from A2 to A5. 

IRR_in_Excel_6.

Alt-text: Picture showing XIRR

  • Step 3: The resultant value here is 14%

Interpreting the result, we see the value returned here is different from IRR. The change in value is owed to specific dates showing irregular periods, which alters the calculation method. Compared to IRR, XIRR is considered more accurate due to the consideration of dates. 

Learn The Latest Trends in Data Analytics!

Post Graduate Program In Data AnalyticsExplore Program
Learn The Latest Trends in Data Analytics!

IRR and NPV in Excel

NPV, or Net Present Value, indicates the value of money over time. It utilizes the investment to state the potential return amount. Further, the comparison concerning the value of money further implicates its worth. The NPV can be positive or negative, where the former is considered better, and the lower or negative NPV is considered poor, owing to the generation of less money than investment. The resultant NPV value indicates the profit, while IRR indicates the rate of return. 

The syntax for NPV is: “=NPV(discount rate, range of cash flows).” 

For instance, let us consider the previous example and put in the rate of return of 8%. The NPV is INR 16.32 here. 

Alt-text: NPV for the previous example at a discount rate of 8%

/IRR_in_Excel_7.

Learn Excel Basics for Free

Introduction to MS ExcelSTART LEARNING
Learn Excel Basics for Free

Choose the Right Program

Looking to build a career in the exciting field of data analytics? Our Data Analytics courses are designed to provide you with the skills and knowledge you need to excel in this rapidly growing industry. Our expert instructors will guide you through hands-on projects, real-world scenarios, and case studies, giving you the practical experience you need to succeed. With our courses, you'll learn to analyze data, create insightful reports, and make data-driven decisions that can help drive business success.

Program Name

Data AnalystPost Graduate Program In Data Analytics
Data Analytics Bootcamp
Available inGlobalGlobalUS
UniversitySimplilearnPurdueCaltech
Course Duration11 Months8 Months6 Months
Coding Experience RequiredNoBasicNo
Skills You Will Learn10+ skills including Python, MySQL, Tableau, NumPy and more10+ skills including
Data Analytics, Statistical Analysis using Excel, Data Analysis Python and R, and more
8+ Data Visualization with Tableau, Linear and Logistic Regression, Data Manipulation and more
Additional BenefitsApplied Learning via Capstone and 20+ industry-relevant Data Analytics projectsPurdue Alumni Association Membership
Free IIMJobs Pro-Membership of 6 months
Resume Building Assistance
Access to Integrated Practical Labs Caltech CTME Circle Membership
Cost$$$$$$$$$$
Explore ProgramExplore ProgramExplore Program

Become an Expert in Data Visualization

With Our Top-notch Tableau TrainingGet Started
Become an Expert in Data Visualization

Conclusion

Data analytics is one of the trending fields, often requiring proficiency in Microsoft Excel or Google Sheets for organization and data analysis. The professionals are also expected to be well-versed in the latest tool and statistical analysis software. Guiding you through numerous built-in functions just like the mentioned example, the Post Graduate Program in Data Analytics is offered by Purdue University and IBM experts. The course promises career mentorship along with much-needed hands-on experience. 

FAQs

1. How do you calculate IRR from NPV in Excel?

Write the syntax for IRR to find the IRR value from NPV in Excel. 

2. Can IRR be calculated manually?

The formula for manual calculation of Internal Rate of Return: 

Internal Rate of Return (IRR)=(Future valuePresent value)1Number of periods-1

3. How do I calculate IRR by month in Excel?

Use XIRR to calculate IRR by month in Excel. Ensure to put in the months. 

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More

Find Post Graduate Program in Business Analysis in these cities

Post Graduate Program in Business Analysis, Oxford
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, OPM3 and the PMI ATP seal are the registered marks of the Project Management Institute, Inc.