Calculate IRR 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.

IRR 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 flows 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.

The IRR formula in Excel is as follows:

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.

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 function, 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

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

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

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

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 code in Microsoft Excel as follows:

IRR Formula in Excel VBA:

The Irr formula in VBA is as follows.

Where,

Values = Cash flow

IRRVal = Internal rate of return

Let us go through the example for Irr in VBA.

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.

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