Syntax. NPV(Rate,Value1,Value2, ...)
Definition. This function calculates the net cash value of future period surpluses (cash flow) of an investment based on a given calculation interest rate.
Arguments
Rate (required) The calculation interest rate supplied by the investor.
Value1,Value2,... (required) The (actual and expected) surpluses from disbursements and deposits, arranged without gaps in a column. Each value represents the end of a period (usually one year) in ascending order and without gaps. Negative surpluses have a minus sign.
If the cells of the Value argument do not contain numbers or are empty, Excel calculates as if these cells didn’t exist. This is also the case if cell references in the argument refer to such cells.
Background. Dynamic methods of investment calculation are based on the (estimated and predicted) deposits and disbursements and their yield, unlike static methods, which are based on cost and earnings of an investment. Both payment directions are evaluated with a uniform calculation interest rate that results from the experiences of the investor. The sum of all discounted period surpluses is called the capital value.
An investment is considered financially sound if the capital value is not negative, because then the invested capital plus the yield is returned.
The first value for the Value arguments of NPV() represents the end of the first period. The capital value of an investment is determined from NPV() minus the disbursements at the start of the first period.
Note
If the period surpluses are consistent, the function PV() can be used for calculating the net cash value, as an alternative to NPV(). Because PV() permits a future value as an argument, a surplus that deviates from the consistent surpluses and is based on the residual value of the investment can also be taken into consideration.
Example. When reading the following examples, compare them to the explanations about IRR() and the examples in that section.
Investment in Material Assets. The purchase cost for a machine is $80,000.00. The expected annual surpluses (deposits minus disbursements) are estimated as shown in Table 15-6.
Table 15-6. Estimated Annual Surpluses in the Use of a Machine
Year | Surplus (in $) |
|---|---|
1 | 15,000 |
2 | 19,000 |
3 | 25,000 |
4 | 27,000 |
5 | 17,000 |
6 | 7,000 |
Is the investment economically sound if a calculation interest rate of 10 percent (p.a.) is used?
To answer this question, make sure to note the purchase cost in the first cell, and at another location, note the values from Table 15-6 in a column without gaps. Using NPV() returns an amount of approximately $81,070, which is slightly above the purchase cost. The yield will therefore most likely be paid at a slightly better interest rate than expected.
Note that the decimal places are not necessarily important when you are working with real investments whose future surpluses are only estimated.
Financial Investment. Type A German federal savings bonds with the terms of six years, shown in Table 15-7, might seem to have a total yield of about 1.5 percent at first glance.
In the sample files for this function, you can find calculations that show that the net cash value for an investment amount of $100.00 is only $99.63. Therefore, an investment at this expected yield does not make sense.