Useful Tips

NPV (net present value)

Pin
Send
Share
Send
Send


We will reveal such a concept as the net present value (NPV) of an investment project, give a definition and economic sense, on a real example, consider the calculation of 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 investments in an investment project: the amount of cash flow over the period of its implementation and reduced to current value (discounting).

Net present value. Calculation formula

where: NPV - net present value of the investment project,

IC (InvestCapital) - investment capital, represents the costs of the investor in the initial time period,

r is the discount rate (barrier rate).

So, in order to calculate NPV, it is necessary to predict future cash flows for the investment project, determine the discount rate and calculate the final value of the incomes brought to the current moment.

Making investment decisions based on the NPV criterion

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

Score valueNPVMaking decisions
NPV≤0This investment project does not provide coverage for future expenses or provides only breakeven and should be rejected from further consideration.
NPV> 0The project is attractive for investment and requires further analysis.
NPV1> NPV2Investment project (1) is more attractive at the rate of present income than the second project (2)

Calculating and Forecasting Future Cash Flow (CF) in Excel

Cash flow is the amount of cash that a company / enterprise has at a given time. Cash flow reflects the financial stability of the company. To calculate cash flow, it is necessary from the cash inflow (CI,CashInflows) funds to take out the outflow (CO,CashOutflows), the calculation formula will look as follows:

Determining the future cash flow of an investment project is very important, therefore, we will consider one of the forecasting methods using the MS Excel program. Statistical forecasting of cash flows is possible only if the investment project already exists and is functioning. That is, cash is needed to increase its capacity or its scaling. I would like to note that if the project is venture and does not have statistical data on production volumes, sales, costs, then an expert approach is used to assess future cash income. Experts correlate this project with analogues in this field (industry) and evaluate the potential for possible development and possible cash receipts.

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 us examine a simple example of forecasting future cash receipts for a project depending on advertising costs. If there is a direct correlation between these indicators, then you can predict what kind of 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. at an advertising cost of 50 rubles. In reality, the determination of the size of future revenues is affected by a much larger number of factors that 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

The calculation of the discount rate is an important task in calculating the current value of the investment project. The discount rate represents the alternative return that an investor could receive. One of the most common goals of determining the discount rate is to evaluate the value of the company.

To estimate the discount rate, methods such as: 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's consider in the table the advantages and initial data that are used for the calculation.

MethodsBenefitsThe initial data for the calculation
CAPM ModelAccounting for the impact of market risk on the discount rateQuotations of ordinary shares (MICEX)
WACC ModelThe ability to take into account the efficiency of using both own and borrowed capitalQuotations of ordinary shares (MICEX exchange), interest rates on borrowed capital
Gordon ModelAccounting for dividend yieldQuotations of ordinary shares, dividend payments (MICEX)
Ross ModelAccounting for industry, macro and micro factors determining the discount rateStatistics on macro indicators (Rosstat)
Model Famous and FrenchTaking into account the impact on the discount rate of market risks, the size of the company and its industry specificsQuotations of ordinary shares (MICEX)
Based on market multiplesAll market risksQuotations of ordinary shares (MICEX)
Based on return on equityAccounting for the use of equityBalance sheet
Based on expert judgmentThe ability to evaluate venture projects and various difficult to formalize factorsExpert assessments, rating and point scales

A change in the discount rate non-linearly affects the change in the value of the net present value, this dependence is shown in the figure below. Therefore, when choosing an investment project, it is necessary not only to compare the 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.

About the discount rate and modern methods and formulas for calculating it, you can read in more detail in my article: Discount Rate. 10 modern calculation methods.

Calculating Net Discounted Income (NPV) Using Excel

Calculate the net present value using Excel. The table below shows the table of changes in future cash flows and their discounts. So, we need to determine the discount rate for the venture investment project. Since he does not have issues of ordinary shares, there are no dividend payments, and there are no estimates of the profitability of equity and borrowed capital, we will use the method of expert estimates. The evaluation formula will be as follows:

Discount rate = Risk-free rate + risk adjustment,

We take the risk-free rate equal to the interest on risk-free securities (GKO, OFZ, these interest rates can be viewed on the CBR website, cbr.ru) equal to 5%. And adjustments for industry risk, the risk of seasonality affecting sales and personnel risk. The table below shows the estimates of the amendments taking into account the highlighted data of the types of risk. These risks were identified by experts, therefore, when choosing an expert, close attention should be paid.

Types of riskRisk adjustment
The risk of seasonality affecting sales5%
Industry risk7%
HR risk3%
15%
Risk-free interest rate5%
Total:20%

As a result, adding up all the risk adjustments 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. In column "B" reflects the initial investment cost = 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. The calculation of discounted income (NPV) minus the initial investment cost (IC). F16 = SUM (F7: F15) -B6

The second option for calculating net present value is to use the built-in financial function of NPV in Excel (net present value). 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 calculations of the net present value. As we see the final calculation result is the same.

Modified Net Present Value MNPV (Modified Net Present Value)

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

MNPV - modification of the net present value,

CFt - cash flow in the period of time t,

It - cash outflow in a period of time t,

r is the discount rate (barrier rate),

d - reinvestment rate, interest rate showing the possible income from capital reinvestment,

n is the number of analysis periods.

As we can see, the main difference from a simple formula is the ability to account for returns on reinvestment of capital. Evaluation of an investment project using this criterion is as follows:

Indicator valueMnpvDecision making by criterion
MNPV> 0The investment project is accepted for further analysis.
MNPV ≤0Investment project rejected
Mnpv1 > MNPV2Comparison of projects among themselves. An investment project (1) is more attractive than (2)

Advantages and disadvantages of the method of estimating net present value

Let's make a comparison between the advantages of NPV and MNPV. The advantages of using these indicators include:

  • Clear boundaries of the selection and assessment of the investment attractiveness of the project,
  • The ability to take into account in the formula (discount rate) additional risks for the project,
  • Using discount rates to reflect changes in the value of money over time.

The disadvantages of net present value are the following:

  • Difficulty evaluating complex investment projects that involve many risks,
  • The difficulty of accurately forecasting future cash flows,
  • Lack of influence of intangible factors on future profitability (intangible assets).

Summary

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

Author: Ph.D. Zhdanov Ivan Yuryevich

What is it needed for

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

  1. to understand what income the project will bring, whether it will pay off in principle or is it unprofitable when it can pay off and how much money it will bring at a particular moment 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, i.e. the largest NPV) are taken.

Calculation formula

To calculate the indicator, the following formula is used:

where

  • 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 - 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, we 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):

Article1 year2 year3 year4 year5 year
Project investment100 000
Operating income35 00037 00038 00040 000
Operating expenses4 0004 5005 0005 500
Net cash flow- 100 00031 00032 50033 00034 500

The discount rate of the project is 10%.

Substituting in the formula the values ​​of net cash flow for each period (where we get negative cash flow 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

Calculate NPV in Excel

To illustrate how NPV is calculated in Excel, consider the previous example by putting it in tables. The calculation can be done in two ways.

  1. Excel has an NPV formula that calculates the net present value, for this you need to specify the discount rate (unsigned percent) and highlight the range of net cash flow. The form of the formula is as follows: = NPV (percentage, range of net cash flow).
  2. You can draw up an additional table yourself, where you discount the cash flow and sum it up.

Below in the figure we presented both calculations (the first shows the formulas, the second results of the calculations):

As you can see, both calculation methods lead to the same result, which suggests that depending on which is more convenient for you to use, you can use any of the presented calculation options.

Pin
Send
Share
Send
Send