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 valueNPV||Making decisions|
|NPV≤0||This investment project does not provide coverage for future expenses or provides only breakeven and should be rejected from further consideration.|
|NPV> 0||The project is attractive for investment and requires further analysis.|
|NPV1> NPV2||Investment 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.
|Methods||Benefits||The initial data for the calculation|
|CAPM Model||Accounting for the impact of market risk on the discount rate||Quotations of ordinary shares (MICEX)|
|WACC Model||The ability to take into account the efficiency of using both own 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)|
|Ross Model||Accounting for industry, macro and micro factors determining the discount rate||Statistics on macro indicators (Rosstat)|
|Model Famous and French||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)|
|Based on market multiples||All market risks||Quotations of ordinary shares (MICEX)|
|Based on return on equity||Accounting for the use of equity||Balance sheet|
|Based on expert judgment||The ability to evaluate venture projects and various difficult to formalize factors||Expert 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 risk||Risk adjustment|
|The risk of seasonality affecting sales||5%|
|Risk-free interest rate||5%|
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:
- In column "B" reflects the initial investment cost = 100 000 rubles.,
- Column “C” reflects all future planned cash receipts for the project,
- Column “D” records all future cash expenses,
- Cash flow CF (column "E"). E7 = C7-D7,
- Calculation of discounted cash flow. F7 = E7 / (1 + $ C $ 3) ^ A7
- 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 valueMnpv||Decision making by criterion|
|MNPV> 0||The investment project is accepted for further analysis.|
|MNPV ≤0||Investment project rejected|
|Mnpv1 > MNPV2||Comparison 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).
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:
- 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,
- 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.
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 - the number of periods for which the investment project is calculated,
- i is the discount rate taken into account in this project.
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):
|Article||1 year||2 year||3 year||4 year||5 year|
|Project investment||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 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.
- 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).
- 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.