IRR is the acronym for Internal Rate Of Return.It is defined in the terms of NPV or Net Present Value. The IRR can be stated as the discount rate that makes the NPV of all cash flows ( both positive and negative cash flows) from a project or investment equal to zero. It's one of the most important financial tool to evaluate the desirability of a potential project or investment. A higher IRR than the company's own acceptable rate indicates the project or investment is likely to give good return in future. Whereas, the lower IRR indicates poor return on investment.
IRR can be easily calculated with our online irr calculator, which is an excellent tool to get quick result. However, for offline use, you can still prefer using the spreadsheet programs like Excel, Libreoffice Calc, OpenOffice Calc, etc. Let's find out, how to calculate IRR in Excel spreadsheet. The method for calculating IRR in other spreadsheets is almost same.
The IRR Function Calculates the internal rate of return for a series of periodic (consistent timing) cash flows. If the cash flows are not periodic, use the XIRR function instead. To use the IRR Excel Worksheet Function, select a cell and type: (Notice how the formula inputs appear). The syntax for the IRR function in Microsoft Excel is: IRR( range, estimatedirr ) Parameters or Arguments range A range of cells that represent the series of cash flows. Estimatedirr Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an estimatedirr of 0.1 or 10%. Since the main purpose of Microsoft Excel is to speed up your work, there’s a short formula as well when it comes to IRR in Microsoft Excel. Formula for IRR in Excel 2013. The IRR formula in excel is quite simple. You don’t need to create charts and assume some imaginary discount rates and figure out NPVs using them and then apply the IRR.
Formula for IRR in Excel
The IRR function in Excel is designed to work with regular cash flow periods such as weekly, monthly, quarterly or annually. If your inflows and outflows occur at unequal intervals, IRR would still consider the intervals equal and return a wrong result. In this case, use the XIRR function instead of IRR. Different borrowing and reinvestment rates. First, I meant to say: you might get different results because I am using Excel 2007, and I believe Excel (X)IRR was 'improved' starting with Excel 2010. Second, -0.0465377 sic does not work for me, either. Note that I wrote -4.65%. You would think that a closer 'guess' would work as well, if not better.
The formula or function for calculating IRR in excel considers the initial investment as the first cash flow (negative Cash flow). So the formula ( or function) for calculating IRR in Excel is :
IRR(value1,value2,... )
In the above formula the value1 would be the initial investment (Period 0 with negative cash flow) followed by cash flows from all other periods.
IRR calculation in Excel with example
Using the above mentioned formula, let's find out how to calculate IRR in an Excel Spreadsheet with a real world example. Given below is a table with cash flows data for different period. The initial investment made in a project is $5000. So, cash flow for period 0 will be -5000. Since the cash is flowing outwards for period 0, that's why it is considered as negatve cash flow. All other cash flows are given into the table. Based on these given data, we would calculate the Internal Rate of Return in Excel.
| Cash Flows | |
| Period 0 (Initial Investment) | -5000 |
| Period 1 | 1000 |
| Period 2 | -150 |
| Period 3 | 3000 |
| Period 4 | -700 |
| Period 5 | 2500 |
| Period 6 | 900 |
Step 1: Getting all values inside Excel
First you have to fill in the data inside the cells of Excel. You can start with any row or column but in this case we'll start with extreme left column A and column B. Let the column A should contain the cash-flow period and the column B should have it's corresponding values (either positive or negative) depending upon the data given into the example above. The initial investment or period 0 will be into the first row under colum B that's why it's cell is B1. Similarly, other values are written under column B. The cash-flow count starts with B1 cell and ends upto B7 cell as shown in the picture below.
Step 2: Appying Excel Formula inside a cell
Since, the values of cash flows starts with B1 and goes upto B7 cell. Now, we have all the values ready to be applied inside the function to get the IRR result. Let's select a new cell under the last cash flow and name it as 'internal rate of return'. The corresponding cell B8 should have the IRR value. Based upon the above mentionded formula type '=IRR(B1:B7)'. Here (B1:B7) is equivalent to (B1,B2,B3,....,B7).
Step 3: Finally, Getting the result
Finally, after typing the above function inside the B8 cell hit enter. This will automatically return the IRR inside the same cell (B8) in percentage. Therefore, the IRR for this example would be 7.59%
The IRR of 7.59% would be acceptable case for desirability of a project or investment. That's it, this is how you can calculate IRR inside Excel
Read More : How to calculate NPV in Excel.
Read More : How to calculate CAGR in Excel.
Description
The VBA IRR function calculates the Internal Rate of Return for a supplied series of periodic cash flows (i.e. a series of payments and returns).
The syntax of the function is:
Where the function arguments are:
| ValueArray | - | An array of cash flows, representing a series of payments and income, where:
|
| [Guess] | - | An initial estimate at what the IRR will be. If this argument is omitted, it will take on the default value of 10% (=0.1). (Note this is only a value for the function to start off working with - the IRR function then uses an iterative procedure to converge to the correct rate). |
VBA IRR Function Example
In the following example, the VBA IRR function is used to calculate the internal rate of return for an initial investment of $100, that generates a series of cash returns over 5 years.
' Calculate the internal rate of return of an initial investment of $100, ' that generates a series of cash returns over 5 years. Dim cashFlows(0 to 5) As Double Dim irrVal As Double cashFlows(0) = -100 ' Initial investment of $100 irrVal = IRR( cashFlows )cashFlows(1) = 20 ' Return from year 1 cashFlows(2) = 24 ' Return from year 2 cashFlows(3) = 28.8 ' Return from year 3 cashFlows(4) = 34.56 ' Return from year 4 cashFlows(5) = 41.47 ' Return from year 5 ' irrVal is calculated to be 0.130575756375562. |
The above VBA code calculates the internal rate of return for the investment to be 0.130575756375562 (13.1%).
Note that:
- As the initial investment of $100 is an outgoing payment, this is supplied to the function as a negative value;
- As the returns during years 1-5 are incoming payments, these are supplied to the function as positive values.
VBA IRR Function Error
The VBA IRR function produces the Run-time error '5': Invalid procedure call or argument if either:
- The supplied ValueArray does not contain at least one negative value and at least one positive value
How To Make Irr Work In Excel
or- The function fails to converge after 20 iterations.
Irr To Work In Excel Sheet
Return to the Excel VBA Tutorial Page