We can use IRR (Internal Rate of Return) function in Excel to calculate return from a series of even spaced cash flows. IRR Example: For example, say you invest $10,000 in a savings bond and you get $500 per year for next 5 years and then you will get $11,000 back. In Excel, the function =IRR is a handy formula, Using the example in our Excel workbook above, the IRR formula in cell B10 computes the IRR for this series of cashflow in B2:B7 as 5.59%. When we verify the results in column C by calculating the discounted cash flow, we also see C2:C7 sums to 0, i.e. 5.59% is the IRR. Excel uses three different functions to determine IRR. These are IRR, XIRR, and MIRR. To use the formulas, simply select the “accounting” drop-down menu shown at the top of your screen and search for the formula that you want to use. Excel will then prompt you to enter in the necessary values and generate a calculation.

Internal Rate of Return is another method used for cash flow valuation. IRR is a discount rate, whereby NPV equals to zero. You might use the following Excel function:

There is no exact and universal formula for the IRR valuation; therefore, only approximate methods are used in practice. For example, you might use the GoalSeek function in Excel. Let's take an example of NPV calculation and try to find the value of discount rate (cell C15), so that NPV (cell C12) would equal to zero:

Moreover, you might calculate IRR by using the Newton’s method, where the root of an equation is calculated recursively based on the previous value and the angle tangent of that point:

In our case, Х – is a discount rate, f(x) – NPV, and a derivative can be calculated by knowing the value of function for two points.

For more precise results, this method has to be applied several times. Let’s add more columns for calculating several iterations:

Above picture shows the several iterations for calculating NPC at discount rate of 1%.

Let's specify the formula for calculating the discount rate. First of all, we need to calculate IRR for iteration №0. For doing this, we need to know the start value (for example, 1% - cell C15) and NPV (cell C12). Moreover, for calculating the derivative we need to know more values: let's assume that NPV is the total sum of payments (cell B12) if the discount rate is 0% (cell B15). Then the formula for IRR calculation will be equalled to the cell D15:

Copy this formula in order to apply it for cells E15:I15, and then you will see that the approximate value of IRR applied to iteration #5 will not differ from the value calculated by means of Excel functions:

Obviously, it is much easier to use the IRR formula in Excel, however in this article this calculation method is shown as an example. The principle of this method can be used in practice, especially in such situations where the use of macro commands or GoalSeek functions is not possible.

Download: IRR Calculation Excel Template (12 Kb)

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.

Formula for IRR in Excel

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.

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.

Excel Multiple Irr

Read More : How to calculate CAGR in Excel.