NPV() is a specialized function that can help you decide whether to make an investment
or embark on a business venture by calculating Net Present Value. To understand NPV, we first need to understand the concept of Present Value, the value
that a projected investment has today.
If you have an investment that earns 5 percent monthly interest and is worth $200
at maturity (one month), its Present Value is about $190.00.
Net Present Value (NPV) is similar to Present Value, except that it applies to a
series of cash flows - profit or loss -- over time, rather than a fixed rate
of return.
In practice, investors almost always compute NPV to compare the present value of
an investment with an investment having a fixed return. The idea is that in order
for an investment to be worthwhile, the venture investment must exceed a specified
rate of return.
If you know that you can get a 5% fixed rate of return from your bank (yeah, right?)
you wouldn't consider using the money to open an Internet Cafe that's projected
to earn only a 4% return.
The NPV() function makes its calculation by examining cash flow over a series of
periods (usually years). You specify how much the investment cost you, along with how much you expect to receive
over the holding period, and NPV() calculates how that compares with an investment
having a fixed rate.
You have to choose this rate, called the Discount Rate. If the final NPV is negative,
then you would have been better off with the fixed rate investment.
If the NPV is positive, your venture is exceeding the fixed rate option.
Let's say you want to start that Internet Cafe. You're prepared to invest $150,000
to start it, and you expect to see net returns of $20,000, $19,000, $20,000,
$21,000, and $22,000 during a 5 year holding period. Then you expect to sell
the business for $180,000 and retire.
Based on this scenario, you can compute the Net Present Value of the business using
the NPV() formula. For the income stream you could use =NPV(D9,B4:F4) where D9
is your fixed rate of 5%, and B4:F4 are the five annual income streams. But you
also need to compute the
NPV of the selling price: =NPV(D9,0,0,0,0,G4) where G4 is the sales price. So in
this case, the total NPV is $229,000.
We can now compute the IRR - Internal Rate of Return, by applying the IRR formula
to all of the annual income streams (including the initial cost). This computes
to 14.26% in this case.
Buying a Business
Income -->
Cost to Buy Year 1 Year 2 Year 3 Year 4 Year 5 Selling Price
-150,000 20,000 19,000 20,000 21,000 22,000 180,000
Total Profit: $132,000.00
NPV of income stream: $88,072.26 Rate for NPV
NPV of selling price: $141,034.71 5%
Total NPV: $229,106.97
IRR: 14.26%
So do you really want to run an Internet Cafe? You can download the sample Excel workbook here.