Calculate IRR in Excel

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 which is used to calculate the same and it is also a financial formula, this function takes a range of values as an input for which we need to calculate internal rate of return and a guess value as the second input.

IRR in excel stands for Internal Rate of Return. IRR in Excel is one of the built-in functions available in Microsoft Excel. IRR on excel falls under the category of Financial Functions.

The Internal Rate of Return is the rate of interest received for the investment done. IRR in excel consists of the payments done represented by the negative values and the income generated denoted by positive values that occur at a regular time interval.

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)

Formula used to Calculate IRR in Excel

The IRR formula Excel is as follows:

IRR formula

Where,

  • range = This is a required parameter. A range of cells that denote the series of cash flows for which the Internal Rate of Returns would be calculated.
  • guess = This is an optional parameter. It denotes a number that you guess is close to the result of the internal rate of return. 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.

How to Calculate IRR in Excel? (with Examples)

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

The IRR Calculations in Excel can be done in 2 ways, i.e., a Worksheet (WS) function or a VBA function. As a WS function, it can be entered as a part of the formula in a cell of a worksheet. 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, it can be used in macro code, which is entered through the Microsoft Visual Basic Editor integrated into MS Excel. Refer to the IRR calculation in the excel example given below to understand better.

Example #1 – IRR Calculation in Excel Worksheet

IRR Example 1

In this IRR example calculation, -3000 denotes the capital amount invested. The subsequent numbers 2000 and 5000 denote income. The IRR function in excel is calculated on values -3000, 2000, and 5000. The result is 67%.

Example #2

IRR Example 2

In this IRR example calculation, #NUM! is displayed as a result because there is no single negative value 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. This indicates there was no income generated. Hence, the result generated is #NUM! 

Example #4

IRR Example 4

In this IRR example calculation, the total values are five. -10000 denotes the payment done. The following values are per year, i.e., income earned in Year 1 is 2000, year 2 is 5000, year 3 is 4000, year 5 is 6000.

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 income generated in the subsequent years.

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’s 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 the form of an array of data type double wherein the initial value is -10000 indicating the payment done in the first year. The subsequent values are 2000, 5000, 4000, and 6000, indicating the income generated per year for the next four years.

Things to Remember

  1. The values array (range) must contain a minimum 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 take care of entering the payment and income values in the sequence as they happened or you desire to make it happen.
  3. If an array of reference argument contains text, logical values, or empty cells, those values are ignored.
  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 if the result is not around what you expected it to be, then try with a different value for
  6. In most cases, you need not provide guess value for the IRR calculation in excel. If the guessing parameter is skipped, the software assumes it to be 0.1, i.e., 10%.
  7. IRR is closely related to NPV, i.e., 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.

IRR in Excel Video

Recommended Articles

This has been a guide to IRR Excel Function. Here we discuss the IRR Formula in excel and how to Calculate IRR in Excel along with practical IRR calculation examples and downloadable excel templates. You may also have a look at these other lookup and reference functions in excel.

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *