Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
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. |
Definition of NPV
Excel's NPV function assumes that the first cash flow is received at the end of the first period.
| Caution | This assumption differs from the definition used by most financial calculators, and it is also at odds with the definition used by institutions such as the Appraisal Institute of America (AIA). For example, the AIA defines NPV as the difference between the present value of positive cash flows and the present value of negative cash flows. If you use Excel's NPV function without making an adjustment, the result will not adhere to this definition. |
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).
Figure 12-1 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.
NPV Function Examples
This section contains a number of examples that demonstrate the NPV function.
| On the CD | All the examples in this section are available in the workbook |
INITIAL INVESTMENT
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. Figure 12-2 shows a worksheet set up to calculate the net present value of the future cash flows associated with buying the plow.
The NPV calculation in cell B18 uses the following formula, which returns –$19,880.30:
=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. Figure 12-3 shows the calculation of the net present value of a series of cash flow with no initial investment.
The NPV calculation in cell B20 uses the following formula:
=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.
INITIAL CASH INFLOW
Figure 12-4 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.
TERMINAL VALUES
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 in Figure 12-5.
The NPV calculation in cell D15 is
=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.
INITIAL AND TERMINAL VALUES
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 in Figure 12-6.
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.
FUTURE OUTFLOWS
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.
Figure 12-7 shows a worksheet set up to account for all of these varying cash flows.
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.
MISMATCHED INTEREST RATE PERIODS
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.
| Cross Ref | See Chapter 11 for a discussion on interest rate conversion. |
Figure 12-8 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, see Figure 12-9. 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.
Using the NPV Function to Calculate Accumulated Amounts
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)
CALCULATING FUTURE VALUE
The data for this example is shown in Figure 12-10. The net present value calculation is performed by the formula in cell B15:
=NPV(B3,B7:B13)+B6
The future value is calculated using the following formula (in cell B17):
=(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.
SMOOTHING PAYMENTS
Chapter 11 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 shown in Figure 12-11, 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)
Категории