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
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.
Search JabSto ::

Custom Search