NPV

Returns the net present value of a series of future receipts at a fixed periodic interest rate.

Syntax:

NPV(interest:number, range)

interest is the interest rate,
range is the range of cells containing the amounts of the receipts

It is assumed that the receipts are at equal intervals and take place at the end of the period.

The following formula is used to calculate net present value:

net present value = Σi=n ( vi ÷ (1 + interest)i )

where i is the current iteration,
n is the number of cash flows (i.e. the number of elements in the range) and
v is the value of the current iteration:

Example:

Cells B10 to B14 each contain £120. Given an interest rate of 10% then the formula

NPV(0.10, B10:B14)

returns the number 500.38 (pounds), the present value of the future receipts.