MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• What is net present value (NPV)?

• How do I use the Excel NPV function?

• How can I compute NPV when cash flows are received at the beginning of a year or in the middle of a year?

• How can I compute NPV when cash flows are received at irregular intervals?

Consider the following two investments, whose cash flows are listed in the file NPV.xlsx and shown in Figure 7-1 on the next page.

Figure 7-1: To determine which investment is better, we need to calculate net present value.

• Investment 1 requires a cash outflow of $10,000 today and $14,000 two years from now. One year from now, this investment will yield $24,000.

• Investment 2 requires a cash outflow of $6,000 today and $1,000 two years from now. One year from now, this investment will yield $8,000.

Which is the better investment? Investment 1 yields total cash flow of $0, whereas Investment 2 yields a total cash flow of $1,000. At first glance, Investment 2 appears to be better. But wait a minute. Most of the cash outflow for Investment 1 occurs two years from now, while most of the cash outflow for Investment 2 occurs today. Spending $1 two years from now doesn’t seem as costly as spending $1 today, so maybe Investment 1 is better than it first appears. To determine which investment is better, we need to compare the values of cash flows received at different points in time. That’s where the concept of net present value proves useful.

• What is net present value?

• The net present value (NPV) of a stream of cash flow received at different points in time is simply the value measured in today’s dollars. Suppose we have $1 today and we invest this dollar at an annual interest rate of r percent. This dollar will grow to 1+r dollars in the first year, (1+r)² dollars in two years, and so on. We can say in some sense that $1 today equals $(1+r) a year from now and $(1+r)² two years from now. In general, we can say that $1 today is equal to $(1+r)ⁿ n years from now. As an equation, we can express this calculation as follows:

• $1 now=$(1+r)ⁿ received n years from now

• If we divide both sides of this equation by (1+r)ⁿ, we get the following important result:

• 1/(1+r)ⁿ now=$1 received n years from now

• This result tells us how to compute (in today’s dollars) the NPV of any sequence of cash flows. You can convert any cash flow to today’s dollars by multiplying the cash flow received n years from now (n can be a fraction) by 1/(1+r)ⁿ.

• You then add up the value of the cash flows (in today’s dollars) to find the investment’s NPV. Let’s assume r is equal to 0.2. We could calculate the NPV for the two investments we’re considering as follows:

• On the basis of NPV, Investment 1 is superior to Investment 2. Although total cash flow for Investment 2 exceeds total cash flow for Investment 1, Investment 1 has a better NPV because a greater proportion of Investment 1’s negative cash flow comes later and the NPV criterion gives less weight to cash flows that come later. If we use a value of .02 for r, Investment 2 has a larger NPV because when r is very small, later cash flows are not discounted as much and NPV returns results similar to those derived by ranking investments according to total cash flow.

  Note

  I randomly chose the interest rate r=0.2, skirting the issue of how to determine an appropriate value of r. You would need to study finance for at least a year to understand the issues involved in determining an appropriate value for r. The appropriate value of r used to compute NPV is often called the company’s cost of capital. Suffice it to say that most U.S. companies use an annual cost of capital between 0.1 (10 percent) and 0.2 (20 percent). If the annual interest rate is chosen according to accepted finance practices, projects with NPV>0 increase the value of a company, projects with NPV<0 decrease the value of a company, and projects with NPV=0 keep the value of a company unchanged. A company should (if it had unlimited investment capital) invest in every available investment having positive NPV.

• To determine the NPV of Investment 1 in Excel, I first assigned the range name r_ to the interest rate (located in cell C3). I then copied the Time 0 cash flow from C5 to C7. I determined the NPV for Investment 1’s Year 1 and Year 2 cash flows by copying from D7 to E7 the formula D5/(1+r_)^D$4. The caret symbol (^), located over the number 6 on the keyboard, raises a number to a power. In cell A5, I computed the NPV of Investment 1 by adding the NPV of each year’s cash flow with the formula SUM(C7:E7). To determine the NPV for Investment 2, I copied the formulas from C7:E7 to C8:E8 and from A5 to A6.

• How do I use the Excel NPV function?

• The Excel NPV function uses the syntax NPV(rate,range of cells). This function determines the NPV for the given rate of the cash flows in the range of cells. The function’s calculation assumes that the first cash flow is one period from now. In other words, entering the formula NPV(r_,C5:E5) will not determine the NPV for Investment 1. Instead, this formula (entered in cell C14) computes the NPV of the following sequence of cash flows: –$10,000 a year from now, $24,000 two years from now, and –$14,000 three years from now. Let’s call this Investment 1 (End OF Year). The NPV of Investment 1 (End of Year) is $231.48. To compute the actual cash NPV of Investment 1, I entered the formula C7+NPV(r_,D5:E5) in cell C11. This formula does not discount the Time 0 cash flow at all (which is correct because Time 0 cash flow is already in today’s dollars), but first multiplies the cash flow in D5 by 1/1.2 and then multiplies the cash flow in E5 by 1/1.2²

• The formula in cell C11 yields the correct NPV of Investment 1, $277.78.

• How can I compute NPV when cash flows are received at the beginning of a year or in the middle of a year?

• To use the NPV function to compute the net present value of a project whose cash flows always occur at the beginning of a year, you can use the approach we followed to determine the NPV of Investment 1: separate out the Year 1 cash flow and apply the NPV function to the remaining cash flows. Alternatively, observe that for any year n, $1 received at the beginning of year n is equivalent to $(1+r) received at the end of year n. Remember that in one year, a dollar will grow by a factor (1+r). Thus, if we multiply the result obtained with the NPV function by (1+r), we can convert the NPV of a sequence of year-end cash flows to the NPV of a sequence of cash flows received at the beginning of the year. We can also compute the NPV of Investment 1 in cell D11 with the formula (1+r_)*C14. Of course, we again obtain an NPV of $277.78.

• Now suppose the cash flows for an investment occur in the middle of each year. For an organization that receives monthly subscription revenues, we can approximate the 12 monthly revenues received during a given year as a lump sum received in the middle of the year. How can we use the NPV function to determine the NPV of a sequence of mid-year cash flows? For any year n,

• received at the end of year n is equivalent to $1 received at the middle of year n because in half a year $1 will grow by a factor of

• If we assume the cash flows for Investment 1 occur mid year, we can compute the NPV of the mid-year version of Investment 1 in cell C17 with the formula SQRT(1+r_)*C14. We obtain a value of $253.58.

• How can I compute NPV when cash flows are received at irregular intervals?

• Cash flows often occur at irregular intervals, which makes computing the NPV or internal rate of return (IRR) of these cash flows more difficult. Fortunately, the Excel XNPV function makes computing the NPV of irregularly timed cash flows a snap.

• The XNPV function uses the syntax XNPV(rate,values,dates). The first date listed must be the earliest, but other dates need not be listed in chronological order. The XNPV function computes the NPV of the given cash flows assuming the current date is the first date in the sequence. For example, if the first listed date is 2/15/03, the NPV is computed in February 15, 2003 dollars.

• To illustrate the use of the XNPV function, look at the example on worksheet NPV as of first date in the file XNPV.xlsx, which is shown in Figure 7-2. Suppose that on April 8, 2001 we paid out $900. Later we receive the following amounts:

  • $300 on August 15, 2001

  • $400 on January 15, 2002

  • $200 on June 25, 2002

  • $100 on July 3, 2003

  Figure 7-2: Using the XNPV function

• If the annual interest rate is 10 percent, what is the NPV of these cash flows? We enter the dates (in Excel date format) in D3:D7 and the cash flows in E3:E7. Entering the formula XNPV(A9,E3:E7,D3:D7) in cell D11 computes the project’s NPV in April 8, 2001 dollars because that is the first date listed. This project would have an NPV, in April 8, 2001 dollars, of $20.63.

• The computations performed by the XNPV function are as follows:

  1. Compute the number of years after April 8, 2001, that each date occurred. (We did this in column F.) For example, August 15 is 0.3534 years after April 8.

  2. Discount cash flows at the rate 1/(1+rate)^{years after}.

     For example, the August 15, 2001 cash flow is discounted by

  3. Sum up in cell E11 overall cash flows: (cash flow value)*(discount factor).

• Suppose that today’s date is actually February 14, 2001. How would you compute the NPV of an investment in today’s dollars? Simply add a row with today’s date and 0 cash flow and include this row in the range for the XNPV function. (See Figure 7-3 and the Today worksheet.) The NPV of the project in today’s dollars is $20.34.

  Figure 7-3: NPV converted to today’s dollars.