NPV

Returns the net present value of an investment with regular cash payments.

Syntax:

NPV(discountrate, payment1, payment2, ... payment30)

payment1 to payment30 are up to 30 numbers or ranges containing numbers, representing payments made at the end of each of a series of fixed length periods. The payments may be both positive and negative, for income and outgoing.

discountrate is the discount rate (expressed as a fraction of 1) which you consider applies to one single period. It is assumed to be constant for all periods.

NPV calculates the net present value using the formula:

$\sum_{i = 1}^{n} \frac{p a y m e n t _{i}}{( 1 + d i sco u n t r a t e ) ^{i}}$

Example:

NPV(8.75%, 1000, 2000, 3000)

where the discount rate 8.75% is the assumed competitive return over one year, and 1000 is to be paid at the end of year 1, 2000 at the end of year 2 and 3000 at the end of year 3, returns 4943.21 as currency.

NPV(0.0875, A1:A3)

where cells A1:A3 contain 1000, 2000 and 3000, returns 4943.21 similarly.

Application:

Let's consider an application of the NPV function: A small business is considering purchasing a new coffee roaster.

The owner needs to decide if this investment is worthwhile. The initial cost of the roaster is a significant upfront expense, but it is expected to generate positive cash flows over the next five years.

Here's the data for our example:

Initial Investment: -$50,000 (This is a negative cash flow as it's an outflow of cash).
Discount Rate (Cost of Capital): 8% per year. This represents the minimum return the business needs to make on its investments to justify the risk.
Projected Annual Cash Flows: The roaster is expected to increase net income (and thus cash flow) as follows:
- Year 1: $15,000
- Year 2: $18,000
- Year 3: $20,000
- Year 4: $16,000
- Year 5: $12,000

The NPV function calculates the present value of each of these future cash flows and then subtracts the initial investment. The formula for NPV is:

$NP V = \sum_{t = 1}^{n} \frac{C F _{t}}{( 1 + r ) ^{t}} - C_{0}$

Where:

CFt = Cash flow in period t
r = Discount rate
t = Period number
C0 = Initial investment

Let's create a table to visualize the calculation:

	Year	Cash Flow	Present Value Calculation	Present Value
	A	B	C	D
1	0	-$50,000.00	-$50,000	-$50,000.00
2	1	$15,000.00	$15,000 / (1 + 0.08)¹	$13,888.89
3	2	$18,000.00	$18,000 / (1 + 0.08)²	$15,432.10
4	3	$20,000.00	$20,000 / (1 + 0.08)³	$15,876.64
5	4	$16,000.00	$16,000 / (1 + 0.08)⁴	$11,760.48
6	5	$12,000.00	$12,000 / (1 + 0.08)⁵	$8,167.00
7	NPV			$15,125.11

Given that your calculation for the Net Present Value is $15,125.11, this is what that result means:

The positive NPV of $15,125.11 signifies that the investment in the coffee roaster is expected to generate a return that is greater than the required rate of return (the 8% discount rate).

In simple terms, after accounting for the time value of money and the initial cost of the roaster, the project is expected to increase the value of your business by $15,125.11 in today's dollars. This amount represents the "net gain" from the investment, above and beyond simply recovering the initial cost of capital.

Therefore, an NPV of $15,125.11 indicates that this is a financially sound investment and a worthwhile decision to proceed with.