FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Calculate IRR in Excel

Updated on December 26, 2023
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

IRR Calculation in Excel

IRR, or Internal Rate of Return, is used to calculate the internal profit for some investment in financials. IRR is an inbuilt function in Excel used to calculate the same. It is also a financial formula. This function takes a range of values as an input for which we need to calculate the internal rate of return and a guess value as the second input.

For example, suppose your company made an initial investment of $50,000 and expects to generate  $10,000 yearly for the next 10 years. We can use this data to calculate the IRR of this investment which is the rate of return you may get on your investment of $100. As a result, the IRR comes out to be 15%. It is equivalent to money invested at 15% for 10 years.

IRRIRRInternal rate of return (IRR) is the discount rate that sets the net present value of all future cash flow from a project to zero. It compares and selects the best project, wherein a project with an IRR over and above the minimum acceptable return (hurdle rate) is selected.read more in Excel stands for Internal Rate of Return. It is one of the built-in functions available in Microsoft Excel. In addition, it falls under the category of financial functions.

The Internal Rate of Return is the rate of interest received for the investment made. It consists of the payments made represented by the negative values and the income generated denoted by positive values that occur regularly.

IRR function in Excel returns the internal rate of return for a series of cash flowsCash FlowsCash Flow is the amount of cash or cash equivalent generated & consumed by a Company over a given period. It proves to be a prerequisite for analyzing the business’s strength, profitability, & scope for betterment. read more represented by the positive and negative numbers. These cash flows do not have to be consistent as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly, quarterly, or annually. The cash amount can vary for each interval.

IRR-in-Function-in-Excel

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Calculate IRR in Excel (wallstreetmojo.com)

The Formula used to Calculate IRR in Excel

The IRR formula in Excel is as follows:

IRR formula

Where,

  • range = This is a required parameter. A range of cells denotes the series of cash flows for which we would calculate the internal rate of returns.
  • guess = This is an optional parameter. It denotes a number that you guess is close to the internal rate of return result. If no value is mentioned, it takes the default value as 0.1 or 10%.
  • “[ ]” denotes optional parameters.

The function returns a numeric value. It could be positive or negative.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

IRR in Excel Video

How to Calculate IRR in Excel? (with Examples)

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


We can do the IRR calculations in Excel in a Worksheet (WS) function or a VBA function. It can be entered as a part of the formula in a worksheet cell as a WS function. On the other hand, it can be used in macro code as a VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more, entered through the Microsoft Visual Basic Editor integrated into MS Excel. Refer to the IRR calculation in the Excel example below to better understand.

Example #1 – IRR Calculation in Excel Worksheet

IRR Example 1

In this IRR example calculation, -3,000 denotes the capital amount invested. The following numbers, 2,000 and 5,000, indicate income. The IRR function in Excel is calculated on values -3,000, 2,000, and 5,000. The result is 67%.

Example #2

IRR Example 2

In this IRR example calculation, #NUM! It is displayed as a result because no single negative value is present in the set of values. So, there is no value indicating payment, but all are income.

Example #3

IRR Example 3

In this IRR example calculation, all the values are negative. Therefore, it indicates it generated no income. Hence, the result generated is #NUM!

Example #4

IRR Example 4

In this IRR example calculation, the total values are five. -10,000 denotes the payment done. The following values are per year, i.e., income earned in Year 1 is 2,000, year 2 is 5,000, year 3 is 4,000, and Year 5 is 6,000.

To calculate the income generated at the end of three years, apply the IRR formula to cells ranging from B2:B4. The result is -19%, as the payment amount in the initial year is more than the previous year’s income.

To calculate the income generated at the end of five years, apply the IRR formula to cells ranging from B2:B6. The result is 22%. The result is positive as the income generated in the following years is more than the payment amount invested in the initial year.

Example #5 – IRR Calculation in Excel VBA

The IRR function can be used in VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more in Microsoft Excel as follows:

IRR Formula in Excel VBA:

The Irr formula in VBA is as follows.

IRR in Excel VBA

Where,

Values = Cash flow

IRRVal = Internal rate of return

IRR in Excel VBA Example

Let us go through the example for Irr in VBA.

IRR in Excel VBA 1

In this IRR example calculation, the IRRVal is a variable of type double. It holds the result returned by the function IRR. The values are represented in an array of data type double wherein the initial value is -10000 indicating the payment done in the first year. For example, the following values are 2,000, 5,000, 4,000, and 6,000, showing the annual income generated for the next four years.

Things to Remember

  1. The values array (range) must contain a minimum of one positive and one negative number.
  2. Values should be in sequential order. IRR uses the order of values to interpret the order of cash flows. Please enter the payment and income values in the sequence as they happen or if you desire to make it happen.
  3. Those values are ignored if an array or reference argument contains text, logical values, or empty cells.
  4. MS Excel uses an iterative technique to calculate IRR with Excel. Starting with a guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent; if IRR cannot find a result that works after 20 tries, the #NUM! Error value is returned.
  5. If the IRR function in Excel returns the #NUM! or the result is not what you expected, try with a different value.
  6. In most cases, you need not provide a guess value for the IRR calculation in Excel. However, if the guessing parameter is skipped, the software assumes 0.1, i.e., 10%.
  7. IRR is closely related to NPV, the Net Present Value function. The rate of return calculated by IRR with Excel is the interest rate corresponding to a zero Net Present ValueNet Present ValueNet Present Value (NPV) estimates the profitability of a project and is the difference between the present value of cash inflows and the present value of cash outflows over the project’s time period. If the difference is positive, the project is profitable; otherwise, it is not.read more.

Recommended Articles

This article is a guide to Calculate IRR in Excel. We discuss the IRR formula and function’s use in Excel, calculation, examples, and downloadable templates. You may also look at these other lookup and reference functions in Excel: –