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.
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. 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. 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.
Formula used to Calculate IRR 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? (with Examples)
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., 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
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%.
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.
In this IRR example calculation, all the values are negative. This indicates there was no income generated. Hence, the result generated is #NUM!
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. 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 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
- The values array (range) must contain a minimum one positive and one negative number.
- 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.
- 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 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.
- 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
- 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%.
- 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..
IRR in Excel Video
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.