The Time Value of Money (TVM) is an important metric in the accounting and finance industry. The idea is that a ruble today is worth less than the same ruble tomorrow. The difference between these two financial values ​​is the profit that can be made from one ruble or loss. For example, this profit can be received from interest accrued on a bank account or as dividends from investments. But there may also be a loss when paying interest on repaying a loan debt.

An example of calculating the current present value of an investment in Excel

Excel offers several financial functions for calculating the time value of money. For example, the PV (Present Value) function returns the present value of an investment. In simple terms, this function reduces the amount by the discount percentage and returns the fair value for that amount. If the investment project assumes to bring a profit of 10,000 in a year. Question: what is the maximum amount of rational risk to invest in this project?

For example, in Russia, the retail business sometimes makes a profit of up to 35% per annum, and the wholesale business does not exceed 15%. Given the small amount of investment, it is assumed that the investment object is not a wholesale business, which means that a profit of more than 15% per annum should be expected. The figure below shows an example of the formula for the percentage return on investment calculator:

As we see in the figure, the calculator displays to us, in order to get the amount of 10,000 for 1 year with a yield of 25%, we need to invest 8,000 financial resources. That is, if we had an amount of 8,000 and we invested it at 25% per annum, in a year we would have earned 10,000.

The PS function has 5 arguments:

1. Rate - percentage discount rate. This is the percentage return that can be expected over the discount period. This value has the greatest impact on the calculation of the present value of the investment, but is the most difficult to accurately determine. Cautious investors most often underestimate the interest rate to the maximum realistically achievable level under certain conditions. If the funds are intended to repay the loan, then this argument is easily determined.
2. Number of periods(Nper) - the period of time during which the future amount is discounted. In this example, 1 year is specified (recorded in cell B2). The interest rate and the number of years must be expressed in the appropriate units of measure. This means that you are using an annual rate, then the numeric value in this argument is the number of years. If the interest rate in the first argument is for months (for example, 2.5% monthly), then the number in the second argument is the number of months.
3. Payment (Pmt) - the amount that is periodically paid during the discount period. If there is only one payment in the investment conditions, as in the above example, then this amount is the future value of money, and the payment itself is = 0. This argument must match the second number of periods argument. If the number of discount periods is 10 and the third argument is not<>0, then the PS function will count as 10 payments for the amount specified in the third argument (Pmt). The following example below shows how the present value of money is calculated with several installments in separate payments.
4. Future value (FV) is the amount to be received at the end of the discount period. Excel financial functions are based on cash flow calculations. This means that the future value and present value of an investment have opposite signs. In this example, the future value is a negative number, so the formula evaluates to a positive number.
5. Type - this argument must have the value 0 if the payment of the total amount falls at the end of the discount period, or the number 1 - if at its beginning. In this example, the value of this argument does not matter and will not affect the final result of the calculation in any way. Because the payment fee is zero and the type argument can be omitted. In this case, the function defaults to this argument with the value 0.



Formula for calculating the present value of money with inflation in Excel

In another example of applying the PV function, the future value of money is calculated for a whole series of future equal payments at once. If, for example, under an office lease, the tenant must pay 5,000 every month for one year, then the landlord can use the PV function to calculate how much he will lose in income, taking into account 6.5% annual inflation:

In this example, the fifth Type argument has a numeric value of 1 because the rent is paid at the beginning of each month.

If there is an amount of regular payments, the PS function actually calculates the current value of money separately for each payment and sums up the results. The figure shows the results of calculating the cost for each payment. The current value of the first payment is the same as the amount of the payment, as it is now paid after the fact. The next month's payment will be paid in a month and its current monetary value is already decreasing (depreciating). It is discounted to the amount of 4,973. The changes are not significant, but the last payment, which will be paid in 11 months, already has a value significantly lower - 4,712. All results of calculating the values ​​of the present value of investments must be summed up. The PS function does all this work automatically without the need for a chronological payment schedule for the entire period.

The present value is understood as the present value of future cash flows (receipts or payments), discounted in accordance with the established rate (percentage, discount). The discount rate when calculating the present value of money is also called the capitalization rate, or cost of capital, or the minimum rate of return requested by investors.

simple discounting technique. The formula for calculating the present value (Po, or PV) can be derived from Equation 5 by taking Po as the unknown. It is known that FVn = Po* (1 + i) n . Expressing Po, we obtain a formula by which the present value of future payments or, conversely, receipts of money is determined:

Factor
, or T3 (i, n), is the current value of 1 rub. at given discount rates and terms. For the convenience of financial calculations, it is also standardized in special tables (4).

The current value of 1 rub. at given discount rates and terms:

= T3 (1, n)

	Bid, %

The present value of serial payments (annuities). The present value of a series of future equal periodic payments (receipts) (РVAn) is determined according to the principle of geometric progression:

where A is the equal amount of serial payments, thousand rubles; Т4(i, n) - the current cost of 1 rub. future serial periodic payments, discounted at the rate i for n number of periods.

The factor T4(i, n) is standardized in the form of table 5.

The current value of 1 rub. future serial periodic payments, discounted at the rate I for n number of periods.

	Bid, %

Lifetime annuity. One of the special cases of equal periodic payments (annuities) is a life annuity, in which payments are supposed to be made indefinitely. A common example of extracting a lifetime annuity is investing in preferred stock, which brings a constant income without a time limit. The present value of a lifetime annuity (PR) is determined by the formula:

(11)

where A - rental payments (dividends), thousand rubles; i - discount rate.

3. Assessment of income and risk

1. Income estimation methods

Income is the reward received on invested capital. Investors' incomes are formed from two sources: 1) current receipts (dividends) on shares; 2) changes in the market value of securities compared to their purchase price.

In addition, the investor's income depends on the duration of holding the security. The return on investment in securities (ER) for the holding period is calculated as follows:

(1)

where Dt - income received by the end of period i; Рt - stock price in period i; P t -1 - share price in period t-1.

Typically, securities are held by the investor for several periods of time when the levels of returns are different. Therefore, in the practice of financial and investment management, the arithmetic and geometric mean values ​​of profitability are determined. The arithmetic mean return is the arithmetic average of the return over the holding period. This indicator does not always accurately reflect the actual return, estimated over several periods. A more accurate indicator of assessing the real return on investment over a number of periods is the geometric average return (AGR), otherwise called the annual rate of return. It is calculated according to the formula

where i - profitability for certain periods of holding a security; m is the number of periods of holding the security.

An important step in the financial decision-making process is the assessment of the weighted average expected return (ER) from investing in a particular security. Forecast measurements are carried out based on the statistical probability of obtaining possible income (i t) in the event of certain events of a political, economic and other nature that can affect the state of the stock market and the value of quoted securities:

where i t - possible profitability upon the occurrence of the i-th event; р t , - probability of occurrence of the i-th event, %; n is the number of possible events.

As we have already found out, today's money is more expensive than the future. If we are offered to purchase a zero-coupon bond, and in a year they promise to redeem this security and pay 1000 rubles, then we need to calculate the price of this bond at which we would agree to buy it. In fact, for us the task is to determine the current value of 1000 rubles, which we will receive in a year.

Present value is the flip side of future value.

The present value is the present value of the future cash flow. It can be derived from the formula for determining the future value:

where RU is the current value; V- future payments; G - discount rate; discount coefficient; P - number of years.

In the example above, we can calculate the price of a bond using this formula. To do this, you need to know the discount rate. As a discount rate, they take the yield that can be obtained in the financial market by investing money in any financial instrument with a similar level of risk (bank deposit, bill, etc.). If we have the opportunity to place funds in a bank that pays 15% per year, then the price of the bond offered to us

Thus, by purchasing this bond for 869 rubles. and having received 1000 rubles in a year when it is repaid, we will earn 15%.

Consider an example where an investor needs to calculate the initial deposit amount. If in four years the investor wants to receive the amount of 15,000 rubles from the bank. at market interest rates of 12% per annum, how much should he place in a bank deposit? So,

To calculate the present value, it is advisable to use discount tables showing the current value of the monetary unit, which is expected to be received in a few years. The table of discount coefficients showing the present value of the monetary unit is presented in Appendix 2. A fragment of this table is given below (Table 4.4).

Table 4.4. The present value of the monetary unit, which will be received in and years

	Annual interest rate

For example, you want to determine the present value of $500 expected to be received in seven years at a discount rate of 6%. In table. 4.4 at the intersection of the row (7 years) and column (6%) we find the discount factor 0.665. In this case, the present value of $500 is 500 0.6651 = $332.5.

If interest is paid more than once a year, then the formula for calculating the present value is modified in the same way as we did with the calculations of the future value. With multiple interest accruals during the year, the formula for determining the present value has the form

In the above example with a four-year deposit, let's assume that interest on the deposit is calculated quarterly. In this case, in order to receive $15,000 in four years, the investor must deposit an amount

Thus, the more often interest is calculated, the lower the current value for a given end result, i.e. the relationship between interest rate and present value is inverse to that for future value.

In practice, financial managers are constantly faced with the problem of choosing options when it is necessary to compare cash flows at different times.

For example, there are two options for financing the construction of a new facility. The total construction period is four years, the estimated cost of construction is 10 million rubles. Two organizations are participating in the tender for a contract, offering the following terms of payment for work by year (Table 4.5).

Table 4.5. Estimated cost of construction, million rubles

	Organization BUT	Organization IN

The estimated cost of construction is the same. However, the costs of their implementation are distributed unevenly. Organization BUT the main amount of costs (40%) is carried out at the end of construction, and the organization IN - in the initial period. Of course, it is more profitable for the customer to attribute the payment costs to the end of the period, since over time the funds depreciate.

In order to compare multi-temporal cash flows, it is necessary to find their value reduced to the current point in time and sum the obtained values.

Present value of the payment stream (RU) calculated by the formula

where is the cash flow per year; t - serial number of the year; G - discount rate.

If in the example under consideration r \u003d 15%, then the results of calculating the reduced costs for the two options are as follows (Table 4.6).

Table 4.6.

According to the present value criterion, the financing option proposed by the organization BUT, turned out to be cheaper than the offer of the organization IN. The customer in these conditions will certainly prefer to give the contract to the organization BUT (ceteris paribus).

In this article, we will consider what net present value (NPV) is, what economic meaning it has, how and by what formula to calculate net present value, we will consider some examples of calculation, including using MS Excel 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 seeks not only to recoup his investments, but also to earn something in excess of the amount of the initial investment. In addition, the task of the investor is to search for alternative investment options that, given comparable levels of risk and other investment conditions, would bring higher returns. One of the methods of such analysis is the calculation of the net present value of the 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) the expected cash flows (both income and expenses).

The net present value reflects the investor's return (the value added of the investment) that the investor expects to receive from the implementation of the project, after the cash inflows pay off its initial investment costs and the periodic cash outflows associated with the implementation of such a project.

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

Formula for calculating NPV

To calculate NPV, you need:

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

The calculation of NPV is carried out according to 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 amount of initial investment IC(Invested Capital), i.e. CF0=IC. At the same time, the cash flow CF 0 has a negative value.

Therefore, the above formula can be modified:

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

Practical application of NPV (net present value)

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

1. NPV > 0. If the net present value is positive, then this indicates a full return on investment, and the NPV value shows the final profit of 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 receive a profit. For example, if borrowed funds were used, then the cash flows from investments will make it possible to fully pay off the creditor, including paying the interest due to him, but the financial position of the investor 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. Investing in such a project should be abandoned.

Thus, all projects that have a positive NPV value are accepted for investment. If the 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.

Calculation of NPV using MS Excel

In MS Excel, there is a NPV function that allows you to calculate the net present value.

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

Syntax of the NPV function:

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

where
Bid is the discount rate for one period.
Value1, value2,…- 1 to 29 arguments representing expenses and income.

Value1, value2, … should be evenly distributed in time, payments should be made at the end of each period.

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

Consider an example of NPV calculation based on 4 alternative projects.

As a result of the calculations project A should be rejected project B is at the point of indifference for the investor, but projects C and D should be used for investment. At the same time, if it is necessary to select only one project, then preference should be given to project B, despite the fact that it generates less undiscounted cash flows over 10 years than project D.

Advantages and disadvantages of NPV

The positive aspects of the NPV methodology include:

• clear and simple rules for making decisions regarding the investment attractiveness of the project;
• applying a discount rate to adjust the amount of cash flows over time;
• the possibility of including a risk premium as part of the discount rate (for more risky projects, a higher discount rate can be applied).

The disadvantages of NPV include the following:

• Difficulty in valuation for complex investment projects that involve many risks, especially in the long term (adjustment of the discount rate is required);
• the complexity of forecasting future cash flows, the accuracy of which depends on the estimated value of NPV;
• 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, for example, such as , .

NPV (abbreviation, in English - Net Present Value), in Russian this indicator has several variations of the name, among them:

• net present value (abbreviated NPV) - the most common name and abbreviation, even the formula in Excel is called that;
• net discounted income (abbreviated NPV) - the name is due to the fact that cash flows are discounted and only then summed up;
• net present value (abbreviated as 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 the economy, if we earn 1,000 rubles and then we actually get less than if we received the same amount but now).

NPV is an indicator of the profit that the participants in the investment project will receive. Mathematically, this indicator is found by discounting the values ​​of the net cash flow (regardless of whether it is negative or positive).

Net present value can be found for any time period of the project since its inception (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 project performance indicators, along with IRR, simple and discounted payback period. It is needed in order to:

1. 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 point in time;
2. to compare investment projects (if there are a number of projects, but there is not enough money for all, then projects with the greatest opportunity to earn, i.e., the largest NPV, are taken).

Calculation formula

The following formula is used to calculate the indicator:

• CF - the amount of net cash flow in a period of time (month, quarter, year, etc.);
• t is the time period for which the net cash flow is taken;
• N - the number of periods for which the investment project is calculated;
• i - 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):

Article	1 year	2 year	3 year	4 year	5 year
Investment in the project	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

Project discount factor - 10%.

Substituting in the formula the values ​​of the net cash flow for each period (where a 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, consider the previous example by putting it into 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 a discount rate (without a percentage sign) and select a range of net cash flow. The formula looks like this: = NPV (percentage; net cash flow range).
2. You can make 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 shows the results of the calculations):

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