Irr in Excel. Calculation of NPV and IRR in Excel. Application of internal rate of return

Let's explore the concept of net present value (NPV) of an investment project, give a definition and economic meaning, use a real example to look at calculating NPV in Excel, and also consider a modification of this indicator (MNPV).

Net present value(NPVNetPresentValue, net present value, net present value)– shows the effectiveness of an investment in an investment project: the amount of cash flow during the period of its implementation and reduced to the current value (discounting).

Net present value. Calculation formula

where: NPV – net present value of the investment project;

CF t (Cash Flow) – cash flow in time period t;

IC (Invest Capital) – investment capital represents the investor’s expenses in the initial time period;

r – discount rate (barrier rate).

Making investment decisions based on the NPV criterion

The NPV indicator is one of the most common criteria for evaluating investment projects. Let us consider in the table what decisions can be made at different NPV values.

Calculate and forecast future cash flow (CF) in Excel

Cash flow represents the amount of cash that a company/enterprise has at a given point in time. Cash flow reflects the financial strength of a company. To calculate cash flow it is necessary from the cash inflow (CI,Cash Inflows) means to take away the outflow (CO,Cash Outflows) , the calculation formula will look like this:

Determining the future cash flow of an investment project is very important, so let’s consider one of the forecasting methods using MS Excel. Statistical forecasting of cash flows is only possible if the investment project already exists and is operating. That is, funds are needed to increase its capacity or scale it. I would like to note that if the project is a venture project and does not have statistical data on production volumes, sales, costs, then an expert approach is used to assess future cash income. Experts compare this project with analogues in this area (industry) and assess the potential for possible development and possible cash flows.

When forecasting the volume of future receipts, it is necessary to determine the nature of the relationship between the influence of various factors (forming cash receipts) and the cash flow itself. Let's look at a simple example of predicting future cash flows from a project depending on advertising costs. If there is a direct relationship between these indicators, then you can predict what cash receipts will be depending on costs using linear regression in Excel and the “TREND” function. To do this, we write the following formula for advertising costs of 50 rubles.

Cash Flow (CF). B12=TREND(B4:B11,C4:C11,C12)

The size of the future cash flow will be 4831 rubles. with advertising costs of 50 rubles. In reality, determining the size of future revenues is influenced by a much larger number of factors, which should be selected according to the degree of influence and their relationship with each other using correlation analysis.

Determining the discount rate (r) for an investment project

Calculating the discount rate is an important task in calculating the current value of an investment project. The discount rate represents the alternative return that an investor could have received. One of the most common purposes for determining a discount rate is to estimate the value of a company.

To estimate the discount rate, methods such as the CAPM model, WACC, Gordon model, Olson model, E/P market multiples model, return on equity, Fama and French model, Ross model (ART), expert assessment, etc. are used. There are many methods and their modifications for estimating the discount rate. Let us consider in the table the advantages and initial data that are used for the calculation.

A change in the discount rate has a non-linear effect on the change in net present value; this relationship is shown in the figure below. Therefore, when choosing an investment project, it is necessary not only to compare NPV values, but also the nature of the change in NPV at different rates. Analysis of various scenarios allows you to choose a less risky project.

Calculate Net Present Value (NPV) Using Excel

Let's calculate net present value using Excel. The figure below shows a table of changes in future cash flows and their discounting. So, we need to determine the discount rate for a venture investment project. Since it has no issues of ordinary shares, no dividend payments, and no estimates of return on equity and debt capital, we will use the method of expert assessments. The evaluation formula will be as follows:

Discount rate=Risk-free rate + Risk adjustment;

Let's take a risk-free rate equal to interest on risk-free securities (GKOs, OFZs, these interest rates can be viewed on the website of the Central Bank of the Russian Federation, cbr.ru) equal to 5%. And adjustments for industry risk, the risk of the impact of seasonality on sales and personnel risk. The table below shows estimates of adjustments taking into account these identified types of risk. These risks have been identified by experts, so when choosing an expert you need to pay close attention.

As a result, adding up all the adjustments for the risk affecting the investment project, the discount rate will be = 5 + 15 = 20%. After calculating the discount rate, it is necessary to calculate the cash flows and discount them.

Two options for calculating net present value NPV

The first option for calculating net present value consists of the following steps:

1. Column “B” reflects the initial investment costs = 100,000 rubles;
2. Column “C” reflects all future planned cash receipts for the project;
3. Column "D" records all future cash expenses;
4. Cash flow CF (column “E”). E7= C7-D7;
5. Calculation of discounted cash flow. F7=E7/(1+$C$3)^A7
6. Calculate the present value (NPV) minus the initial investment cost (IC). F16 =SUM(F7:F15)-B6

The second option for calculating net present value is to use Excel's built-in NPV (net present value) financial function. Calculation of the net present value of the project minus the initial investment costs. F17=NPV($C$3;E7;E8;E9;E10;E11;E12;E13;E14;E15)-B6

The figure below shows the resulting net present value calculations. As we can see, the final result of the calculation is the same.

Modification of net present value MNPV (Modified Net Present Value)

In addition to the classic net present value formula, financiers/investors sometimes use its modification in practice:

MNPV – modification of net present value;

CF t – cash flow in time period t;

I t – cash outflow in time period t;

r – discount rate (barrier rate);

d – level of reinvestment, interest rate showing possible income from reinvestment of capital;

n – number of analysis periods.

As we see, the main difference from the simple formula is the possibility of taking into account the profitability from reinvestment of capital. Evaluation of an investment project using this criterion has the following form:

Advantages and disadvantages of the net present value valuation method

Let's compare the advantages of the NPV and MNPV indicators. The advantages of using these indicators include:

• Clear boundaries for choosing and assessing the investment attractiveness of the project;
• Possibility of taking into account additional project risks in the formula (discount rate);
• Using a discount rate to reflect changes in the value of money over time.

The disadvantages of net present value include the following:

• Difficulty in assessing complex investment projects that involve many risks;
• Difficulty in accurately forecasting future cash flows;
• No influence of intangible factors on future profitability (intangible assets).

Resume

Despite a number of shortcomings, the net present value indicator is key in assessing the investment attractiveness of a project, comparing it with analogues and competitors. In addition to estimating NPV, for a clearer picture, it is necessary to calculate investment ratios such as IRR and DPI.

NPV (abbreviation in English - Net Present Value), in Russian this indicator has several variations of the name, among them:

• net present value (abbreviated NPV) is the most common name and abbreviation, even the formula in Excel is called exactly that;
• net present value (abbreviated NPV) - the name is due to the fact that cash flows are discounted and only then summed up;
• net present value (abbreviated NPV) - the name is due to the fact that all income and losses from activities due to discounting are, as it were, reduced to the current value of money (after all, from the point of view of economics, if we earn 1,000 rubles and then actually receive less than if we received the same amount, but now).

NPV is an indicator of the profit that participants in an investment project will receive. Mathematically, this indicator is found by discounting the values ​​of net cash flow (regardless of whether it is negative or positive).

Net present value can be found for any period of time of the project since its beginning (for 5 years, for 7 years, for 10 years, and so on) depending on the need for calculation.

What is it for?

NPV is one of the indicators of project efficiency, along with IRR, simple and discounted payback period. It is needed to:

1. understand what kind of income the project will bring, whether it will pay off in principle or is it unprofitable, when it will be able to pay off and how much money it will bring at a particular point in time;
2. to compare investment projects (if there are a number of projects, but there is not enough money for everyone, then projects with the greatest opportunity to earn money, i.e. the highest NPV, are taken).

Calculation formula

To calculate the indicator, the following formula is used:

• CF - the amount of net cash flow over a period of time (month, quarter, year, etc.);
• t is the period of time for which the net cash flow is taken;
• N is the number of periods for which the investment project is calculated;
• i is the discount rate taken into account in this project.

Calculation example

To consider an example of calculating the NPV indicator, let's take a simplified project for the construction of a small office building. According to the investment project, the following cash flows are planned (thousand rubles):

The project discount rate is 10%.

Substituting into the formula the values ​​of net cash flow for each period (where negative cash flow is obtained, we put it with a minus sign) and adjusting them taking into account the discount rate, we get the following result:

NPV = - 100,000 / 1.1 + 31,000 / 1.1 2 + 32,500 / 1.1 3 + 33,000 / 1.1 4 + 34,500 / 1.1 5 = 3,089.70

To illustrate how NPV is calculated in Excel, let's look at the previous example by entering it into tables. The calculation can be done in two ways

1. Excel has an NPV formula that calculates the net present value, to do this you need to specify the discount rate (without the percent sign) and highlight the range of the net cash flow. The formula looks like this: = NPV (percent; range of net cash flow).
2. You can create an additional table yourself where you can discount the cash flow and sum it up.

Below in the figure we have shown both calculations (the first shows the formulas, the second the calculation results):

As you can see, both calculation methods lead to the same result, which means that depending on what you are more comfortable using, you can use any of the presented calculation options.

Let's analyze such an indicator as the internal rate of return of an investment project, determine the economic meaning and consider in detail an example of its calculation using Excel.

Internal rate of return of an investment project (IRR). Definition

Internal rate of return(English) InternalRateofReturn,IRR, internal rate of return, internal rate, internal rate of return, internal discount rate, internal efficiency ratio, internal payback ratio) – a coefficient showing the maximum acceptable risk for an investment project or the minimum acceptable level of profitability. The internal rate of return is equal to the discount rate at which there is no net present value, that is, zero.

Internal rate of return calculation formula

CFt( Cash Flow) – cash flow in time period t;

IC( Invest Capital) – investment costs for the project in the initial period (also cash flow CF 0 = IC).

t – time period.

★

Application of internal rate of return

The indicator is used to assess the attractiveness of an investment project or for comparative analysis with other projects. To do this, IRR is compared with the effective discount rate, that is, with the required level of profitability of the project (r). For this level in practice, the weighted average cost of capital is often used ( WeightAverageCost ofCapital, WACC).

It should be noted that instead of the comparison criterion WACC there can be any other barrier level of investment costs, which can be calculated using methods for estimating the discount rate. These methods are discussed in detail in the article "". A simple practical example would be to compare the IRR with the risk-free interest rate on a bank deposit. So if an investment project has IRR = 10%, and the interest on the deposit = 16%, then this project should be rejected.

The internal rate of return (IRR) is closely related to the net present value (NPV). The figure below shows the relationship between the size of IRR and NPV; an increase in the rate of return leads to a decrease in income from the investment project.

Master class: “How to calculate the internal rate of return of a business plan”

Calculation of internal rate of return (IRR) using an example in Excel

Let's look at an example of calculating the internal rate of return using Excel, and look at two methods of construction using a function and using the “Solution Search” add-in.

Example of calculating IRR in Excel using the built-in function

The program has a built-in financial function that allows you to quickly calculate this indicator - IRR (internal discount rate). It should be noted that this formula will only work when there is at least one positive and one negative cash flow. The calculation formula in Excel will look like this:

Internal rate of return (E16)=VSD(E6:E15)

Internal rate of return. Calculation in Excel using built-in formula

As a result, we found that the internal rate of return is 6%; then, to conduct an investment analysis, the obtained value must be compared with the cost of capital (WACC) of this project.

★ (calculation of Sharpe, Sortino, Treynor, Kalmar, Modiglanca beta, VaR) + forecasting course movements

Example of calculating IRR using the “Solution Search” add-on

The second calculation option involves using the “Solution Search” add-on to find the optimal value of the discount rate for NPV=0. To do this, you need to calculate net present value (NPV).

The figure below shows the formulas for calculating discounted cash flow by year, the sum of which gives net present value. The formula for calculating discounted cash flow (DCF) is as follows:

Discounted cash flow (F)=E7/(1+$F$17)^A7

Net present value (NPV)=SUM(F7:F15)-B6

The figure below shows the initial view for calculating IRR. You will notice that the discount rate used to calculate NPV refers to a cell that has no data (it is set to 0).

Internal rate of return (IRR) and NPV. Calculation in Excel using an add-in

Now our task is to find, based on optimization using the “Search for Solutions” add-on, the value of the discount rate (IRR) at which the NPV of the project will be equal to zero. To do this, open the “Data” section in the main menu and “Search for solutions” in it.

When you click in the window that appears, fill in the lines “Set target cell” - this is the formula for calculating NPV, then select the value of this cell equal to 0. The variable parameter will be the cell with the value of the internal rate of return (IRR). The figure below shows an example of a calculation using the Solution Search add-on.

Finding the IRR value for NPV=0

After optimization, the program will fill our empty cell (F17) with the value of the discount rate at which the net present value is zero. In our case, it turned out to be 6%, the result completely coincides with the calculation using the built-in formula in Excel.

The result of calculating the internal rate of return (IRR)

Calculation of internal rate of return in Excel for unsystematic receipts

In practice, it often happens that funds do not arrive periodically. As a result, the discount rate for each cash flow will change, making it impossible to use the IRR formula in Excel. To solve this problem, another financial formula is used: NET INDOH (). This formula includes an array of dates and cash flows. The calculation formula will be as follows:

NETINDOH(E6:E15;A6:A15;0)

Calculation of internal rate of return in Excel for non-systematic payments

Modified internal rate of return (MIRR)

Also used in investment analysis modified internal rate of return (Modified InternalRateofReturn,MIRR) – this indicator reflects the minimum internal level of profitability of the project when reinvesting in the project. This project uses interest rates received from reinvesting capital. The formula for calculating the modified internal rate of return is as follows:

MIRR – internal rate of return of an investment project;

COF t – cash outflow during time periods t;

CIFt – cash inflow;

r is the discount rate, which can be calculated as the weighted average cost of capital WACC;

d – interest rate of capital reinvestment;

n – number of time periods.

Calculation of modified internal rate of return in Excel

To calculate this modification of the internal rate of return, you can use the built-in Excel function, which uses, in addition to cash flows, the size of the discount rate and the level of return on reinvestment. The formula for calculating the indicator is presented below:

MIRR =MIRR(E8:E17,C4,C5)

Advantages and Disadvantages of Internal Rate of Return (IRR)

Let's consider advantages of the indicator internal rate of return for project evaluation.

Firstly, the ability to compare various investment projects with each other in terms of the degree of attractiveness and efficiency of capital use. For example, comparison with the return on risk-free assets.

Secondly, the ability to compare different investment projects with different investment horizons.

TO shortcomings of the indicator include:

First, the disadvantages of estimating internal rate of return are the difficulty of predicting future cash payments. The amount of planned payments is influenced by many risk factors, the impact of which is difficult to objectively assess.

Secondly, the IRR indicator does not reflect the amount of reinvestment in the project (this shortcoming is resolved in the modified internal rate of return MIRR).

Third, the inability to reflect the absolute amount of cash received from the investment.

Resume

In this article, we examined the formula for calculating the internal rate of return (IRR), and examined in detail two ways to construct this investment indicator using Excel: based on built-in functions and the “Solution Search” add-in for systematic and unsystematic cash flows. It was highlighted that the internal rate of return is the second most important indicator for evaluating investment projects after net present value (NPV). A variation of IRR is its modification MIRR, which also takes into account the return on capital reinvestment.

Any investment requires careful calculations. Otherwise, the investor risks losing the invested funds.

At first glance, the business is profitable and attractive for investment. But this is only the first impression. A thorough analysis of the investment project is required. And you can do this yourself using Excel, without involving expensive specialists and experts in investment portfolio management.

Calculation of an investment project in Excel

An investor invests money in a ready-made enterprise. Then he needs to evaluate the efficiency of work (profitability, reliability). Or into a new business - all calculations are carried out on the basis of data obtained during market research (infrastructure, personal income, inflation rate, etc.).

Let's consider creating a business from scratch. Let's calculate the profitability of an enterprise using Excel formulas. For example, we will take conditional goods and numbers. It is important to understand the principle, and you can substitute any data.

So, we have an idea to open a small store. Let's decide on the costs. They happen

• constant (cannot be calculated per unit of product);
• variables (can be calculated per unit of product).

Initial investment – ​​300,000 rubles. The money is spent on registering a business, equipping the premises, purchasing the first batch of goods, etc.

Let's create a table with fixed costs:

* Expense items are individual. But the principle of compilation is clear.

Using the same principle, we draw up a separate table with variable costs:

To find the selling price, we used the formula: =B4*(1+C4/100).

The next stage is to forecast sales volume, revenue and profit. This is the most critical stage in drawing up an investment project.

Sales volume is conditional. In real life, these figures are the result of an analysis of household income, demand for goods, inflation rate, season, location of the outlet, etc.

To calculate revenue, the formula was used: =SUMPRODUCT(B3:B6;Sheet2!$D$4:$D$7). Where the first array is sales volumes; the second array is sales prices.

Revenue minus variable costs: =B7-SUMPRODUCT(B3:B6;Sheet2!$B$4:$B$7).

Profit before taxes: =B8-Sheet1!$B$14 (revenue without variable and fixed costs).

UTII taxes: =Sheet1!A10*1800*0.15*3 (1800 – basic profitability by type of activity, 3 – number of months, C12 – premises area).

Net profit: profit – taxes.

Investment project evaluation in Excel

4 main indicators are calculated:

• net present effect (NPE, NPV);
• return on investment index (RII, PI);
• internal rate of return (IRR, IRR);
• discounted payback period (DSP, DPP).

For example, take the following investment option:

First, we discount each positive element of the cash flow.

Let's create a new column. Let's introduce a formula of the form: = positive element of cash flow / (1 + discount rate)^ degree equal to the period.

Now let's calculate the net present effect:

Let's find the return on investment index. To do this, you need to divide the net present value (NPV) by the amount of funds invested (with a “+” sign):

The result is 1.90.

Let's calculate the IRR of an investment project in Excel. Let's recall the formula:

VND = ΣDP t / (1 + VNR) t = I.

DP t – positive elements of cash flow that need to be discounted at such a rate that the net present effect is equal to zero. The internal rate of return is a discount rate at which an equality of the form occurs:

ΣDP t / (1 + VNR) t – И = 0,

Let's use the What-If Analysis tool:

The discount rate is 0.41. Consequently, the internal rate of return was 41%.

Modeling the risks of investment projects in Excel

We use the Monte Carlo simulation method. The task is to reproduce business development based on the results of an analysis of known elements and the relationships between them.

Let's demonstrate risk modeling using a simple example. Let's create a conditional template with data:

The cells that contain the formulas below are labeled with their values ​​accordingly.

Predicted indicators are the price of the service and the number of users. Under these data we write “Simulation results”. On the “Data” tab, click “Data Analysis” (if there is no tool there, you will have to connect the setting). In the window that opens, select “Random number generation”.

Fill in the parameters as follows:

We need to model the situation based on distributions of different types.

To generate the number of users, we will use the RANDBETWEEN function. The lower limit (in the worst case scenario) is 1 user. The upper limit (for the best business development option) is 50 service buyers.

Let's copy the obtained values ​​and formulas to the entire range. For variable costs we will also generate random numbers. Let us obtain an empirical distribution of project performance indicators.

To assess the risks, you need to do an economic and statistical analysis. Let's use the Data Analysis tool again. Select "Descriptive Statistics".

The program produces the result (according to the “Efficiency coefficient” column):

You can draw conclusions and make a final decision.

And what formulas are used to calculate this indicator, but it requires simple tools at hand that allow you to calculate NPV faster than manually or using conventional calculators.

A multifunctional environment helps them, allowing them to calculate NPV using tabular data or using special functions.

Let's look at a hypothetical example, which we will solve by applying the formula for calculating NPV that we already know, and then we will repeat our calculations using the capabilities of Excel.

Problem of finding NPV

Example. The initial ones in A are 10,000 rubles. Annual – 10%. The dynamics of revenues from years 1 to 10 are presented in the table below:

For clarity, the corresponding data can be presented graphically:

Figure 1. Graphical representation of the initial data for calculating NPV

Standard solution. To solve the problem, we will use the NPV formula already known to us:

We simply substitute known values ​​into it, which we then sum. For these calculations we will need a calculator:

NPV = -10000/1,1 0 + 1100/1,1 1 + 1200/1,1 2 + 1300/1,1 3 + 1450/1,1 4 + 1600/1,1 5 + 1720/1,1 6 + 1860/1,1 7 + 2200/1,1 8 + 2500/1,1 9 + 3600/1,1 10 = 352.1738 rubles.

NPV calculation in Excel (tabular example)

We can solve this same example by organizing the relevant data in the form of an Excel table.

It should look something like this:

Figure 2. Layout of example data on an Excel sheet

In order to get the desired result, we must fill in the corresponding cells with the necessary formulas.

As a result, in cell F15 we get the desired NPV value equal to 352.1738.

It takes 3-4 minutes to create such a table. Excel allows you to find the desired NPV value faster.

NPV calculation in Excel (NPV function)

Place the formula in cell B17 (or any other cell):

NPV(F2/100;C5:C14)-D14

We will instantly receive the exact NPV value in rubles (352.1738 rubles).

Figure 3. NPV calculation using Excel NPV formula

Our formula refers to cells F2 (we have an interest rate of 10% there; to use it in the NPV function, we need to divide it by 100), the value range C5:C14, where the data on inflows is located, and cell D14, which contains the size of the initial