Microsoft Office Tutorials and References

In Depth Information

**The NPV function**

Assuming you could invest your money in a five-year CD money-market account at 2.1

percent, we’ll use 2.1 percent as the discount rate of the investment. (Because this discount

rate is a sort of hurdle over which an investment must leap before it becomes attractive to

you, it’s often called the
hurdle rate
.) To determine the present value of this investment, use

the formula =PV(2.1%, 5, 1000), which returns the value –4699.81, meaning you should be

willing to spend $4699.81 now to receive $5,000 over the next five years. (Negative values

indicate money going out; positive values indicate money coming in.) Because your

investment is only $4,000, you can surmise that this is an excellent investment.

Suppose you’re offered $5,000 at the end of five years instead of $1,000 for each of the

next five years. Is the investment still as attractive? To find out, use the formula =PV(2.1%,

5,, 5000). (Include a comma as a placeholder for the unused
payment
argument.) This

formula returns the present value –4506.52, which means that at a hurdle rate of 2.1 percent,

you should be willing to spend $4,506.52 to receive $5,000 in five years. Although the

proposal might not be as attractive under these terms, it’s still acceptable because your

investment is only $4,000. However, it also makes a guaranteed 2.1 percent CD look a bit more

attractive by comparison.

The NPV function

The NPV function calculates the net present value, which is another common method for

determining the profitability of an investment. In general, any investment that yields a net

present value greater than zero is considered profitable. This function takes the arguments

rate
,
value1
,
value2
, and so on; for definitions of these arguments, see Table 16-1. You can

use as many as 254 separate inflow values as arguments, but you can include any number

of values by using an array as an argument.

NPV differs from PV in two important respects. Whereas PV assumes constant inflow values,

NPV allows variable payments. The other major difference is that PV allows payments and

receipts to occur at either the beginning or the end of the period, whereas NPV assumes

that all payments and receipts are evenly distributed and that they occur at the end of each

period. If the cost of the investment must be paid up front, you should not include the cost

as one of the function’s inflow arguments but should subtract it from the result of the

function. On the other hand, if the cost must be paid at the end of the first period, you should

include it as a negative first inflow argument. Let’s consider an example to help clarify this

distinction.

Suppose you are contemplating an investment on which you expect to incur a loss of

$85,000 at the end of the first year, followed by gains of $95,000; $140,000; and $185,000

at the ends of the second, third, and fourth years. You will invest $250,000 up front, and the

hurdle rate is 8 percent. To evaluate this investment, use the formula =NPV(8%, –85000,

95000, 140000, 185000) –250000.