Both concepts from the title of this section, discounted (present) value, PS (presentvalue, or PV ), And net present value, NPV (netpresentvalue, or NPV ), denote current the value of expected future cash receipts.

As an example, consider valuing an investment that promises an income of $100 per year at the end of this year and the next four years. We assume that this series of five payments of $100 each is guaranteed and the money will certainly arrive. If a bank were to pay us 10% annual interest on a five-year deposit, then that 10% would be the opportunity cost of the investment—the benchmark rate of return against which we would compare the benefits of our investment.

You can calculate the value of an investment by discounting its cash flows using opportunity cost as the discount rate.

Calculation formula inExceldiscounted (present) value (PV)= NPV(C1,B5:B9)

Present value(PS) in the amount of $379.08 is the current value of the investment.

Suppose this investment were to sell for $400. Obviously, it would not be worth the asking price because - assuming an opportunity return (discount rate) of 10% - the real value of this investment would be only $379.08. it is appropriate to introduce the concept net present value(NPS). Denoted by the symbol r discount rate for this investment, we get the following NPV formula:

Where CF t is the cash flow from the investment at time t; CF 0 – flow of funds (receipt) at the current moment.

Calculation formula inExcel net present value (NPV)= NPV(C1,B6:B10)+B5

Excel terminology regarding discounted flows Money, differs somewhat from standard financial terminology. In Excel, the abbreviation MUR (NPV) denotes present value (not chiI'm standing present value) of a series of cash receipts.

To calculate in Excel net present value series of cash receipts in the usual sense of financial theory, you must first calculate present value future cash receipts (using such Excel functions, as “NPV”), and then subtract the cash flow from this number by starting moment time. (This value is often the same as the value of the asset in question.)

Let's calculate the Net Present Value and Internal Rate of Return using the formulasMSEXCEL.

Let's start with a definition, or rather with definitions.

Net present value (NPV) is called the sum of discounted values ​​of the payment stream reduced to today(taken from Wikipedia).
Or like this: Net present value is the current value of future cash flows of an investment project, calculated taking into account discounting, minus investments (websitecfin.ru)
Or like this: Currentthe cost of a security or investment project, determined by taking into account all current and future income and expenses at the appropriate interest rate. (Economy . Dictionary . - M . : " INFRA - M ", Publishing house " The whole world ". J . Black .)

Note1. Net present value is also often called Net Present Value, Net Present Value (NPV). But, because the corresponding MS EXCEL function is called NPV(), then we will adhere to this terminology. In addition, the term Net Present Value (NPV) clearly indicates a connection with.

For our purposes (calculation in MS EXCEL), we define NPV as follows:
Net present value is the sum of cash flows presented in the form of payments of arbitrary amounts made at regular intervals.

Advice: when first becoming acquainted with the concept of Net Present Value, it makes sense to get acquainted with the materials of the article.

This is a more formal definition without reference to projects, investments and securities, because this method can be used to evaluate cash flows of any nature (although, in fact, the NPV method is often used to evaluate the effectiveness of projects, including for comparing projects with different cash flows).
Also, the definition does not contain the concept of discounting, because The discounting procedure is, in essence, the calculation of present value using the method.

As mentioned, in MS EXCEL, the NPV() function is used to calculate the Net Present Value (NPV()). It is based on the formula:

CFn is the cash flow (money amount) in period n. The total number of periods is N. To show whether a cash flow is an income or an expense (investment), it is written with a certain sign (+ for income, minus for expenses). The value of cash flow in certain periods can be =0, which is equivalent to the absence of cash flow in a certain period (see note 2 below). i is the discount rate for the period (if the annual interest rate is given (let it be 10%), and the period is equal to a month, then i = 10%/12).

Note2. Because cash flow may not be present in every period, then NPV determination you can clarify: Net present value is the present value of cash flows presented in the form of payments of an arbitrary value, made at intervals that are multiples of a certain period (month, quarter or year). For example, initial investments were made in the 1st and 2nd quarters (indicated with a minus sign), there were no cash flows in the 3rd, 4th and 7th quarters, and in the 5th, 6th and 9th quarters revenue from the project has been received (indicated with a plus sign). For this case, NPV is calculated in exactly the same way as for regular payments (amounts in the 3rd, 4th and 7th quarters must be indicated =0).

If the sum of the reduced cash flows representing income (those with a + sign) is greater than the sum of the reduced cash flows representing investments (expenses, with a minus sign), then NPV > 0 (the project/investment pays off). Otherwise NPV<0 и проект убыточен.

Selecting the discount period for the NPV() function

When choosing a discount period, you need to ask yourself the question: “If we are forecasting 5 years in advance, can we predict cash flows with an accuracy of up to a month / up to a quarter / up to a year?”
In practice, as a rule, the first 1-2 years of receipts and payments can be predicted more accurately, say monthly, and in subsequent years the timing of cash flows can be determined, say, once a quarter.

Note3. Naturally, all projects are individual and there can be no single rule for determining the period. The project manager must determine the most likely dates for the receipt of amounts based on current realities.

Having decided on the timing of cash flows, for the NPV() function you need to find the shortest period between cash flows. For example, if in the 1st year receipts are planned monthly, and in the 2nd year quarterly, then the period should be chosen equal to 1 month. In the second year, the amounts of cash flows in the first and second months of the quarters will be equal to 0 (see. example file, NPV sheet).

In the table, NPV is calculated in two ways: through the NPV() function and by formulas (calculating the present value of each amount). The table shows that already the first amount (investment) is discounted (-1,000,000 turned into -991,735.54). Let's assume that the first amount (-1,000,000) was transferred on January 31, 2010, which means its present value (-991,735.54=-1,000,000/(1+10%/12)) is calculated as of December 31, 2009. (without much loss of accuracy we can assume that as of 01/01/2010)
This means that all amounts are given not as of the date of transfer of the first amount, but at an earlier date - at the beginning of the first month (period). Thus, the formula assumes that the first and all subsequent amounts are paid at the end of the period.
If it is required that all amounts be given as of the date of the first investment, then it does not need to be included in the arguments of the NPV() function, but simply added to the resulting result (see example file).
A comparison of 2 discounting options is given in the example file, NPV sheet:

About the accuracy of calculating the discount rate

There are dozens of approaches for determining the discount rate. Many indicators are used for calculations: the weighted average cost of capital of the company; refinancing rate; average bank deposit rate; annual inflation rate; income tax rate; country risk-free rate; premium for project risks and many others, as well as their combinations. It is not surprising that in some cases the calculations can be quite labor-intensive. The choice of the right approach depends on the specific task; we will not consider them. Let us note only one thing: the accuracy of calculating the discount rate must correspond to the accuracy of determining the dates and amounts of cash flows. Let's show the existing dependency (see. example file, sheet Accuracy).

Let there be a project: implementation period is 10 years, discount rate is 12%, cash flow period is 1 year.

NPV amounted to 1,070,283.07 (Discounted to the date of the first payment).
Because If the project period is long, then everyone understands that the amounts in years 4-10 are not determined precisely, but with some acceptable accuracy, say +/- 100,000.0. Thus, we have 3 scenarios: Basic (the average (most “probable”) value is indicated), Pessimistic (minus 100,000.0 from the base) and Optimistic (plus 100,000.0 to the base). You must understand that if the base amount is 700,000.0, then the amounts of 800,000.0 and 600,000.0 are no less accurate.
Let's see how NPV reacts when the discount rate changes by +/- 2% (from 10% to 14%):

Consider a 2% rate increase. It is clear that as the discount rate increases, NPV decreases. If we compare the ranges of NPV spread at 12% and 14%, we see that they intersect at 71%.

Is it a lot or a little? Cash flow in 4-6 years is predicted with an accuracy of 14% (100,000/700,000), which is quite accurate. A change in the discount rate by 2% led to a decrease in NPV by 16% (when compared with the base case). Taking into account the fact that the NPV spread ranges overlap significantly due to the accuracy of determining the amounts of cash income, an increase of 2% in the rate did not have a significant impact on the NPV of the project (taking into account the accuracy of determining the amounts of cash flows). Of course, this cannot be a recommendation for all projects. These calculations are provided as an example.
Thus, using the above approach, the project manager must estimate the costs of additional calculations of a more accurate discount rate, and decide how much they will improve the NPV estimate.

We have a completely different situation for the same project, if the discount rate is known to us with less accuracy, say +/- 3%, and future flows are known with greater accuracy +/- 50,000.0

An increase in the discount rate by 3% led to a decrease in NPV by 24% (when compared with the base case). If we compare the ranges of NPV spread at 12% and 15%, we see that they intersect only by 23%.

Thus, the project manager, having analyzed the sensitivity of NPV to the discount rate, must understand whether the NPV calculation will be significantly refined after calculating the discount rate using a more accurate method.

After determining the amounts and timing of cash flows, the project manager can estimate what maximum discount rate the project can withstand (NPV criterion = 0). The next section talks about the Internal Rate of Return - IRR.

Internal rate of returnIRR(VSD)

Internal rate of return internal rate of return, IRR (IRR)) is the discount rate at which the Net Present Value (NPV) is equal to 0. The term Internal Rate of Return (IRR) is also used (see. example file, IRR sheet).

The advantage of IRR is that in addition to determining the level of return on investment, it is possible to compare projects of different scales and different durations.

To calculate IRR, the IRR() function is used (English version - IRR()). This function is closely related to the NPV() function. For the same cash flows (B5:B14), the rate of return calculated by the IRR() function always results in a zero NPV. The relationship of functions is reflected in the following formula:
=NPV(VSD(B5:B14),B5:B14)

Note4. IRR can be calculated without the IRR() function: it is enough to have the NPV() function. To do this, you need to use a tool (the “Set in cell” field should refer to the formula with NPV(), set the “Value” field to 0, the “Changing cell value” field should contain a link to the cell with the rate).

Calculation of NPV with constant cash flows using the PS() function

Internal rate of return NET INDOH()

Similar to NPV(), which has a related function, IRR(), NETNZ() has a function, NETINDOH(), which calculates the annual discount rate at which NETNZ() returns 0.

Calculations in the NET INDOW() function are made using the formula:

Where, Pi = i-th amount of cash flow; di = date of the i-th amount; d1 = date of 1st amount (starting date to which all amounts are discounted).

Note5. The function NETINDOH() is used for .

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:

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 time period 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.

The current value of the asset.

The present value of the object's future cash flows.

PV and FV are related by a simple relationship:

FV = PV (1 + r)n
PV = FV (1 + r) -n(1)

Usage example:

PV = $100,000/(1 + 1.08) 6 = $63,016

Present value of future equal payments(present value of a series of equal cash flow) is calculated using formula (2):

Example task:
There is a financial asset that will bring you $1000 per year in income for 20 years starting one year from now, at a market rate of 12%. Estimate the current value of the asset. In this case, the values ​​can simply be substituted into the formula.

If an asset begins to generate income of 1000 from the first day of its acquisition, then instead of 20 we insert 19 into the formula and simply add 1000 to the resulting value.

Calculation of present value when payments start from a certain date in the future (Tx).

In this case, you need to use formula (2) to calculate PV at the moment Tx, and then calculate PV at the current moment using formula (1), where PV(Tx) becomes the usual FV.

Present value of the sum of regular infinite cash flows It is calculated very simply:

The present value of heterogeneous cash flows is calculated as the sum of individual discounted income:

Measuring FV and PV is useful for comparing alternative investment methods because the assessment of flows should be carried out at the same points in time - at the end of the investment horizon (FV) or at the beginning (PV).

When considering various investment projects, there is a need for an objective assessment of their effectiveness. Calculation of the net present value indicator (NPV, NPV - “net present value” - English) helps to cope with this task.

This is the sum of the differences between expected cash receipts and project costs, discounted at a given interest rate. Thus, NPV shows the value of future cash flows, reduced to today, which allows you to objectively assess the profitability of the investment plan.

The calculation of the indicator must be performed in stages:

1. Find the difference between projected profit and investment costs for each period of time (usually a year).
2. Determine the discount rate by determining the cost of capital.
3. Bring the results obtained to today - discount cash flows separately for each period.
4. Find the sum of all discounted cash flows (both negative and positive). This value will constitute the NPV, which shows the investor’s total profit.

Necessity of calculation

Calculating net present value is one of the most popular methods for predicting the effectiveness of investment programs. Assessing the value of this indicator allows us to answer the main question for an entrepreneur: “Should I invest money in the project or not?”

The need to determine NPV is due to the fact that the coefficient allows not only to estimate the amount of predicted profit, but also to take into account the fact that any amount of money at the current time has a greater real value than the same amount in the future.

So, for example, instead of investing in a project, an entrepreneur can:

• Open a deposit account in a bank and receive an annual profit in accordance with the interest rate.
• Purchase property whose value will increase in the future by the amount of inflation.
• Hide money.

Therefore, the indicator is calculated using a given discount percentage rate, which allows take into account inflation and risk factors, as well as evaluate the effectiveness of the project in comparison with alternative investment options.

Formula and calculation examples

The formula for calculating NPV is as follows:

• t, N – number of years or other time periods;
• CF t – cash flow for period t;
• IC – initial investment;
• i – discount rate.

In order to correctly understand the methodology for calculating this indicator, let’s consider it using a practical example.

Let's say an investor is considering the possibility of implementing two projects - A and B. The program implementation period is 4 years. Both options require an initial investment of RUB 10,000. However, the projected cash flows of the projects differ greatly and are presented in the table:

Thus, project A assumes maximum profit in the short term, and project B implies its gradual increase.

Let's determine the NPV of projects at a given discount rate of 10%:

Due to the fact that discount factors become smaller with each subsequent year, the contribution of larger, but more distant cash flows to the total net present value decreases. Therefore, the NPV of project B is less than the corresponding value of project A.

The step-by-step calculation process is discussed in detail in the following video:

Analysis of the result

The main rule that is relied upon when assessing the effectiveness of investments using the NPV method is the project should be accepted if the indicator value is positive. If this value is negative, then the investment plan is unprofitable.

If the indicator turns out to be 0, it is necessary to understand that the income cash flows from the implementation of the program are capable of reimbursing the costs, but nothing more.

Let's go back to the example above. The NPV of both projects turned out to be positive, which suggests that the investor can invest in any of them, because they are capable of generating profit. However, the NPV for project A exceeds the same value for project B, which indicates its greater efficiency. It is investing in the first project that is most profitable for an entrepreneur - after 4 years of implementation with an initial cost of 10,000 rubles. it is able to bring a net profit of 788.2 rubles.

Thus, it is worth remembering: the higher the NPV of an investment, the higher its efficiency and profitability.

Advantages and disadvantages of the method

Despite the advantages of the method, such as taking into account changes in the value of funds over time and taking into account risks, you should remember a number of limitations:

• All indicators used in the calculations are predictive in nature and remain stable throughout the entire duration of the program. In reality, they can vary significantly from the given values, which makes the final value only a probabilistic parameter.
• Discount rates are often adjusted taking into account possible risks, which is not always justified and leads to an unreasonable decrease in the final NPV value. In this regard, the investor may refuse to implement a profitable project.

Thus, the NPV calculation method makes it possible to easily and qualitatively assess the likely profitability of investments given to the current point in time.

However, it is worth remembering that this technique is predictive in nature and is only suitable in a stable economic situation.