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.
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 | <>
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.
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)