The net present value indicator shows. Net present value NPV. NPV calculation in Excel


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:


We know we want to save $100,000 within 6 years. We know that the deposit rate is 8% per annum, which means we can calculate the required initial investment volume to receive the required payment:

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

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;

CFt (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.

Methods Advantages Initial data for calculation
CAPM model Taking into account the impact of market risk on the discount rate
WACC model The ability to take into account the efficiency of using both equity and borrowed capital Quotations of ordinary shares (MICEX exchange), interest rates on borrowed capital
Gordon model Accounting for dividend yield Quotations of ordinary shares, dividend payments (MICEX exchange)
Ross model Taking into account industry, macro and micro factors that determine the discount rate Statistics on macroindicators (Rosstat)
Fama and French model Taking into account the impact on the discount rate of market risks, the size of the company and its industry specifics Quotations of ordinary shares (MICEX exchange)
Based on market multiples Accounting for all market risks Quotations of ordinary shares (MICEX exchange)
Based on return on equity Accounting for the efficiency of using equity capital Balance sheet
Based on expert assessment The ability to evaluate venture projects and various factors that are difficult to formalize Expert assessments, rating and point scales

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.

Types of risk Risk adjustment
Risk of seasonality affecting sales 5%
Industry risk 7%
Personnel risk 3%
15%
Risk-free interest rate 5%
Total: 20%

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

Summary

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.


The concept of "net present value" usually pops up in consciousness when it is necessary to evaluate the feasibility of certain things.

There are mathematically based theses that involve the concept (pure) and which are worth sticking to whenever you have the idea of ​​\u200b\u200bforking out for this or that.

To understand what is net present value, we will analyze in detail a specific (hypothetical) example.

To do this, we will have to recall some basic information related to the topic of present value, which we already discussed on the pages.

So, an example.

Net Present Value: Introduction

Suppose you inherited a plot of land worth 23 thousand dollars. Plus, there are about 280 thousand “green” lying around in your accounts.

Total - 303 thousand dollars, which would be nice to put somewhere.

An investment option looms on the horizon, the price of which, as experts suggest, should skyrocket in a year.

Let’s assume that the cost of constructing a certain building is $280 thousand, acceptable to us, and the expected selling price of an already completed building is about $330 thousand.

If it turns out that the present value of $330,000 is greater than the amount of money you spent ($280,000 + $23,000 = $303,000), then you should agree to the proposal to build the facility.

In this case, the difference between both quantities will be the very net present value that we are so striving to find.

To begin with, however, we will have to deal with intermediate calculations aimed at establishing the value of the present value.

How to calculate present value

Obviously, the $330 thousand we will receive in the future is worth less than the $330 thousand we have today. And it's not just about .

The main reason for this state of affairs is that we can invest the available 330 thousand dollars in risk-free instruments like banking or government ones.

In this case, to determine the “true” value of our 330 thousand dollars, it is necessary to add to them the income on the corresponding deposit ().

You can look at this situation like this: today's 330 thousand dollars will cost the same amount in the future plus interest income on risk-free financial instruments.

We are very close to understanding one of the most important theories: TODAY are worth EXPENSIVE than the money we get TOMORROW.

This is why the present value of any future income will be LESS its nominal value, and to find it, you need to multiply the expected income by some, obviously LESS units.

This coefficient is usually called discount factor.

To do this, let us introduce into the problem conditions the interest rate on risk-free financial instruments, equal to, for example, 8 percent per annum.

In this case, the discount rate will be equal to the value of the fraction 1 / (1 + 0.08):

DF = 1 / (1 + 0.08) = 1 / 1.08 = 0.926.

We calculate the present value of 330 thousand dollars as follows:

PV =DF*C 1 = 0.926 * $330,000 = $305,580.

Opportunity Cost

Now let's remember what we were talking about at the beginning of our conversation.

If the size of our investment turns out to be less than the present value of the income we expect, then the corresponding offer is PROFITABLE, and it should be accepted.

As you can see, $303,000.< 305 580 долл., а значит, строительство офиса на нашем участке (скорее всего) окажется вложением…

What we have just done sounds like this in financial language: discounting future income at a rate that other (alternative) financial instruments can “offer.”

The indicated rate of return can be called differently: profitability ratio, discount rate, marginal return, opportunity cost, opportunity cost.

All marked options are equally used, and their choice depends on the context.

It is worth paying attention to the term "opportunity cost", since it emphasizes the very essence of the current value of money, income, etc.

You'll just carry LOSSES, equal to opportunity costs.

About all this (and more) another time.

Additional information on the topic is presented in the articles:
1. ,
2. .

Happy investment!

Let's calculateReduced (to the current moment) costinvestments with different methods of calculating interest: using the simple interest formula, compound interest, annuity and in the case of payments of an arbitrary amount.

Present Value is calculated based on the concept of time value of money: money available now is worth more than the same amount in the future due to its potential to provide income. The calculation of the Present Value is also important, since payments made at different points in time can be compared only after bringing them to one point in time.
The current value is obtained as a result of reducing Future income and expenses to the initial period of time and depends on the method by which interest is calculated: , or (the example file contains a solution to the problem for each method).

Simple interest

The essence of the simple interest method is that interest is accrued throughout the entire investment period on the same amount (interest accrued for previous periods is not capitalized, i.e. interest is not accrued on them in subsequent periods).

In MS EXCEL, the abbreviation PS is used to denote Present Value (PV appears as an argument in numerous financial functions of MS EXCEL).

Note. MS EXCEL does not have a separate function for calculating Present Value using the Simple Interest method. The PS() function is used for calculations in the case of compound interest and annuity. Although, by specifying the value 1 as the Nper argument, and specifying i*n as the rate, you can force PS() to calculate the Present Value using the simple interest method (see example file).

To determine the Present Value when calculating simple interest, we use the formula for calculating (FV):
FV = PV * (1+i*n)
where PV is Present Value (the amount that is currently invested and on which interest is accrued);
i - interest rate during the period interest calculations (for example, if interest is accrued once a year, then annual; if interest is accrued monthly, then per month);
n is the number of time periods during which interest is accrued.

From this formula we get that:

PV = FV / (1+i*n)

Thus, the procedure for calculating Present Value is the opposite of calculating Future Value. In other words, with its help we can find out how much amount we need to invest today in order to receive a certain amount in the future.
For example, we want to know how much we need to open a deposit for today in order to accumulate 100,000 rubles in 3 years. Let the bank have a deposit rate of 15% per annum, and interest is accrued only on the principal amount of the deposit (simple interest).
In order to find the answer to this question, we need to calculate the present value of this future amount using the formula PV = FV / (1+i*n) = 100000 / (1+0.15*3) = 68,965.52 rubles. We received that today's (current, real) amount is 68,965.52 rubles. equivalent to the amount after 3 years in the amount of 100,000.00 rubles. (at the current rate of 15% and calculated using the simple interest method).

Of course, the Present Value method does not take into account inflation, bank bankruptcy risks, etc. This method works effectively for comparing amounts “all other things being equal.” For example, that it can be used to answer the question “Which bank offer is more profitable to accept in order to receive the maximum amount in 3 years: open a deposit with simple interest at a rate of 15% or with compound interest with monthly capitalization at a rate of 12% per annum”? To answer this question, consider calculating Present Value when calculating compound interest.

Compound interest

When using compound interest rates, the interest money accrued after each compounding period is added to the amount owed. Thus, the basis for compounding, as opposed to using, changes in each compounding period. Adding accrued interest to the amount that served as the basis for its accrual is called capitalization of interest. This method is sometimes called "percentage on interest".

The present value of PV (or PS) in this case can be calculated using.

FV = РV*(1+i)^n
where FV (or S) is the future (or accumulated amount),
i - annual rate,
n is the loan term in years,

those. PV = FV / (1+i)^n

When capitalizing m times a year, the Present Value formula looks like this:
PV = FV / (1+i/m)^(n*m)
i/m is the rate for the period.

For example, the amount is 100,000 rubles. in the current account in 3 years is equivalent to today's amount of 69,892.49 rubles. at the current interest rate of 12% (% accrued monthly; no replenishment). The result was obtained by the formula =100000 / (1+12%/12)^(3*12) or by the formula =PS(12%/12;3*12;0;-100000).

Answering the question from the previous section “Which bank offer is more profitable to accept in order to receive the maximum amount in 3 years: open a deposit with simple interest at a rate of 15% or with compound interest with monthly capitalization at a rate of 12% per annum”? we need to compare two Present values: 69,892.49 rubles. (compound interest) and 68,965.52 rub. (simple interest). Because The present value calculated according to the bank’s offer for a deposit with simple interest is less, then this offer is more profitable (today you need to invest less money in order to receive the same amount of 100,000.00 rubles in 3 years)

Compound interest (multiple amounts)

Let us determine the present value of several amounts that belong to different periods. This can be done using the PS() function or the alternative formula PV = FV / (1+i)^n

By setting the discount rate to 0%, we simply obtain the sum of the cash flows (see example file).

Annuity

If, in addition to the initial investment, additional equal payments (additional investments) are made after equal periods of time, then the calculation of the Present Value becomes significantly more complicated (see the article, which shows the calculation using the PS() function, as well as the derivation of an alternative formula).

Here we will analyze another task (see example file):

The client opened a deposit for a period of 1 year at a rate of 12% per annum with monthly interest accrual at the end of the month. The client also makes additional contributions in the amount of 20,000 rubles at the end of each month. The value of the deposit at the end of the term reached 1,000,000 rubles. What is the initial deposit amount?

The solution can be found using the PS() function: =PS(12%/12;12;20000;-1000000;0)= 662,347.68 rub.

Argument Bid indicated for the period of accrual of interest (and, accordingly, additional contributions), i.e. per month.
Argument Nper– is the number of periods, i.e. 12 (months), because the client opened a deposit for 1 year.
Argument Plt- this is 20,000 rubles, i.e. the amount of additional contributions.
Argument Bs- this is -1000000 rub., i.e. future value of the deposit.
The minus sign indicates the direction of cash flows: additional contributions and the initial deposit amount are of the same sign, because client lists these funds to the bank, and the future amount of the client’s deposit will receive from the bank. This very important note applies to everyone, because... otherwise, you may get an incorrect result.
The result of the PS() function is the initial deposit amount, it does not include the Present value of all additional contributions of 20,000 rubles. This can be verified by calculating the present value of additional contributions. There were 12 additional contributions in total, the total amount was 20,000 rubles * 12 = 240,000 rubles. It is clear that at the current rate of 12%, their present value will be less = PS(12%/12;12;20000) = -225,101.55 rub. (up to sign). Because these 12 payments made over different periods of time are equivalent to RUB 225,101.55. at the time of opening the deposit, they can be added to the initial deposit amount calculated by us, 662,347.68 rubles. and calculate their total Future Value = BS(12%/12;12;; 225,101.55+662,347.68)= -1000000.0 rub., which is what needed to be proven.

In this article we will look at what net present value (NPV) is, what economic meaning it has, how and by what formula to calculate net present value, and consider some calculation examples, including using MS Exel formulas.

What is Net Present Value (NPV)?

When investing money in any investment project, the key point for the investor is to assess the economic feasibility of such an investment. After all, the investor strives not only to recoup his investment, but also to earn something more than the amount of the initial investment. In addition, the investor’s task is to search for alternative investment options that, under comparable risk levels and other investment conditions, would bring higher profits. One of the methods of such analysis is to calculate the net present value of an investment project.

Net present value (NPV, Net Present Value) is an indicator of the economic efficiency of an investment project, which is calculated by discounting (reducing to the current value, i.e. at the time of investment) expected cash flows (both income and expenses).

Net present value reflects the investor's return (the added value to the investment) that the investor expects to receive from a project after cash inflows have paid off its initial investment costs and the periodic cash outflows associated with the project.

In domestic practice, the term “net present value” has a number of identical designations: net present value (NPV), net present effect (NPE), net present value (NPV), Net Present Value (NPV).

NPV calculation formula

To calculate NPV you need:

  1. Draw up a forecast schedule for the investment project by period. Cash flows must include both income (inflows of funds) and expenses (investments made and other costs of implementing the project).
  2. Determine the size. Essentially, the discount rate reflects the investor's marginal cost of capital. For example, if borrowed funds from a bank are used for investment, the discount rate will be the loan. If the investor’s own funds are used, then the discount rate can be taken as the interest rate on a bank deposit, the rate of return on government bonds, etc.

NPV is calculated using the following formula:

Where
NPV(Net Present Value) - net present value of the investment project;
CF(Cash Flow) - cash flow;
r- discount rate;
n— total number of periods (intervals, steps) i = 0, 1, 2, …, n for the entire investment period.

In this formula CF 0 corresponds to the volume of initial investment IC(Invested Capital), i.e. CF 0 = IC. At the same time, cash flow CF 0 has a negative value.

Therefore, the above formula can be modified:

If investments in a project are made not at once, but over a number of periods, then the investment must also be discounted. In this case, the NPV formula for the project will take the following form:

Practical Application of NPV (Net Present Value)

NPV calculation allows you to assess the feasibility of investing money. There are three possible NPV value options:

  1. NPV > 0. If the net present value is positive, then this indicates the full return on investment, and the NPV value shows the final amount of profit for the investor. Investments are appropriate due to their economic efficiency.
  2. NPV = 0. If the net present value is zero, then this indicates a return on investment, but the investor does not make a profit. For example, if borrowed funds were used, then the cash flows from the investment will make it possible to pay the creditor in full, including paying the interest due to him, but the investor’s financial position will not change. Therefore, you should look for alternative options for investing money that would have a positive economic effect.
  3. NPV< 0 . If the net present value is negative, then the investment does not pay off, and the investor in this case receives a loss. You should refuse to invest in such a project.

Thus, all projects that have a positive NPV value are accepted for investment. If an investor needs to make a choice in favor of only one of the projects under consideration, then, other things being equal, preference should be given to the project that has the highest NPV value.

NPV calculation using MS Excel

MS Exel has a NPV function that allows you to calculate net present value.

The NPV function returns the net present value of an investment using the discount rate, plus the value of future payments (negative values) and receipts (positive values).

NPV function syntax:

NPV(rate, value1, value2, ...)

Where
Bid— discount rate for one period.
Value1, value2,…- from 1 to 29 arguments representing expenses and income.

Value1, value2, ... must be evenly distributed over time, payments must be made at the end of each period.

NPV uses the order of arguments value1, value2, ... to determine the order of receipts and payments. Make sure your payments and receipts are entered in the correct order.

Let's look at an example of calculating NPV based on 4 alternative projects.

As a result of the calculations carried out project A should be rejected project B is at the point of indifference for the investor, but projects V and D should be used for investment. Moreover, if you need to select only one project, then preference should be given project B, despite the fact that the amount of undiscounted cash flows over 10 years it generates less than project G.

Advantages and disadvantages of NPV

The positive aspects of the NPV method include:

  • clear and simple rules for making decisions regarding the investment attractiveness of a project;
  • applying a discount rate to adjust the amount of cash flows over time;
  • the ability to take into account the risk premium as part of the discount rate (for riskier projects, an increased discount rate can be applied).

The disadvantages of NPV include the following:

  • difficulty in assessing complex investment projects that involve many risks, especially in the long term (adjustment of the discount rate is required);
  • the difficulty of forecasting future cash flows, the accuracy of which determines the estimated NPV value;
  • the NPV formula does not take into account the reinvestment of cash flows (income);
  • NPV reflects only the absolute value of profit. For a more correct analysis, it is also necessary to additionally calculate relative indicators, such as, for example.
@ 2024 wowway.ru - Diets and nutrition. Diagnostics and tests. Medicines. Cholesterol. Atherosclerosis