npv excel calculation. How is IRR calculated? Calculation of IRR for monthly cash flows

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 needed for

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

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;
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):

Article	1 year	2 year	3 year	4 year	5 year
Investments in the project	100 000
Operating income		35 000	37 000	38 000	40 000
Operating expenses		4 000	4 500	5 000	5 500
Net cash flow	- 100 000	31 000	32 500	33 000	34 500

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

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).
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.

IRR is Internal Rate of Return, which is translated into Russian as “internal rate of return.” This is the name of one of the two main methods for evaluating investment projects. There are many articles on the Internet that provide a brief summary of this topic in financial analysis textbooks. Their common disadvantage is that they contain too much mathematics and too little explanation.

This article provides not only the formula and definition of IRR, but there are examples of calculations of this indicator and interpretation of the results obtained.

IRR - what is it? IRR formula.

IRR or internal rate of return is the interest rate at which the present value of all cash flows of an investment project (i.e. NPV) is zero. This means that at this interest rate, the investor will be able to recoup their original investment, but no more. How to use the IRR indicator to approve investment projects is described a little further in this article. First, you need to learn how to calculate the internal rate of return IRR, or, as it is also called, the internal rate of return.

The mathematics for calculating IRR is quite simple. It is best to consider it with elementary examples. To calculate the indicator, one of the earlier articles on this site used projects A and B with the same amount of initial investment (10,000), but with different cash inflows in the subsequent 4 years. It will be convenient to use these examples to study the formula for calculating the IRR indicator.

The present value of all cash flows for four-year projects will be calculated using the formula:

where NPV is net present value, CF is cash flows, R is the % rate, cost of capital, 0,1,2,3,4 is the number of time periods from today.

If we equate NPV to zero, and instead of CF we substitute cash flows corresponding to each project, then one variable R will remain in the equation. The interest rate, which will be the solution to this equation, i.e. at which the sum of all terms will be equal to zero, and will be called IRR or internal rate of return.

For project A, the equation will take the form:

For project B, you can write a similar formula to calculate IRR, only the cash flows will be different:

To make it even clearer, you can plot the cash flows from the project on a time scale and visualize discounting. Say, for project A, the calculation of the internal rate of return can be presented as follows:

In general, for any investment project, the formula for calculating IRR looks like this:

where CF t - cash flows from the project at time t, n - number of time periods, IRR - internal rate of return. Please note that the concept of IRR, unlike NPV, makes sense only for an investment project, i.e. when one of the cash flows (usually the very first) is negative. This negative cash flow will be the initial investment. Otherwise we will never get an NPV of zero.

Calculating internal rate of return using Excel - examples

It is impossible to manually find the IRR value for projects A and B using a regular calculator, because in this case an equation of the 4th degree is obtained (it will contain a multiplier of IRR 4 - the interest rate to the fourth power). The problem of solving such an equation of the nth degree can be eliminated either using a financial calculator, or, more simply, you can use the built-in function in Excel. This function is located in the Formulas -> Financial section, and it is called IRR (internal rate of return).

For project A, the IRR value, as can be seen from the figure below, will be 14.48%.

To use the IRR function, in the “values” line you need to put links to the table cells with the amounts of cash flows. The “assumption” cell can be left blank; this argument is optional. The output value 0.144888443 is the desired IRR, i.e. internal rate of return for a given project. If we convert this value into percentages, then it is equal to 14.48% accurate to two decimal places.

For project B, the IRR value according to Excel is 11.79%.

I will give important explanations on this function from the “help” section with my additions:

The values must contain at least one positive and one negative value. Otherwise, the VSD function returns the #NUM! error value. Indeed, if there is no negative cash flow, then NPV cannot be equal to zero, and in this case the IRR does not exist.
To calculate the IRR function, the order in which funds are received is important. Therefore, if cash flows differ in magnitude in different periods, which usually happens, then they must be entered into the table in accordance with the time of their occurrence.
Microsoft Excel uses the iteration method to calculate IRR. The VSD function performs cyclic calculations starting from the value of the “guess” argument until a result is obtained with an accuracy of 0.00001%. In most cases, you do not need to specify a guess argument for calculations using the VSD function. If omitted, a value of 0.1 (10%) is assumed.

In other words, the IRR function of the Excel program will search for the IRR value by selection, sequentially substituting various percentage rates into the formula, starting with the value in the “assumption” cell or with 10%. If the VSD function fails to obtain a result after 20 attempts, the error value #NUM! Therefore, in some cases, for example, if you will calculate the IRR for monthly flows over several years, it is better to put the expected monthly interest rate in the “guess” cell. Otherwise, Excel may not be able to complete the calculation in 20 attempts.

Graphical method for calculating IRR

Before the advent of personal computers, a graphical method for determining IRR was commonly used. Below are graphs of changes in NPV for projects A and B depending on the interest rate. To construct graphs, you need to find the NPV value by substituting different values of the discount rate into the NPV formula. You can read it in one of my previous articles.

In the figure above, the blue graph is project A, the red graph is project B. The intersection of the graphs with the X-axis (at this point the NPV of the project is zero) will precisely give the IRR value for these projects. It is easy to see that the graphical method gives an IRR value similar to the internal rate of return values found in Excel for projects A - 14.5% and B - 11.8%.

How to use the IRR indicator to evaluate investment projects?

Any investment project involves an initial investment (cash outflow), which will lead to cash inflows in the future (ideally). What does the internal rate of return of an investment project show? It shows the lending rate at which we will not receive a loss on our investment, i.e. the result of all cash inflows and outflows will be zero - no profit, no loss. In this case, our investment in the project will be recouped by future cash flows from the project, but in the end we will not earn anything.

Rule for evaluating investment projects:

If the project's IRR is greater than the company's cost of capital (i.e., WACC), then the project should be accepted.

In other words, if the loan rate is less than the investment rate (the internal rate of return of the project), then the borrowed money will bring added value. Because such an investment project will earn a higher percentage of income than the cost of capital that is required for the initial investment.

For example, if you take out a bank loan at 14% per annum in order to invest in a business project that will bring you 20% annual income, then you will make money on this project. If your calculations turn out to be incorrect, and the internal rate of return of your project is below 14%, then you will have to give the bank more money than you receive from the project. That is, you will incur a loss.

The bank itself does the same. It attracts money from the population, say, at 10% per annum (deposit rate), and issues loans at 20% per annum (a figure taken “out of the blue”). As long as the rate on deposits accepted by the bank is less than the rate on loans issued by the bank, the bank will live on this difference.

By calculating the IRR, we find out the upper acceptable level of the cost of borrowed capital that is supposed to be invested. If the cost of capital (at which a company can raise financial resources) is higher than the project's internal return (IRR), then the project will generate losses. If the company's cost of capital is lower than the project's IRR, then the company will, in a sense, operate like a bank - living off the difference between bank lending interest rates and the return on investment.

To make the logic of calculating IRR even clearer, I will give several examples from life that an ordinary person can (and does) encounter.

Example 1 - time deposit in Sberbank

Let's say you have 6,000,000 rubles available. Right now you can make a fixed-term deposit in Sberbank, say, for three years. The amount is large, so we need the most reliable bank in Russia. Sberbank currently offers a rate for deposits over 2 million rubles for three years in the amount of 9.0% per annum without capitalization and 10.29% per annum with monthly capitalization. You can read what it is by following the link.

Since we will withdraw interest at the end of each year, this will be a deposit without interest capitalization, and the rate will be 9% per annum. At the end of each year, it will be possible to withdraw an amount equal to 6,000,000 * 0.09 = 540,000 rubles. At the end of the third year, the deposit can be closed by withdrawing interest for the third year and the principal amount of 6 million rubles.

A bank deposit is also an investment project, since first an initial investment is made (negative cash flow), and then cash inflows from our project are collected. A bank deposit is a financial instrument and the easiest way of investing available to the average person. Since this is an investment project, we can calculate its internal rate of return. Probably many have already guessed what it will be equal to.

The internal rate of return (IRR of an investment) in a bank deposit is equal to the interest rate on this deposit, i.e. 9%. If you inherited 6,000,000 rubles after taxes, then this means that the cost of capital for you is zero. Therefore, such an investment project will be profitable at any deposit rate. But taking a loan for 6 million from one bank and putting this money on deposit in another bank with a profit will not work: the loan rate will always be obviously higher than the investment rate. This is the operating principle of the banking system.

Example 2 - buying an apartment to make money by renting it out

Free funds can be used in another way, namely, buy an apartment in Moscow, rent it out for three years, and at the end of the third year sell this apartment to return the main capital. The cash flows from such a project will be very similar to the cash flows from a fixed-term deposit in a bank: let us assume that, for ease of calculation, the rent is paid by the tenant of the apartment immediately for the year at the end of each year, and the cost of the apartment in rubles after three years will remain the same as and now. I’m intentionally simplifying the situation; you can do more complex calculations yourself.

I chose the first apartment I came across on the Internet for 6 million rubles in the northwestern part of Moscow. Renting such a one-room apartment costs 30,000 rubles per month. For simplicity, the tax consequences of these transactions are not taken into account.

So, the rent for the year will be 30,000 * 12 = 360,000 rubles. To make it more clear, cash flows from both projects - a deposit in Sberbank and renting out a 1-room apartment in the north-west of Moscow - are shown together in the table below:

Even without calculating IRR, it is clear that now a bank deposit is a more profitable option. It is easy to prove this if you calculate the internal rate of return for the second project - it will be lower than the IRR on the deposit. When renting out this one-room Moscow apartment for three years, provided it is sold at the end of the third year, the IRR of the investment will be 6.0% per annum.

If you do not have an inheritance in the amount of 6 million rubles, then taking this money on credit to rent out an apartment is unwise, since the lending rate is now obviously higher than 6.0% of the internal profitability of this project. Moreover, the IRR does not depend on the number of years the apartment has been rented out - the internal rate of return will remain the same if, instead of three years, you rent it out for 10 years or 15.

If we take into account the annual rise in price of an apartment as a result of inflation, the IRR of this project will be higher. For example, if in the first year (2015) the ruble cost of an apartment increases by 10%, in the second (2016) by 9%, and in the third (2017) by 8% , then by the end of the third year it can be sold for 6,000,000*1.10*1.09*1.08 = 7,769,520 rubles. This increase in cash flow in the third year of the project would produce an IRR of 14.53%. Therefore, if we could predict future ruble prices for apartments with great accuracy, then our project would become more realistic. But it is still unprofitable in the current situation, when the Central Bank refinancing rate is 17%, and, accordingly, all bank loans are too expensive.

Calculation of IRR for monthly cash flows

Using the IRR function, you can calculate the IRR of an investment project with equal time intervals between cash flows. The result of the calculations will be the interest rate for the period - year, quarter, month. For example, if we believed that payments for renting an apartment come at the end of each month (and not the year), then we would need to make an Excel table with 36 payments of 30,000 rubles each. In this case, the IRR function would give the value of the internal rate of return of the project per month. For our project, the IRR turned out to be 0.5% per month. This corresponds to an annual interest rate of 6.17% (calculated as (1+0.005) 12 -1), which is not much more than the 6.0% calculated previously.

If you want to get this result yourself, be sure to fill out the “guess” cell - put 0.03 there, otherwise you will get a #NUM! error in the output, because Excel will not have enough 20 attempts to calculate the IRR.

Calculation of IRR for unequal time intervals between cash flows

Excel provides the ability to calculate the internal rate of return of a project even if cash flows from the project arrive at irregular intervals. To calculate the IRR of such a project, you need to use the NET IH function and, as an argument, specify not only the cells with cash flows, but also the cells with the dates of their receipts. For example, if we postpone the sale of an apartment along with the last rent payment to the end of the fourth year (from 12/31/17 to 12/31/18), and at the end of the third year we have no cash flows, then the IRR will fall from 6% to 4. 53% per annum. Please note that in this case it will be possible to calculate the internal rate of return only using the NET IRR function, because the IRR function will give the same result as it was - 6%, i.e. VSD will not take into account changes in the time period.

“Where Piglet and I are going is a big, big secret...”

The current refinancing rate of 17% is killing both businesses and banks. Because it is difficult to find investment projects that would pay off at such lending rates. How to develop a business in such conditions? The arms and drug trade will, of course, be profitable in this case, but most businesses will survive at best, and go bankrupt at worst.

And how will banks earn money if investment projects with such high returns simply do not exist? And in order to pay us increased interest on deposits, banks must earn money somewhere to do this.

Russia could withstand a lower exchange rate of the ruble against major currencies, but coping with a high interest rate in the economy is already too much.

In 2014, we repeatedly heard that the Central Bank of the Russian Federation was engaged in inflation targeting. And this was done with good intentions - the lower the inflation, the easier it is to achieve a return on investment. But it turns out that they wanted “the best,” but it turned out “as always.” With an expensive currency, as it is now, Russia could successfully develop its own production, and import substitution would become a reality. But no, we don’t look for easy ways, and what’s worse is that we don’t learn from our mistakes. And we live, as in that joke:

“Last year we sowed 100 hectares of wheat. The hamster ate everything...This year we are going to sow 200 hectares of wheat. Let the hamster choke!”

There are many financial formulas in Excel. Let's look at the most important formulas that will allow you to calculate:

(Net Present Value) or net present value;
IRR of an investment project(Internal Rate of Return) or internal rate of return;

We will also consider some of the nuances and tricks of using these formulas. All calculations can be found in the attached file. The main focus is on Excel functions.

How to calculate NPV in Excel

Let's consider a conditional example: there is a project that will bring in 250,000 rubles annually for 5 years. Its implementation requires 1,000,000 rubles. - 10%.
If the cash flows reduced to the current period are greater than the invested money (NPV>0), then the project is profitable. Otherwise, no.

The NPV formula looks like this:

If the cash flows reduced to the current period are greater than the invested money (NPV>0), then the project is profitable. Otherwise no.

To calculate NPV, we will need to do the following in Excel:

Let's add serial numbers of years: 0 – starting year, flows are reduced to it.
1, 2, 3, etc. – these are the years of project implementation. The formula in the figure performs the actions that are written above after the sum sign (Σ).

We divide the cash flow for the period by the amount of 1 and the discount rate raised to the power of the corresponding year. The calculated line represents the discounted cash flow. To get the NPV value, it is enough to find the total amount of the entire line.

It turns out “-52 303”. The project is unprofitable.

Read also:

Who will find it useful?: Let’s say your company plans to invest in a project that seems promising at first glance. It has a good net present value (NPV), a good internal rate of return (IRR). These indicators seem completely standard and generally accepted. But there are many subtleties in their calculations that affect the final figure, and sometimes the decision made. Read the article to understand the features of project evaluation by analyzing it using different techniques.

Calculating NPV using the NPV formula

To calculate NPV in Excel, it is not necessary to prepare such a table. Just use the NPV formula in Excel. Where NPV is the discount rate; range of discounted values. That is, it is enough to indicate a cell with interest and cash flows. But when using this formula out of habit, financiers often make a mistake:

In fact, the results should be the same. Why are there different meanings here? The fact is that NPV in Excel starts discounting from the very first value. That is, it looks for present value. And the initial investment must be taken away afterwards. The correct form of the formula in our case will be as follows:

The starting investments are “removed” beyond the discounted range (red) and added (actually, subtracted: since the starting investments are already minus, D8 needs to be added) separately. Now the result is the same.

More on the topic:

How it will help: The modified internal rate of return, compared to the conventional internal rate of return, allows for a more accurate assessment of the effectiveness of investment projects in which the net cash flow changes sign several times during the life cycle. How to calculate this indicator is described in this solution.

How it will help: in order to choose the most profitable of two investment projects, you have to evaluate the effectiveness of each of them according to one or more indicators - payback period, rate of return, net present value and internal rate of return. More details on how to compare them if the projects themselves are not comparable can be found in this solution.

How to evaluate the effectiveness of an investment project in Excel using IRR

IRR is another indicator for evaluating investment projects. IRR answers the question: what should the rate be for NPV to become = 0?

IRR Formula in Excel

If the discount rate< IRR, то проект стоит принять, если нет – отказаться. Как рассчитать IRR в Excel? Очень просто: Подставляем в функцию ВСД итоговый денежный поток.

The IRR turned out to be less than the rate of return. The project is unprofitable (same conclusion as with NPV).

NPV and IRR are rightfully considered the main economic criteria. They are used both for investment evaluation of projects and for assessing the value of existing businesses. In particular, the EVA (Economic Value Added) indicator is considered a good criterion, since if calculated correctly it is equal to NPV.

Where else can you use NPV and IRR calculations in Excel?

NPV and IRR financial specialists can use it in more applied matters. For example, in resolving the issue with banks about the real lending rate. The fact is that when issuing a loan, banks calculate the amount of annuity or, in other words, a flat payment. To plan loan payments, it is important to understand how annuity is calculated.
Let's say you are going to take out a loan of 1,000,000 rubles for 5 years at 10% per annum. You will pay once a year in equal installments. We will not present the formula for calculating annuity from a textbook on financial management here. Here's the Excel formula:

PMT – discount rate; number of periods; the loan amount you take out.

There are two more optional points in the formula. The amount that should remain (zero by default) and how to calculate the amount: at the beginning of the month - set 1, at the end of the month - zero. In most cases, these items are not needed, so they can be omitted at all. The total annuity amount is determined as follows:

The amount of the annual payment is immediately minus. This amount must be paid to the bank every year. It contains two parts:

Loan payment – we take 10% (interest on the loan) of the debt amount at the beginning of the period.
The loan body is the difference between the annual payment and the interest payment (in Excel you can find formulas that will calculate these payments for you).

The debt at the end of the month is calculated as the difference between the debt at the beginning and the payment on the loan body. If payments are not annual, but monthly or quarterly, then the rate and period must be adjusted to these values. So if we had a payment every month, the formula for calculating the annuity would look like this:

We would divide the annual rate by 12 (resulting in a monthly rate) and take not 5 periods, but 5 12 = 60 months. We receive a monthly payment equal to 21,247 rubles.

How to check banks for honesty by calculating NPV and IRR in Excel

Any flow of loan payments implies that all outflows of money are reduced to inflows at the lending rate. In other words, if we build the cash flow from the loan we received and subsequent annuity payments, we can calculate NPV and IRR from them. NPV should take zero value, and IRR should show us the real interest rate.

When the loan and its payments are calculated correctly, the NPV taken at the same interest rate is zero. And IRR shows the same rate. When the bank makes us an offer that is impossible to refuse, and which will increase the loan rate “only” by a certain percentage, do not believe it and recalculate. I will give an example of how to calculate IRR and understand the real interest on a loan.

The bank offered insurance at “only” 2% of the loan amount per year. Do you think this is an increase of only 2%? No. The fact is that real credit decreases at the beginning of each year:

The result shows that NPV is not zero. But the real percentage is not 10, but 12.9%. Please note that the overpayment amount has increased here. If you are confused by this, you may be offered “even more favorable conditions” - pay the overpayment now, and the rest later, in smaller payments, or, as in our example, simply pay more and then less. The amount of overpayment will not change, but the percentage will change significantly.

What has been done here? The amount of 43,797 rubles was taken from each subsequent payment and added to the first payment. If for the real sector financial mathematics about “money yesterday - money tomorrow” seems somewhat distant from life, for banks it is real profit. Therefore, they are already loading the first payment. And with the help of simple formulas you can track this and prepare the basis for further negotiations.

P.S. Don’t forget, when it comes to monthly payments, multiply by 12.

Excel was originally created to facilitate calculations in many areas, including business. Using its capabilities, you can quickly make complex calculations, including forecasting the profitability of certain projects. For example, Excel makes it quite easy to calculate the IRR of a project. This article will tell you how to do this in practice.

What is IRR

This abbreviation denotes the internal rate of return (IRR) of a specific investment project. This indicator is often used to compare proposals on the prospects for profitability and business growth. In numerical terms, IRR is the interest rate at which the present value of all cash flows required to implement an investment project (denoted NPV or NPV) is set to zero. The higher the GNI, the more promising the investment project.

How to evaluate

Having found out the IRR of the project, you can decide whether to launch it or abandon it. For example, if you are going to start a new business and plan to finance it with a loan taken from a bank, then calculating the IRR allows you to determine the upper acceptable limit of the interest rate. If a company uses more than one source of investment, then comparing the IRR value with their cost will make it possible to make an informed decision about the feasibility of launching a project. The cost of more than one source of financing is calculated using the so-called weighted arithmetic average formula. It is called the “Cost of Capital” or “Price of Advanced Capital” (denoted CC).

Using this indicator, we have:

IRR > CC, then the project can be launched;
IRR = СС, then the project will bring neither profit nor loss;
IRR< СС, то проект заведомо убыточный и от него следует отказаться.

How to manually

Long before the advent of computers, GNI was calculated by solving a fairly complex equation (see below).

It includes the following quantities:

CFt—cash flow for time period t;
IC - financial investments at the project launch stage;
N is the total number of intervals.

Without special programs, you can calculate the IRR of a project using the method of successive approximation or iterations. To do this, it is first necessary to select barrier rates in such a way as to find the minimum NPV values modulo, and carry out the approximation.

Solution by the method of successive approximations

First of all, you will have to switch to the language of functions. In this context, IRR will be understood as the value of return r at which NPV, being a function of r, becomes equal to zero.

In other words, IRR = r such that when substituted into the expression NPV(f(r)) it becomes zero.

Now let's solve the formulated problem using the method of successive approximations.

Iteration is usually understood as the result of repeated application of one or another mathematical operation. In this case, the value of the function calculated at the previous step becomes its argument during the next one.

The calculation of the IRR indicator is carried out in 2 stages:

calculation of IRR for extreme values of normal returns r1 and r2 such that r1< r2;
calculation of this indicator for values of r close to the IRR value obtained as a result of previous calculations.

When solving the problem, r1 and r2 are chosen in such a way that NPV = f (r) within the interval (r1, r2) changes its value from minus to plus or vice versa.

Thus, we have a formula for calculating the IRR indicator in the form of the expression presented below.

It follows from this that in order to obtain the IRR value, it is necessary to first calculate the NPV at different values of the interest rate.

There is the following relationship between NPV, PI and CC indicators:

if the NPV value is positive, then IRR > CC and PI > 1;
if NPV = 0, then IRR = CC and PI = 1;
if the NPV value is negative, then the IRR< СС и PI< 1.

Graphical method

Now that you know what IRR is and how to calculate it manually, it’s worth getting acquainted with another method for solving this problem, which was one of the most popular before computers appeared. We are talking about a graphical version of determining IRR. To construct graphs, you need to find the NPV value by substituting different values of the discount rate into the formula for calculating it.

How to Calculate IRR in Excel

As you can see, manually finding IRR is quite difficult. This requires certain mathematical knowledge and time. It is much easier to learn how to calculate IRR in Excel (see example below).

For this purpose, the well-known Microsoft spreadsheet processor has a special built-in function for calculating the internal discount rate - IRR, which gives the desired IRR value in percentage terms.

Syntax

Let's take a closer look at its syntax:

By values we mean an array or a reference to cells that contain numbers for which it is necessary to calculate the IRR, taking into account all the requirements specified for this indicator.
The guess is a value that is known to be close to the IRR result.

In Microsoft Excel, the above-described iteration method is used to calculate the VSD. It starts with a Guess value and loops through the calculations until it gets a result with an accuracy of 0.00001%. If the built-in VSD function does not produce a result after making 20 attempts, then the table processor produces an error value indicated as “#NUMBER!”.

As practice shows, in most cases there is no need to set a value for the “Assumption” value. If it is omitted, the processor considers it equal to 0.1 (10%).

If the built-in VSD function returns the error “#NUMBER!” or if the result obtained does not meet expectations, then you can perform the calculations again, but with a different value for the “Assumption” argument.

Solutions in Excel: option 1

Let's try to calculate IRR (you already know what it is and how to calculate this value manually) using the built-in IRR function. Let's say we have data for 9 years ahead, which is entered into an Excel spreadsheet.


Period (year) T	Initial costs	Cash income	Cash expense	Cash flow

The formula “=VSD (E3:E2)” is entered into the cell with address E12. As a result of its application, the spreadsheet processor returned a value of 6%.

Solutions in Excel: option 2

Using the data given in the previous example, calculate the IRR using the “Solution Search” add-on.

It allows you to search for the optimal value of IRR for NPV=0. To do this, you need to calculate the NPV (or NPV). It is equal to the sum of the discounted cash flow over the years.


Period (year) T	Initial costs	Cash income	Cash expense	Cash flow	Discount Cash Flow

Discounted cash flow is calculated using the formula “=E5/(1+$F$11)^A5”.

Then for NPV the formula “=SUM(F5:F13)-B7” is obtained.

Next, you need to find, based on optimization using the “Search for Solutions” add-on, a value of the IRR discount rate at which the NPV of the project becomes equal to zero. To achieve this goal, you need to open the “Data” section in the main menu and find the “Solution Search” function there.

In the window that appears, fill in the lines “Set target cell”, indicating the address of the NPV calculation formula, i.e. +$F$16. Then:

select the value for this cell “0”;
the parameter +$F$17 is entered into the “Cell Changes” window, i.e. the value of the internal rate of return.

As a result of optimization, the table processor will fill the empty cell with address F17 with the value of the discount rate. As can be seen from the table, the result is 6%, which completely coincides with the calculation of the same parameter obtained using the built-in formula in Excel.

MIRR

MIRR - internal rate of return of the investment project;
COFt is the outflow of funds from the project during time periods t;
CIFt - financial inflow;
r is the discount rate, which is equal to the weighted average cost of invested capital WACC;
d - % reinvestment rate;
n - number of time periods.

Calculating MIRR in a table processor

Once you become familiar with the properties of IRR (what it is and how to calculate its value graphically, you already know), you can easily learn how to calculate the modified internal rate of return in Excel.

For this purpose, the table processor has a special built-in MVSD function. Let's take the same example already discussed. How to calculate IRR for it has already been discussed. For MIRR the table looks like this.


Loan amount in percentage
Reinvestment level
Period (year) T	Initial costs	Cash income	Cash expense	Cash flow

In cell E14 enter the formula for MIRR “=MIRR(E3:E13;C1;C2)”.

Advantages and disadvantages of using the internal rate of return indicator

The method for assessing the prospects of projects by calculating IRR and comparing it with the cost of capital is not perfect. However, it does have certain advantages. These include:

The ability to compare various investment projects according to the degree of their attractiveness and the efficiency of using invested capital. For example, a comparison can be made with the return on risk-free assets.
The ability to compare different investment projects with different investment horizons.

At the same time, the shortcomings of this indicator are obvious. These include:

the inability of the internal rate of return indicator to reflect the amount of reinvestment in the project;
the difficulty of forecasting cash payments, since their value is influenced by many risk factors, the objective assessment of which is very difficult;
failure to reflect the absolute amount of income (receipts) from the size of the investment.

Note! The last drawback was resolved by maintaining the MIRR indicator, which was described in detail above.

How the ability to calculate IRR can be useful to borrowers

According to the requirements of the Russian Central Bank, all banks operating in the Russian Federation are required to indicate the effective interest rate (EPR). Any borrower can calculate it independently. To do this, he will have to use a spreadsheet processor, for example, Microsoft Excel and select the built-in VSD function. To do this, the result in the same cell should be multiplied by the payment period T (if they are monthly, then T = 12, if daily, then T = 365) without rounding.

Now, if you know what internal rate of return is, so if someone tells you, “For each of the following projects, calculate the IRR,” you will not face any difficulty.

to previous page - to the development of financial models in EXCEL

For the purposes of this article, by investment or investment investments we will understand the direction of capital in the form of money into the production infrastructure with the aim of creating in the future mass production of a new product or expanding production to create a larger volume of products, and through the sale of which the investment funds are expected to be returned. Where, in turn, as a production infrastructure we will consider all components of the cycle of creating a consumer product from ideas, scientific research and design work to the development, construction, implementation and launch of mass production and sales facilities.

As usual, we immediately post for download the corresponding financial model of the investment project (investment model) in the form of an EXCEL file:

The legend of the investment project, the modeling of which is presented in the EXCEL file, is as follows. Investment funds are invested in creating the production of certain goods; in the project they are called finished products, the profit from the sale of which is used to pay off the debt of the production enterprise to investors. The investment period is considered to be 10 years, the financial model calculations are made on a monthly basis, the user can set the beginning of the period himself from a drop-down list of the first numbers of different years.

From the point of view of the specifics of capital expenditure objects, the financial model distinguishes only that production infrastructure is purchased with investment funds, without specifying the acquisition method - be it construction from scratch or the purchase of ready-made production facilities (buildings, structures, equipment, etc.), including itself a warehouse for raw materials and finished products, as well as areas for personnel, including office spaces for management personnel, i.e. There are no plans to rent offices, just as there are no capital investments in trade and logistics infrastructure - there are no stores or vehicles of our own - outsourced logistics.

The financial model allows you to set the level of maximum output of finished products per month, while the sales plan in the quantity of finished products is set regardless of the maximum output of production. Therefore, the investment model provides for the possibility of expanding or scaling production through capital investments in additional production modules similar to the original one, the source of financing of which is also investments. That is, it is assumed that if in any future period, in order to fulfill the sales plan, it is necessary to produce products in a volume exceeding the maximum output of one production module, then the financial model automatically calculates investment capital costs for the creation of additional production capacities, thereby forming the investment flow of the model.

The EXCEL file with the investment model consists of 10 tabs, the first of which, in the “table of contents” tab, contains a list indicating the contents of the remaining 9 tabs and hyperlinks to them (the financial model file provides the ability to move between tabs using hyperlinks through the table of contents ), briefly it looks like this:

In the “conditions” sheet, the user of the financial model sets the basic initial conditions for modeling the investment project. It all starts with setting the project start date in cell M11 from the drop-down list of dates (the set of dates in this list can be changed in the “structure” tab, column D). After which, in lines 7, 8 and 9, the investment period is automatically divided into 10 subsequent years.

Line 13 of the “conditions” sets the total annual capital costs in thousands of rubles for the infrastructure of one production module, from which the project begins operational functioning.

Lines 15 through 48 provide a monthly breakdown of the total capital expenditures from line 13. The following three splitting methods are provided here, the selection of which is carried out through the drop-down list of cell M11 with the following values (the names of which can be changed in the “structure” tab in column G):

Evenly throughout the year;

Evenly from... to...;

Manually.

If you select the “evenly across the year” method in lines 35 to 46, the total amounts from line 13 are simply divided by 12 for each month of the corresponding year. When choosing the “evenly from... to...” method in lines 17 and 18, you must select from the list the numbers of the months of the beginning and end of capital investments of a given year, after which the financial model will automatically distribute the total volumes of capital investments evenly over the selected months in the block of lines from 35 to 46th. Finally, when choosing the “manual” method, you will be asked in lines 21 to 32 to simply set the percentage distribution by month of the year of the total amounts of capital expenditures.

In line 48, the number of the month of commissioning of capital expenditure objects is specified, and if this number does not coincide with the month number of the last non-zero monthly volumes of capital expenditures, then commissioning will be calculated in two stages: first, the entire volume up to the specified month will be commissioned, including it, then in the last month of non-zero volumes the remaining volume of non-current assets will be put into operation.

Cell Q50 specifies the number of years of depreciation for production assets.

In lines 52 to 56, restrictions are set on the production and storage capacity of one production module: the maximum monthly output of finished products is set in cell Q52, the volume of the own warehouse of finished products is set in cell Q54, as a percentage of the maximum monthly output, volume own warehouse of raw materials and supplies - in cell Q56 (in specific terms per unit of finished product).

In the block of lines from 58 to 77, the sales plan is set in the number of units of finished products, as the product of the number of potential clients/buyers in the sales region (line 58), the forecast target share of the sales market that the enterprise plans to occupy in the region (line 60) and the average number of units that the customer will buy (line 62).

The operating cycle of our enterprise consists of a production cycle and a trading cycle, the first of which begins with the purchase of raw materials and materials and ends with the release of finished products and their placement in our own warehouse. The trading cycle begins with the placement of finished products in the enterprise's warehouse and ends with their sale to customers. The corresponding turnover periods or lengths of the specified cycles are specified in lines 79 and 81.

Indicators such as the budget for the purchase of raw materials and materials, the budget for the production of finished products, the balances at the end of the period of inventories of raw materials and materials, as well as finished products, are calculated in the financial model under consideration from the point of view of the classical approach, through turnover ratios, starting from the sales budget.

GP = GP 1 - GP 0 + S,

where GP 1 and GP 0 are the balances of finished products at the end and beginning of the period, respectively, S – sales of finished products for the period.

In this case, the volume of finished products of GP 1 at the end of the period is calculated using the following formula (can be found in any textbook):

GP 1 = [ 2 * S * P about (GP) / number of days of period ] - GP 0 ,

which follows from the classic definition of the coefficient and turnover period of finished product inventories, where P ob (GP) is the turnover period of finished products specified in line 79 of the “conditions” tab.

Naturally, setting the volume of reserves of GP 0 for the first period of our investment model equal to zero, we sequentially apply the above formulas for each next period, and in our case these are months (see the “monthly” tab), we obtain production volumes for the period and balances of GP reserves at the end period.

Similarly, the volumes of purchases of raw materials and supplies for the period, as well as inventories at the end of the periods of raw materials and materials are calculated through a pre-calculated plan for the production of finished products and the turnover period of inventories of raw materials and supplies relative to the output of finished products specified in line 81.

Further, we will not describe in such detail the methodology of the technical structure of the file with the financial model, since we believe that it would be fair to offer the user interested in this to pay 750 rubles for a detailed modeling methodology. A request for a paid investment modeling methodology can be left through feedback or by calling us by phone, see contacts in the upper right corner of the page.

We only note that we use formulas with the turnover of inventories and finished products in the financial model for sales expressed in natural values, i.e. in the number of units of finished products, after which by setting in lines 83, 85 and 104, respectively, the planned cost of production, projected inflation within the cost of raw materials and materials, as well as the sales margin, we obtain a procurement budget, a production plan and the balance of inventories of raw materials and materials and finished goods products in money, in our case in thousands of rubles.

All calculations of the investment model occur in monthly detail in the “monthly” tab, after which the data enters the sheets of our EXCEL file with the final forms of financial management reporting, see the table of contents above in the text.

The profit and loss statement or, as it has now become customary to say, “the financial result report (P&L) in the financial model is presented in two versions, namely in the form of marginal P&L and functional P&L - the “PL_m” and “PL_f” tabs.

In the marginal income statement, all expenses are divided into variable and fixed, i.e. for those items that depend on certain income indicators and for items that do not directly depend on them. The Margin P&L template/format can be found below:

In a functional income statement, all expenses are divided according to the principle of functional affiliation, i.e. The division of expense items occurs according to the approved organizational structure of the enterprise; the template/format of the functional P&L can be found below:

The cash flow statement template/format used in the financial model is as follows:

We will not post the balance sheet format, because if the reader is already interested in our financial model of the investment project, then he will probably download it and familiarize himself with it (with the balance sheet format/template).

We will turn to the “NPV” tab, in which NPV analysis is carried out using EXCEL formulas, where indicators such as are calculated:

NPV of investments (Inv);

NPV of DS flow (CF);

Return on investment;

PP - payback period;

NPV CF after the investment period;

PI of the investment period;

NPV full;

PI full;

ROI of the investment period;

ROI is complete;

Average discount rate for an investment project;

IRR - internal rate of return.

The format of the report with NPV analysis of the investment project is as follows:

So we're looking at the NPV tab. In the 12th line of this tab, from the Cash Flow report of the “CF” tab, using EXCEL formulas, we enter data on the flow of investments into the project for capital expenditures to create the number of production modules that are necessary in accordance with the finished product sales plan.

In line 14, discount factors are calculated for each year based on data from line 188 of the “conditions” tab, where the user, in turn, manually enters forecast financial flow discount rates separately for each year of the investment project; we remind you that these rates in general can be different from year to year.

The present value of investments is calculated in line 16 by summing the successive quotients of division for each year of line 12 with the nominal volumes of investments by the corresponding discount factors from line 14.

Further, in lines 20 and 24, the present values of the inflows and outflows of project funds are calculated in a similar way, without taking into account the repayment of investment loans, but taking into account the financial activities of the enterprise, if the top management of the latter had to attract loans to replenish working capital of operating activities.

The model under consideration assumes that investments are directed only to production infrastructure, and to maintain operating activities in the event of cash gaps, the top management of the enterprise must itself find the opportunity to attract appropriate loans and borrowings, the volumes of which our financial model calculates automatically through the overdraft lending model and takes into account these operations within the block with cash flow for financial activities in the Cash Flow report.

In other words, in line 26 the reduced financial flow for core and financial activities is calculated or, which is the same, the financial flow for the operating activities of the enterprise, which is exactly what needs to be compared with the reduced investment flow from line 16, which is done in cell M29, in which the specified difference is calculated. Using EXCEL conditional formatting, cell M29 is colored green if its value is greater than zero, i.e. the project is solvent (the normalized financial flow from operating activities is greater than the normalized investment flow), and is colored red; if, on the contrary, the project is unprofitable, all other things being equal, from the investor’s point of view.

In cells M31 and M33, the values of PI and ROI indicators are calculated:

PI - Profitability Index - Investment Profitability Index - equal to the ratio of the reduced financial flow from operating activities to the present value of investment investments - obviously, it must be greater than one for the project to be proposed for consideration;

ROI - Return On Investment - Return on investment - is equal to the ratio of NPV to the present value of investment.

In line 35, the return on investment flow is calculated, and therefore the indicator is calculated in cell M39

PP - Payback Period - The period of return or payback of investments is equal to the number of years, weighted by the amount of return, after which the investor will receive back all the money invested in the project, taking into account the specified discount level.

Next, in the block of lines from 41 to 53, all the previously given indicators of the effectiveness of the investment project are calculated for the case if the investor decides not to leave the project, but to leave it for himself “forever”, see the corresponding formulas in the first part of this section . Here, the average financial flow calculated for the period following the payback period PP is considered as the annual financial flow for the period after 10 years of the project’s existence.

Finally, in cell M56, and in fact in the block of lines from 61 to 160, by applying the dichotomy method, as we noted above, the approximate value of the investment project efficiency indicator IRR - internal rate of return - is calculated. And in order to have something to compare it with, if from year to year the user of the model sets different discount rates, we present in cell M56, with calculation in the block of lines from 161 to 260, the average unified discount rate for each year of the investment project .

In conclusion, we note that outside the material presented in this article, we consciously exclude a discussion of such important concepts as EBITDA and EVA, as well as the market value of net assets, the philosophy of which forms an adequate understanding of what effective management is from the point of view of the system for accepting investment, and not only, solutions. We also note that the view of management through these concepts is similar, for example, to the management system called “lean manufacturing”, which was formed in Japan. In this case, we are not just talking about formulas through which the average person is formally given an idea of these indicators and related concepts, we are talking about how to implement appropriate highly effective management systems in the real life of an enterprise, which is the basic starting point for any investor when forming a team of top managers to manage the project. We will post this material in the near future in the section devoted to the laws of effective management.