The NPV function calculates the net present value of a set of future cash flows. Ability assumes that the first cash flow occurs at the end of the first period, which means the initial payment is usually not included. (Compare this with the IRR function – see IRR)
Ability calculates the net present value according to the following formula:
n is the number of items in the list and listi is the ith element in the list.
In the function, enter rate as the effective interest rate per period. Enter list as the value of the payments in the order in which they appear, one payment per period. If you enter list as a range that includes several rows and columns, Ability reads the range from left to right and top to bottom. If blank cells are in the range, Ability skips them when calculating the net present value.
For example, to find the net present value of cash flows of $400, $300, $300, and $200 and assuming inflation will run at 13.25%, type the following formula:
NPV(13.25%,400,300,300,200)
Ability calculates the net present value and displays 915.23 or $915.23 (depending on the currency formatting of the cell – use the Number command from the Format menu to change it). This shows that the real value of these future payments is much less than the sum of the payments after inflation has eroded the cash flow.
You can also use NPV with the COMPOUND function (see COMPOUND) to calculate the end value of a series of payments, reinvested over a period of time. Suppose you know that you’ll receive payments of $400, $300, $300, and $200 over the next four years, and that you’ll reinvest these payments in an account bearing interest of 12% per annum, compounded over the four years. After the four years, how much will be in the account? This is given using the following formula:
COMPOUND(NPV(13.25%, 400, 300, 300, 200), 13.25%, 4)
Ability calculates the answer as $1505.51.
See also: