Calculation of present (present, current) value in MS EXCEL. Definitions of the present (current) value of money Calculation of the present value of future money

Time value or, as they often say, time value of money (the emphasis in the word “temporary” here is placed on the last syllable) is economic concept taking into account changes in the value of money over time.

If we talk in simple words, then the essence of this concept can be expressed in one sentence: the same amount of money today costs more than tomorrow and in the following days (and the longer the period of time, the greater this difference in value).

This is also explained quite simply, both from an economic and a purely psychological point of view. From the point of view of human psychology, it is always more pleasant to receive money today than tomorrow, next month or in a year. And therefore the same amount received, as they say, at this moment, is always valued more expensive.

Well, from the point of view of economics, the time value of money is explained (and, in fact, estimated) by the interest that money can bring for the specific period of time under consideration.

Take, for example, a simple bank deposit. If you deposited 100,000 rubles into your bank account, and a year later withdrew 108,000 rubles from it, then the time value of the specified amount of money for this period was 8,000 rubles (it would be more correct to indicate it as a percentage - 8% per annum).

In general, the following two important principles follow from the concept under consideration:

1. As part of any financial transactions (with payments staggered over time), it is necessary to take into account the time factor when making mutual settlements;
2. In terms of analysis long term investment(or financial transactions) it is incorrect to summarize monetary values ​​relating to different points in time (without taking into account the value of money for the periods under consideration).

How to calculate the time value of money

Now let's talk about how, in fact, to calculate this notorious cost. As is already clear from the above, the time value of money in numerical terms is nothing more than the profit that could be extracted from it (for example, through investment) over the period of time under consideration.

That is, in the simplest case, for example, when investing money in bonds with an annual rate of return of 8%, the lost profit for the year will be this same 8%. In other words, an amount of 100,000 rubles, after one year will be valued at (100,000 + 100,000x0.08) = 108,000 rubles. Conversely, a future amount (one year from now) of 100,000 rubles will currently be valued at 100,000/1.08 = 92,592.59 rubles.

When conducting financial transactions, all payments spread over time lead to a single point in time (discounted). This is how the time value of money is taken into account.

It is customary to distinguish between two main types of cost:

1. Present value of money (PV);
2. Future value of money (Future value, FV).

The present value of money PV is also called discounted value. For the example above (100,000 rubles and eight percent bonds), the current value of money is 100,000 rubles, and the future, accordingly, is 108,000 rubles.

In general, when carrying out financial calculations, all monetary amounts are reduced to either PV or FV (for a given period of time) and only after that they are summed up (or other calculations are carried out with them).

Calculations of PV and FV values ​​can be carried out on the basis of both simple and compound interest.

Let us remember that compound interest is the calculation of profit taking into account reinvestment. That is, for example, profit for five years at an annual rate of return of 5% will be calculated taking into account the fact that every year 5% profit is added to the invested amount.

In the case of calculation based on simple interest, the formulas for the present and future value of money will look like:

where R – interest rate (per annum);

T – term in years.

When calculating based on compound interest, the formulas will take the form:

And, for example, for the case of annuity payments with a growth rate g and a discount rate i, the present value of money (PV) can be calculated using the formula:

What affects the time value of money

If, as they say, we dig a little deeper, we can say that the time value of money can depend on both internal and external factors. Internal factors include those that depend mainly on how money is managed over time. Namely:

1. Profitability level (percentage of investment Money);
2. The level of risk associated with the above investments. The risk may consist of either a lack of income from investments or a direct loss from them (up to the complete non-return of invested funds).

External factors include those that do not depend on how money is managed, in what financial instruments they are invested, etc. The most important of them is inflation. The higher the inflation rate, the more money depreciates over time and, therefore, the lower its future value (FV) becomes.

To take into account all these factors, there are complex formulas that allow you to calculate the time value of money as accurately as possible (as far as possible). The accuracy of such calculations is largely limited by the fact that such quantities as the level of profitability, risk or inflation are taken based on the predicted values ​​(and any forecast has its own degree of error).

We did not delve into such intricacies and provided simple formulas for calculating the current (PV) and future (FV) value of money based on the expected level of profitability on them (see the previous section). I believe that this is quite enough to understand the whole essence of the theory presented here.

Well, to put it even more simply, from the point of view of a simple trader or investor, the concept of the time value of money under consideration can be reduced to an axiom: Money should make money.

When discounting the predicted cash flow, one should take into account the fact that the company receives income and makes expenses evenly throughout the year, so discounting of flows should be done for the middle of the period. The current value factor is calculated using the formula:

F - current value factor,

R - discount rate,

n - number of periods.

Next, the current value factor determined in this way is multiplied by the amount of cash flow in the forecast period for the corresponding period. Current cost values cash flows add up, resulting in a net current cash flow for the entire forecast period.

It is also necessary to determine the amount of cash flow in the post-forecast period. This problem is solved using the Gordon model.

The essence of the Gordon model is that the value of cash flow at the beginning of the first year of the post-forecast period will be equal to the amount of capitalized profit of the post-forecast period (that is, the sum of all annual future cash flows in the post-forecast period).

Gordon's model looks like this:

V is the total amount of cash flow during the post-forecast period,

G - monetary current in the last forecast year,

R - discount rate;

g is the expected growth rate of cash flow in the post-forecast period.

Gordon's model is based on the following conditions:

Cash flow growth rate of OJSC Opttorg” in the post-forecast period, according to experts, will be about 5% (which correlates with the company’s revenue growth rate in 2013).

Discounting the value of the post-forecast period should be carried out using the current value factor of the last year of the reporting period (in our case, the current value factor is taken at the end of the 5th year).

After which, the resulting value from discounting the value of the company in the post-forecast period is added to the net cash flow determined for the forecast period. The result is the market value of 100% of the equity capital of the company being valued.

The beginning of the forecast period is the date of the assessment, the end is December 31 of the last forecast year. The assessment date is July 8, 2004. Therefore, the calculation of net present value does not include cash flow for the entire year 2009, but for the period from July 8 to December 31, 2009, lasting 176 days (0.48 years). As a result, cash flow for 2009 is adjusted by a factor of 0.48. Accordingly, discounting is carried out to the middle of this period. Then the duration of time from the date of assessment to its midpoint will be:

T2004 = 176/365/2 = 0.241 years

The durations of the periods from the date of assessment to the middle of 2010, 2011, 2012, 2013 and the beginning of the post-forecast period will be:

T2005 = 0.241 x 2 + 0.5 = 0.982

T2006 = 0.982 + 1 = 1.982

T2007 = 1.982 + 1 = 2.982

T2008 = 2.982 + 1 = 3.982

T post. forecast = 3.982 + 0.5 = 4.482

The final amendments are:

1. Adjustment for excess (shortage) of own working capital. This amendment is necessary to take into account the actual amount of working capital, since the cash flow model takes into account the required amount of working capital, while its actual amount may not coincide with the required one. As a result, the excess of own working capital must be added, and the deficiency must be subtracted from the preliminary cost.

The Appraiser's specialists, when determining the adjustment for the excess (deficiency) of SOC as of the valuation date - July 8, 2009, could rely on:

• -on factual data financial statements for 1 sq. 2009, the date of which is 99 days from the date of assessment;
• - forecast values ​​of the state of the current assets and liabilities of the enterprise, calculated as a whole for 2009 (the forecast date is 176 days away from the assessment date).

In the course of forecasting changes in own working capital, I calculated the values ​​of the current assets and liabilities of the enterprise based on the results of 2009 (respectively 18,528 thousand rubles and 54,978 thousand rubles) and determined the lack of SOC, which is equal to 36,450 thousand rubles .

Period from January 1, 2009 before July 8, 2004 = 31 + 29 +31 + 30 +31 +30 +8 = 190 days;

Number of days in 2009 g = 365 days;

Conversion factor as of the valuation date = 190 / 365 = 0.52;

Lack of SOC = 36,450 thousand rubles. * 0.52 = 18,954 thousand rubles.

This model is valid only in one case: the rate of change in the balances of funds in the accounts of current assets and liabilities is the same throughout the year and the lack of SOC grows linearly.

Let us calculate, based on forecast data, the values ​​of the shortage of SOC based on the results of the first quarter of 2009:

Number of days in 2009 g = 365 days;

Conversion factor for the valuation date = 91 / 365 = 0.25;

Lack of SOC = 36,450 thousand rubles. * 0.25 = 9,112.5 thousand rubles.

Comparison of the actual shortage of SOC in 1 quarter. 2004 - 14,092 thousand rubles. and calculated, based on forecast data - 9,112.5 thousand rubles. allows us to conclude that changes in the current assets and liabilities of the enterprise are uneven. Thus, the reliability of the obtained calculation result is relatively low; the obtained value of the RMS deficiency may be overestimated, which will lead to an underestimation of the value of the valuation object.

Amount of deficiency (excess) of JUICE, thousand rubles.

Table 20

Therefore, the calculated shortfall in own working capital at the valuation date must be deducted.

2. Adjustment for the value of non-operating assets. During the assessment of Opttorg OJSC, the appraiser’s specialists were unable to identify these objects, and therefore the adjustment for the value of non-operating assets cannot be calculated correctly.

Calculation of enterprise value by income approach(net present value), thousand rubles.

Table 21

Index						Post-forecast period
Net profit of the reporting period
Depreciation deductions
Reduction of long-term debt
Increase in working capital
Capital investments
Cash flow
Discount rate, %
Growth rates in the post-forecast period, %
Cost in the post-forecast period,
Duration of the discount period
Current cost factor
Net current value den. flow
Sum of current values
Excess personal working capital
Non-operating assets
Total article capital

The market value of 100% of Opttorg OJSC, calculated by the discounted cash flow method, is (rounded): 84,400,000 (eighty-four million four hundred thousand) rubles.

Net present value (NPV, net present value, net present value, NPV, EnglishNet present value , accepted in international practice for analysis investment projects reduction - NPV) is the sum of discounted values ​​of the payment stream, reduced to today.

The net present value method has been widely used in capital budgeting and decision making. investment decisions. NPV is also considered the best selection criterion for making or rejecting a decision to implement an investment project, since it is based on the concept of the time value of money. In other words, net present value reflects the expected change in the investor's wealth as a result of the project.

NPV formula

The net present value of a project is the sum of the present value of all cash flows (both incoming and outgoing). The calculation formula is as follows:

• CFt– expected net cash flow (the difference between incoming and outgoing cash flow) for the period t,
• r- discount rate,
• N– project implementation period.

Discount rate

It is important to understand that when choosing a discount rate, not only the concept of time value of money must be taken into account, but also the risk of uncertainty in the expected cash flows! For this reason, it is recommended to use the weighted average cost of capital ( English Weighted Average Cost of Capital, WACC), brought in to implement the project. In other words, WACC is the required rate of return on the capital invested in the project. Therefore, the higher the risk of cash flow uncertainty, the higher the discount rate, and vice versa.

Project selection criteria

The decision rule for selecting projects using the NPV method is quite straightforward. A zero threshold value indicates that the project's cash flows allow it to cover the cost of capital raised. Thus, the selection criteria can be formulated as follows:

1. An individual independent project must be accepted if its net present value is positive or rejected if its net present value is negative. Zero is the point of indifference for the investor.
2. If an investor is considering several independent projects, those with a positive NPV should be accepted.
3. If a number of mutually exclusive projects are being considered, the one with the highest net present value should be selected.

Net present value is the sum of the current values ​​of all predicted cash flows, taking into account the discount rate.

The net present value (NPV) method is as follows.
1. The current cost of costs (Io) is determined, i.e. The question of how much investment needs to be reserved for the project is decided.
2. The current value of future cash receipts from the project is calculated, for which the income for each year CF (cash flow) is reduced to the current date.

The calculation results show how much money would need to be invested now to receive the planned income if the income rate were equal to the barrier rate (for an investor, the interest rate in a bank, in a mutual fund, etc., for an enterprise, the price of total capital or through risks). Summing up the current value of income for all years, we obtain the total current value of income from the project (PV):

3. The present value of investment costs (Io) is compared with the present value of income (PV). The difference between them is the net present value of income (NPV):

NPV shows the investor's net gains or net losses from investing money in a project compared to keeping the money in a bank. If NPV > 0, then we can assume that the investment will increase the wealth of the enterprise and the investment should be made. At NPV

Net present value (NPV) is one of the main indicators used in investment analysis, but it has several disadvantages and cannot be the only means of evaluating an investment. NPV determines absolute value return on the investment, and most likely the larger the investment, the greater the net present value. Hence, it is not possible to compare multiple investments of different sizes using this indicator. In addition, NPV does not determine the period over which the investment will pay off.

If capital investments related to the upcoming implementation of the project are carried out in several stages (intervals), then the NPV indicator is calculated using the following formula:

, Where

CFt - cash inflow in period t;

r - barrier rate (discount rate);
n is the total number of periods (intervals, steps) t = 1, 2, ..., n (or the duration of the investment).

Typically for CFt the t value ranges from 1 to n; in the case where CФо > 0 is considered a costly investment (example: funds allocated for an environmental program).

Defined by: as the sum of the current values ​​of all predicted, taking into account the barrier rate (discount rate), cash flows.

Characterizes: investment efficiency in absolute values, in current value.

Synonyms: net present effect, net present value, Net Present Value.

Acronym: NPV

Flaws: does not take into account the size of the investment, the level of reinvestment is not taken into account.

Eligibility Criteria: NPV >= 0 (the more the better)

Comparison conditions: To correctly compare two investments, they must have the same investment costs.

Example No. 1. Calculation of net present value.
The investment amount is $115,000.
Investment income in the first year: $32,000;
in the second year: $41,000;
in the third year: $43,750;
in the fourth year: $38,250.
The size of the barrier rate is 9.2%

Let's recalculate cash flows in the form of current values:
PV 1 = 32000 / (1 + 0.092) = $29304.03
PV 2 = 41000 / (1 + 0.092) 2 = $34382.59
PV 3 = 43750 / (1 + 0.092) 3 = $33597.75
PV 4 = 38250 / (1 + 0.092) 4 = $26899.29

NPV = (29304.03 + 34382.59 + 33597.75 + 26899.29) - 115000 = $9183.66

Answer: The net present value is $9,183.66.

The formula for calculating the NPV (net present value) indicator taking into account the variable barrier rate:

NPV - net present value;
CFt - inflow (or outflow) of funds in period t;
It is the amount of investments (costs) in the t-th period;
ri - barrier rate (discount rate), fractions of a unit (in practical calculations, instead of (1+r) t, (1+r 0)*(1+r 1)*...*(1+r t) is used, because . the barrier rate can vary greatly due to inflation and other components);

N is the total number of periods (intervals, steps) t = 1, 2, ..., n (usually the zero period implies the costs incurred to implement the investment and the number of periods does not increase).

Example No. 2. NPV with a variable barrier rate.
Investment size - $12800.

in the second year: $5185;
in the third year: $6270.

10.7% in the second year;
9.5% in the third year.
Determine the net present value for the investment project.

n =3.
Let's recalculate cash flows in the form of current values:
PV 1 = 7360 / (1 + 0.114) = $6066.82
PV 2 = 5185 / (1 + 0.114)/(1 + 0.107) = $4204.52
PV 3 = 6270 / (1 + 0.114)/(1 + 0.107)/(1 + 0.095) = $4643.23

NPV = (6066.82 + 4204.52 + 4643.23) - 12800 = $2654.57

Answer: The net present value is $2,654.57.

The rule according to which, from two projects with the same costs, the project with a large NPV is selected does not always apply. A project with a lower NPV, but with short term payback may be more profitable than a project with a large NPV.

Example No. 3. Comparison of two projects.
The cost of investment for both projects is 100 rubles.
The first project generates a profit equal to 130 rubles at the end of 1 year, and the second 140 rubles after 5 years.
For simplicity of calculations, we assume that barrier rates are equal to zero.
NPV 1 = 130 - 100 = 30 rub.
NPV 2 = 140 - 100 = 40 rub.

But at the same time, the annual profitability calculated using the IRR model will be equal to 30% for the first project, and 6.970% for the second. It is clear that the first investment project will be accepted, despite the lower NPV.

To more accurately determine the net present value of cash flows, the modified net present value (MNPV) indicator is used.

Example No. 4. Sensitivity analysis.
The investment amount is $12,800.
First year investment income: $7,360;
in the second year: $5185;
in the third year: $6270.
The size of the barrier rate is 11.4% in the first year;
10.7% in the second year;
9.5% in the third year.
Calculate how the net present value would be affected by a 30% increase in investment income?

The initial value of the net present value was calculated in example No. 2 and is equal to NPV ex = 2654.57.

Let's recalculate cash flows in the form of current values, taking into account sensitivity analysis data:
PV 1 ah = (1 + 0.3) * 7360 / (1 + 0.114) = 1.3 * 6066.82 = $7886.866
PV 2 ah = (1 + 0.3) * 5185 / (1 + 0.114)/(1 + 0.107) = 1.3 * 4204.52 = $5465.876
PV 3 ah = (1 + 0.3) * 6270 / (1 + 0.114)/(1 + 0.107)/(1 + 0.095) = 1.3 * 4643.23 = $6036.199

Let's determine the change in net present value: (NPV ach - NPV out) / NPV out * 100% =
= (6036,199 - 2654,57) / 2654,57 * 100% = 127,39%.
Answer. A 30% increase in investment income resulted in a 127.39% increase in net present value.

Note. Discounting cash flows with a time-varying barrier rate (discount rate) corresponds to “Methodological guidelines No. VK 477...” clause 6.11 (p. 140).

Net present value

Copyright © 2003-2011 by Altair Software Company. Potential programs and projects.

where PV is the current value of money,

FV – future value of money,

n – number of time intervals,

i – discount rate.

Example. What amount must be deposited into the account in order to receive 1000 rubles in five years? (i=10%)

PV = 1000 / (1+0.1)^5 = 620.92 rub.

Thus, to calculate the current value of money, we must divide its known future value by the value (1+i) n. The present value is inversely related to the discount rate. For example, current value monetary unit received after 1 year at a rate of 8% is

PV = 1/(1+0.08) 1 = 0.93,

And at a rate of 10%

PV = 1/(1+0.1) 1 = 0.91.

The current value of money is also inversely related to the number of time periods before it is received.

The considered procedure for discounting cash flows can be used when making investment decisions. Most general rule decision making – the rule for determining net present value (NPV). Its essence is that participation in an investment project is advisable if the present value of future cash receipts from its implementation exceeds the initial investment.

Example. It is possible to buy a savings bond with a nominal value of 1000 rubles. and a repayment period of 5 years for 750 rubles. Another alternative investment option is to place your money in bank account with an interest rate of 8% per annum. It is necessary to evaluate the feasibility of investing in the purchase of a bond.

To calculate NPV as interest rate or in a broader sense of rate of return, the opportunity cost of capital must be used. The opportunity cost of capital is the rate of return that can be obtained from other investment avenues. In our example, an alternative type of investment is to place money on a deposit with a yield of 8%.

A savings bond provides cash receipts of RUB 1,000. after 5 years. The present value of this money is

PV = 1000/1.08^5 = 680.58 rub.

Thus, the current value of the bond is 680.58 rubles, while they offer to buy it for 750 rubles. The net present value of the investment will be 680.58-750=-69.42, and investing in the purchase of a bond is not advisable.

The economic meaning of the NPV indicator is that it determines the change financial condition investor as a result of the project implementation. In this example, if the bond is purchased, the investor’s wealth will decrease by 69.42 rubles.

The NPV indicator can also be used to evaluate various options for borrowing funds. For example, you need to borrow $5,000. to purchase a car. The bank offers you a loan at 12% per annum. Your friend can borrow $5,000 if you give him $9,000. After 4 years. It is necessary to determine the optimal borrowing option. Let's calculate the current value of $9,000.

PV = 9000/(1+0.12)^4 = $5719.66

Thus, the NPV of this project is 5000-5719.66= -719.66 dollars. In this case, the best borrowing option is a bank loan.

To calculate the effectiveness of investment projects, you can also use the internal rate of return (IRR). The internal rate of return is the value of the discount rate that equalizes the present value of future revenues and the present value of costs. In other words, IRR is equal to the interest rate at which NPV = 0.

In the above example of purchasing a bond, the IRR is calculated from the following equation

750 = 1000/(1+IRR)^5

IRR = 5.92%. Thus, the yield on the bond at maturity is 5.92% per year, which is significantly less than the yield on a bank deposit.