Calculate IRR in Excel (Table of Contents)
Calculate IRR in Excel
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 in.
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 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.
IRR Formula in Excel
The IRR formula Excel is as follows:
- 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?
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 function, 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 excel example given below to understand better.
1] IRR Calculation in Excel Worksheet
IRR Excel Calculation 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%.
IRR Excel Calculation Example #2
In this IRR example calculation, #NUM! is displayed as 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.
IRR Excel Calculation 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!
IRR Excel Calculation 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, the IRR formula is applied on 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, the IRR formula is applied on cell 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.
2] 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.
Values = Cash flow
IRRVal = Internal rate of return
IRR in Excel VBA Example
Let’s 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 the form of 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 about Calculating IRR in Excel
- The values array (range) must contain minimum one positive and one negative number.
- Values should be in a 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.
- If an array of reference argument contains text, logical values, or empty cells, those values are ignored.
- MS Excel uses an iterative technique to calculate IRR with Excel. Starting with 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.
- If 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
- In most cases, you need not provide guess value for the IRR calculation in excel. If the guess parameter is skipped, the software assumes it to be 0.1 i.e. 10%.
- 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 Value.
You can download this IRR Function in Excel template here – IRR Function Excel Template
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.
- NPV vs IRR
- Time Value of Money Formula
- Top 15 Financial Functions in Excel
- INDIRECT Function in Excel