The NPV (Net Present Value) and IRR(Internal Rate of Return) functions are perhaps the most commonly usedfinancial analysis functions. This chapter provides many examples that usethese functions for various types of financial analysis.
Usingthe NPV Function
The NPV function returns the sum of aseries of cash flows, discounted to the present day using a single discountrate. The cash flows don't have to be the same amount, but they do have to beat regular intervals (for example, monthly). The syntax for Excel's NPVfunction is shown here; arguments in bold are required:
NPV(rate,value1,value2,...)
Cash inflows are represented aspositive values, and cash outflows are negative values. The NPV function issubject to the same restrictions that apply to financial functions, such as PV,PMT, FV, NPER, and RATE.
If the discounted negative flows exceedthe discounted positive flows, the function will return a negative amount.Alternatively, if the discounted positive flows exceed the discounted negativeflows, the NPV function will return a positive amount.
The rate argument is the discount rate-the rate at which future cashflows are discounted. It represents the rate of return the investor requires.If NPV returns zero, this indicates that the future cash flows will provide arate of return exactly equal to the specified discount rate.
If the NPV is positive, this indicatesthat the future cash flows provide a better rate of return than the specifieddiscount rate. The positive amount returned by NPV is the amount that theinvestor 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 NPVindicates that the investor does not get the required discount rate, oftencalled a hurdle rate. Toachieve the desired rate, the investor would have to reduce the initial cashoutflow (or increase the initial cash inflow) by the amount returned by thenegative 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 thefirst cash flow is received at the endof the first period.
The point of a NPV calculation is todetermine whether an investment will provide an appropriate return. The typicalsequence of cash flows is an initial cash outflow followed by a series of cashinflows. For example, you buy a hot dog cart and some hot dogs (initialoutflow) and spend the summer months selling them on a street corner (series ofinflows). If you include the initial cash flow as an argument, NPV will assumethe initial investment isn't made right now but instead at the end of the firstmonth (or some other time period).
shows three calculations using the samecash 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 resultthat's different than the other two. It assumes the $20,000 investment is madeone month from now. There are applications where this is useful, but theyrarely if ever involve an initial investment. The other two formulas answer thequestion of whether a $20,000 investment right now will earn 8%, assuming thefuture cash flows. The formulas in C9 and D9 produce the same result and can beused 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 aninitial cash outlay followed by a series of inflows. In this example, the Time0 cash flow is the purchase of a snow plow. Over the next ten years, the plowwill be used to plow driveways and earn revenue. Experience shows that such a snowplow 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 cashflows associated with buying the plow.
=NPV($B$3,B7:B16)+B6
The NPV is negative, so this analysisindicates that buying the snow plow is not a good investment. Several factorsthat 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 examplein another way. In the previous example, you knew the cost of the snow plow andincluded that as the initial investment. That example tells you whether theinitial investment would produce a 10% return. You can also use NPV to tellwhat 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 netpresent value of a series of cash flow with no initial investment.
=NPV($B$3,B8:B17)+B7
If the future snow plow owner can buythe snow plow for $180,119.70, it will result in a 10% rate of return (assumingthe cash flow projections are accurate, of course). The formula adds the valuein 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 initialcash flow (the Time 0 cash flow) is an inflow. Like the previous example, thiscalculation returns the amount of an initial investment that will be necessaryto achieve the desired rate of return. In this example, however, the initialinvestment entitles you to receive the first inflow immediately.
The net present value calculation is incell B16, which contains the following formula:
=NPV(B3,B7:B13)+B6
This example might seem unusual, but itis common in real estate situations in which rent is paid in advance. Thiscalculation indicates that you can pay $197,292.96 for a rental property thatpays back the future cash flows in rent. The first year's rent, however, is dueimmediately. Therefore, the first year's rent is shown at Time 0.
The previous example is missing one keyelement: namely, the disposition of the property after seven years. You couldkeep renting it forever, in which case you need to increase the number of cashflows 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 theproperty for $450,000, and make 10% on his investment.
This example uses the same cash flowsas the previous example except that you know how much the owner of theinvestment property wants. It represents a typical investment example in whichthe aim is to determine if, and by how much, an asking price exceeds a desiredrate of return, as you can see.
The following formulaindicates that at a $360,000 asking price, the discounted positive cash at thedesired rate of return is $68,214.11:
=NPV(B3,D9:D15)+D8
The resulting positive net presentvalue means that the investor can pay the asking price and make more than hisdesired rate of return. In fact, he could pay $68,214.11 more than the askingprice and still meet his objective.
Although the typical investmentdecision 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 canhave varying amounts, both positive and negative, at all the points in the cashflow schedule.
In this example, a company wants toroll out a new product. It needs to purchase equipment for $475,000 and willneed 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.
Thepositive net present value indicates that the company should invest in theequipment and start producing the new product. If it does, and the estimates ofgross margin and expenses are accurate, the company will earn better than 10%on its investment.
In the previous examples, the discountrate conveniently matched the time periods used in the cash flow. Often, you'llbe faced with a mismatch of rate and time periods. The most common situationoccurs when the desired rate of return is an annual effective rate and cashflows are monthly or quarterly. In this case, you need to convert the discountrate to the appropriate period.
shows a rental of $12,000 paidquarterly 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 threeyears (12 quarters), the same rule applies, and the rent payable for the nextquarter 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, thelease agreement spells out how often rent is paid. When the future cash flow isrevenue from the sale of a product, the figures are usually estimates. In thosecases, determining whether to state the cash flows monthly, quarterly, orannually is not so clear. Generally, you should use a frequency that matchesthe 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 differencethat can result from different frequencies. It shows the same data, but thistime, the calculations are based on the assumption that the rent of $48,000 perannum 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 thatuse the NPV function to calculate future values or accumulations. Theseexamples take advantage of the fact that
FV = PV * (1 + Rate)
The data for this example is. The netpresent 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 columnD, in which formulas calculate a running balance of the interest. Interest iscalculated using the interest rate multiplied by the previous month's balance.The running balance is the sum of the previous balance, interest, and thecurrent month's cash flow.
It is important to properly sign thecash flows. Then, if the running balance for the previous month is negative,the interest will be negative. Signing the flows properly and using addition ispreferable to using the signs in the formulas for interest and balance.
covers the use of the PMT function tocalculate payments equivalent to a given present value. Similarly, you can usethe NPV function, nested in a PMT function, to calculate an equivalentsingle-level payment to a series of changing payments.
This is a typical problem where yourequire a time-weighted average single payment to replace a series of varyingpayments. An example is an agreement in which a schedule of rising rentalpayments is replaced by a single payment amount. In the example, the followingformula (in cell C25) returns $10,923.24, which is the payment amount thatwould substitute for the varying payment amounts in column B:
=PMT(C5,C4,-B23,0,C6)