Internal rate of return: concept and calculation

To assess the effectiveness of planned investments, entrepreneurs consider a number of important economic indicators, such as payback period, net income, need for additional capital, financial stability, etc. One of the key among them is an indicator called the internal rate of return. Let's dwell on it in more detail.

The internal rate of return is often abbreviated as IRR. This term means the maximum cost of investment at which investing money in the project will remain profitable. In other words, the internal rate of return is the average return on invested capital that a given project will provide. This parameter is based on the discounted cash flow method and allows you to make the right decision regarding the feasibility of investing.

Calculation formula and interpretation

The internal rate of return IRR is determined from the following equation:

FCF 1 /(1+IRR) + FCF 2 /(1+IRR) 2 + FCF 3 /(1+IRR) 3 + … + FCF t /(1+IRR) t - Initial Investment = 0, where

FCF t is the current cash flow for the time period t,

Initial Investment - initial investment.

This coefficient is calculated by successive substitution into the formula of such a value of the discount rate at which the total present value of the profit from the planned investment will correspond to the value of these investments, i.e. the NPV indicator is 0. As a rule, the internal rate of return of the project is determined either using a schedule or through specialized programs. In the first case, the dependence of NPV on the level of the discount rate is displayed on the grid of coordinates, and in the second case, MS Excel is usually used to find the IRR, in particular, the formula =VNDOH(). The resulting value is compared with the price of the source of capital (if you plan to take a loan from a bank), or simply with the interest on a deposit. Let's designate cost of the advanced capital through СС (capital cost). As a result of the comparison, one of three options may arise:

Practice

Let's take a simple example first. Suppose that the implementation of the project will require an initial cost of 100,000 UAH. A year later, the amount of net present profit will be UAH 127,000. Let's calculate what the internal rate of return will be in this case: 130,000 / (1 + IRR) - 100,000 = 0. Having solved it, we get that the required coefficient is: 127,000: 100,000 - 1 = 0.27, or 27 %. Now let's take a more complicated example. Suppose that the initial investment is 90,000 rubles, the discount rate is at the level of 10%, and the cash flows are distributed over time as follows (data in thousand UAH):

• 1 year - 48.4
• 2 year - 54.5
• 3 year - 67.3
• 4 year - 20.4
• 5 year - loss 70.4
• 6 year - 30.2
• 7 year - 55.9
• 8 year - loss 20.1

What will be equal in this case, NPV and IRR? Here we need Excel. Copy our data to the top of a new sheet:

Let's put in cell A4 the value 0.1 - the discount rate. To calculate NPV, we use the formula: = NPV (A4; С2: J2) + B2. Please note that we do not discount the initial investment as it is made at the beginning of the year. If they were produced during the first year, then cell B2 would also need to be included in the calculation range. However, to obtain the total value of free cash flows, we must add this value. So, in a fraction of a second, we get that NPV = 146.18 - 90 = 56.18. IRR is even easier to calculate. Since the data in our example came in regularly, instead of the formula =VNDOH(), which requires dates, we can use the function =VSD(). So, we insert the expression = IRR (B2:J8) into a free cell and instantly we get that the internal rate of return is 38%.