Each person who seriously engaged in financial activities or professional investment came across such an indicator as pure discounted income or NPV. This indicator reflects the investment efficiency of the project being studied. The Excel program has tools that help calculate this value. Let's find out how they can be used in practice.
Calculation of net discounted income
An indicator of pure discounted income (CDD) in English is called Net Present Value, so it is generally accepted to call it to call NPV. There is still an alternative name - the net present value.NPV defines the amount of discounted payment values given to the current day, which are the difference between the tributaries and outflows. If we speak in a simple language, this indicator determines how much profit plans to receive an investor minus all outflows after the initial contribution is paid.
The Excel program has a function that is specifically designed to calculate the NPV. It refers to the financial category of operators and is called ChPS. The syntax of this feature is as follows:
= ChPS (rate; value1; value2; ...)
The "rate" argument is a set amount of discount rate for one period.
The "value" argument indicates the amount of payments or revenues. In the first case, it has a negative sign, and in the second is positive. This type of arguments in the function can be from 1 to 254. They can act as in the form of numbers, and be referenced to cells in which these numbers are contained, however, as the "rate" argument.
The problem is that the function is, although the CPS is called, but the NPV calculation is not entirely correct. This is due to the fact that it does not take into account the initial investment, which according to the rules applies not to the current, but to the zero period. Therefore, in Excel, the NPV calculation formula would be more correct to write this way:
= Initial_investment + ChPS (rate; value1; value2; ...)
Naturally, the initial investment, like any kind of investment, will be with the sign "-".
An example of calculating NPV.
Let's consider the use of this function to determine the value of NPV on a specific example.
- Select a cell in which the result of the calculation of NPV will be displayed. Click on the "Insert function" icon placed near the formula row.
- The functions wizard window starts. Go to the category "Financial" or "Full Alphabetical List". Choose a "ChPS" record in it and click on the "OK" button.
- After that, the window of the arguments of this operator will be opened. It has the number of fields equal to the number of function arguments. Commitable to fill is the "rate" field and at least one of the fields "value".
In the "Rate" field, you need to specify the current discount rate. Its value can be guided by manually, but in our case its value is placed in the cell on the sheet, so you specify the address of this cell.
In the "Value1" field, you need to specify the coordinates of the range containing the actual and estimated cash flows, excluding the initial payment. This can also be done manually, but it is much easier to set the cursor to the appropriate field and with the left mouse button to highlight the appropriate range on the sheet.
Since in our case, cash flows are placed on a sheet with a solid array, then you do not need to make data into other fields. Just press the "OK" button.
- The calculation of the function was displayed in the cell, which we allocated in the first point of instructions. But, as we remember, the initial investment remained unaccounted. In order to complete the calculation of the NPV, select the cell containing the CPS function. In the formula row, its value appears.
- After the symbol "=" add the amount of the initial payment with the sign "-", and after it put the "+" sign, which should be in front of the CPS operator.
You can also, instead of the number, specify the address of the cell on the sheet, which contains the initial contribution.
- In order to make calculation and output the result in the cell, click on the ENTER button.
The result is derived and in our case a pure discounted income is 41160.77 rubles. It is this amount that an investor after the deduction of all investments, as well as taking into account the discount rate, can expect to receive in the form of profit. Now, knowing this indicator, he can decide whether it is worth investing in the project or not.
Lesson: Financial functions in Excel
As you can see, if there are all incoming data, perform the calculation of NPV using Excel tools is quite simple. The only inconvenience is that the function intended to solve this task does not take into account the initial payment. But this problem is easy to solve, simply substituting the corresponding value in the final calculation.