The NPV (Net Present Value) and IRR
(Internal Rate of Return) functions are perhaps the most commonly used
financial analysis functions. This chapter provides many examples that use
these functions for various types of financial analysis.
Using
the NPV Function
The NPV function returns the sum of a
series of cash flows, discounted to the present day using a single discount
rate. The cash flows don't have to be the same amount, but they do have to be
at regular intervals (for example, monthly). The syntax for Excel's NPV
function is shown here; arguments in bold are required:
NPV(rate,value1,value2,
...)
Cash inflows are represented as
positive values, and cash outflows are negative values. The NPV function is
subject to the same restrictions that apply to financial functions, such as PV,
PMT, FV, NPER, and RATE.
If the discounted negative flows exceed
the discounted positive flows, the function will return a negative amount.
Alternatively, if the discounted positive flows exceed the discounted negative
flows, the NPV function will return a positive amount.
The rate argument is the discount rate-the rate at which future cash
flows are discounted. It represents the rate of return the investor requires.
If NPV returns zero, this indicates that the future cash flows will provide a
rate of return exactly equal to the specified discount rate.
If the NPV is positive, this indicates
that the future cash flows provide a better rate of return than the specified
discount rate. The positive amount returned by NPV is the amount that the
investor could add to the initial cash flow (called Point 0) to get the exact rate of return specified.
As you may have guessed, a negative NPV
indicates that the investor does not get the required discount rate, often
called a hurdle rate. To
achieve the desired rate, the investor would have to reduce the initial cash
outflow (or increase the initial cash inflow) by the amount returned by the
negative NPV.
|
Note:
|
The discount rate used must be a
single effective rate for the period used for the cash flows. Therefore, if
flows are set out monthly, you must use the monthly effective rate.
|
Excel's NPV function assumes that the
first cash flow is received at the end
of the first period.
The point of a NPV calculation is to
determine whether an investment will provide an appropriate return. The typical
sequence of cash flows is an initial cash outflow followed by a series of cash
inflows. For example, you buy a hot dog cart and some hot dogs (initial
outflow) and spend the summer months selling them on a street corner (series of
inflows). If you include the initial cash flow as an argument, NPV will assume
the initial investment isn't made right now but instead at the end of the first
month (or some other time period).
shows three calculations using the same
cash flows: a $20,000 initial outflow, a series of monthly inflows, and an 8%
discount rate.
The formulas in row 9 are as follows:
B9: =NPV(0.08,B4:B8)
C9:
=NPV(0.08,C5:C8)+C4
D9:
=NPV(0.08,D4:D8)*(1+0.08)
The formula in B9 produces a result
that's different than the other two. It assumes the $20,000 investment is made
one month from now. There are applications where this is useful, but they
rarely if ever involve an initial investment. The other two formulas answer the
question of whether a $20,000 investment right now will earn 8%, assuming the
future cash flows. The formulas in C9 and D9 produce the same result and can be
used interchangeably.
|
On the CD
|
All the examples in this section are
available in the workbook net present value.xlsx on the companion
CD-ROM.
|
Many NPV calculation start with an
initial cash outlay followed by a series of inflows. In this example, the Time
0 cash flow is the purchase of a snow plow. Over the next ten years, the plow
will be used to plow driveways and earn revenue. Experience shows that such a snow
plow lasts ten years. After that time, it will be broken-down and worthless.
shows a worksheet set up to calculate the net present value of the future cash
flows associated with buying the plow.
=NPV($B$3,B7:B16)+B6
The NPV is negative, so this analysis
indicates that buying the snow plow is not a good investment. Several factors
that influence the result:
- First, I defined a "good investment" as one that returns 10% when I set the discount rate. If you settle for a lesser return, the result might be satisfactory.
- The future cash flows are generally, but not always, estimates. In this case, the potential plow owner assumes increasing revenue over the ten-year period. Unless he has a ten-year contract to plow snow that sets forth the exact amounts to be received, the future cash flows are educated guesses at how much money can be made.
- Finally, if you can get the snow plow dealer to lower his price, the ten-year investment may prove worthwhile.
NO INITIAL INVESTMENT
You can look at the snow plow example
in another way. In the previous example, you knew the cost of the snow plow and
included that as the initial investment. That example tells you whether the
initial investment would produce a 10% return. You can also use NPV to tell
what initial investment is required to produce the required return. That is,
how much should you pay for the snow plow. shows the calculation of the net
present value of a series of cash flow with no initial investment.
=NPV($B$3,B8:B17)+B7
If the future snow plow owner can buy
the snow plow for $180,119.70, it will result in a 10% rate of return (assuming
the cash flow projections are accurate, of course). The formula adds the value
in B7 to the end to be consistent with the formula from the previous example.
Obviously, because the initial cash flow is zero, adding B7 is superfluous.
shows an example in which the initial
cash flow (the Time 0 cash flow) is an inflow. Like the previous example, this
calculation returns the amount of an initial investment that will be necessary
to achieve the desired rate of return. In this example, however, the initial
investment entitles you to receive the first inflow immediately.
The net present value calculation is in
cell B16, which contains the following formula:
=NPV(B3,B7:B13)+B6
This example might seem unusual, but it
is common in real estate situations in which rent is paid in advance. This
calculation indicates that you can pay $197,292.96 for a rental property that
pays back the future cash flows in rent. The first year's rent, however, is due
immediately. Therefore, the first year's rent is shown at Time 0.
The previous example is missing one key
element: namely, the disposition of the property after seven years. You could
keep renting it forever, in which case you need to increase the number of cash
flows in the calculation. Or you could sell it, as shown.
=NPV(B3,D7:D13)+D6
In this example, the investor can pay
$428,214.11 for the rental property, collect rent for seven years, sell the
property for $450,000, and make 10% on his investment.
This example uses the same cash flows
as the previous example except that you know how much the owner of the
investment property wants. It represents a typical investment example in which
the aim is to determine if, and by how much, an asking price exceeds a desired
rate of return, as you can see.
The following formula
indicates that at a $360,000 asking price, the discounted positive cash at the
desired rate of return is $68,214.11:
=NPV(B3,D9:D15)+D8
The resulting positive net present
value means that the investor can pay the asking price and make more than his
desired rate of return. In fact, he could pay $68,214.11 more than the asking
price and still meet his objective.
Although the typical investment
decision may consist of an initial cash outflow resulting in periodic inflows,
that's certainly not always the case. The flexibility of NPV is that you can
have varying amounts, both positive and negative, at all the points in the cash
flow schedule.
In this example, a company wants to
roll out a new product. It needs to purchase equipment for $475,000 and will
need to spend another $225,000 to overhaul the equipment after five years.
Also, the new product won't be profitable at first but will be eventually.
The
positive net present value indicates that the company should invest in the
equipment and start producing the new product. If it does, and the estimates of
gross margin and expenses are accurate, the company will earn better than 10%
on its investment.
In the previous examples, the discount
rate conveniently matched the time periods used in the cash flow. Often, you'll
be faced with a mismatch of rate and time periods. The most common situation
occurs when the desired rate of return is an annual effective rate and cash
flows are monthly or quarterly. In this case, you need to convert the discount
rate to the appropriate period.
shows a rental of $12,000 paid
quarterly in advance. It also shows an initial price of $700,000 and a sale
(after three years) for $900,000. Note that because rent is paid in advance,
the purchaser gets a cash adjustment to the price. However, at the end of three
years (12 quarters), the same rule applies, and the rent payable for the next
quarter is received by the new owner. If you discount at 7% per annum effective,
this shows an NPV of $166,099.72.
In some situations,
determining the frequency of cash flows is simple. With rent, for instance, the
lease agreement spells out how often rent is paid. When the future cash flow is
revenue from the sale of a product, the figures are usually estimates. In those
cases, determining whether to state the cash flows monthly, quarterly, or
annually is not so clear. Generally, you should use a frequency that matches
the accuracy of your data. That is, if you estimate sales on an annual basis,
don't divide that number by 12 to arrive at a monthly estimate.
For an illustration of the difference
that can result from different frequencies. It shows the same data, but this
time, the calculations are based on the assumption that the rent of $48,000 per
annum is paid annually in arrears. Still discounting at 7% per annum effective,
you get an NPV of $160,635.26.
This section presents two examples that
use the NPV function to calculate future values or accumulations. These
examples take advantage of the fact that
FV = PV * (1 + Rate)
The data for this example is. The net
present value calculation is performed by the formula in cell B15:
=NPV(B3,B7:B13)+B6
=(NPV(B3,B7:B13)+B6)*(1+B3)^7
The result is also computed in column
D, in which formulas calculate a running balance of the interest. Interest is
calculated using the interest rate multiplied by the previous month's balance.
The running balance is the sum of the previous balance, interest, and the
current month's cash flow.
It is important to properly sign the
cash flows. Then, if the running balance for the previous month is negative,
the interest will be negative. Signing the flows properly and using addition is
preferable to using the signs in the formulas for interest and balance.
covers the use of the PMT function to
calculate payments equivalent to a given present value. Similarly, you can use
the NPV function, nested in a PMT function, to calculate an equivalent
single-level payment to a series of changing payments.
This is a typical problem where you
require a time-weighted average single payment to replace a series of varying
payments. An example is an agreement in which a schedule of rising rental
payments is replaced by a single payment amount. In the example, the following
formula (in cell C25) returns $10,923.24, which is the payment amount that
would substitute for the varying payment amounts in column B:
=PMT(C5,C4,-B23,0,C6)