How to Master the IRR Formula in Excel: The Complete Guide

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

IRR Formula

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

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 

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

Alt-text: Picture showing entry of IRR formula 

• Step 3: The resultant value is 9%. 

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:

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. 

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. 

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

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%

Learn Excel Basics for Free

Introduction to MS ExcelSTART LEARNING

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 Analyst	Post Graduate Program In Data Analytics	Data Analytics Bootcamp
Available in	Global	Global	US
University	Simplilearn	Purdue	Caltech
Course Duration	11 Months	8 Months	6 Months
Coding Experience Required	No	Basic	No
Skills You Will Learn	10+ skills including Python, MySQL, Tableau, NumPy and more	10+ 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 Benefits	Applied Learning via Capstone and 20+ industry-relevant Data Analytics projects	Purdue Alumni Association Membership Free IIMJobs Pro-Membership of 6 months Resume Building Assistance	Access to Integrated Practical Labs Caltech CTME Circle Membership
Cost	$$	$$$$	$$$$
	Explore Program	Explore Program	Explore Program

Become an Expert in Data Visualization

With Our Top-notch Tableau TrainingGet Started

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.