C ONCLUSIONES

Questions answered in this chapter:

■ 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 8-1.

■ 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 first appears. To determine which investment is better, you 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.

FIGURE 8-1 To determine which investment is better, you need to calculate net present value.

Download from Wow! eBook <www.wowebook.com>

58 Microsoft Excel 2010: Data Analysis and Business Modeling

Answers to This Chapter’s Questions

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 you have $1 today and you 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. You can say, in some sense, that $1 today equals

$(1+r) a year from now and $(1+r)² two years from now. In general, you can say that $1 today is equal to $(1+r)ⁿ n years from now. As an equation, this calculation can be expressed as follows:

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

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

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

This result tells you 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. You 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 you use a value of .02 for r, Investment 2 has a larger NPV because when r is very small, later cash flows are not dis-counted 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 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 in-terest 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 for-mula 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 as-sumes 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 al-ways occur at the beginning of a year, you can use the approach I described 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

60 Microsoft Excel 2010: Data Analysis and Business Modeling

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 you multiply the result obtained with the NPV func-tion by (1+r), you 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. You can also compute the NPV of Investment 1 in cell D11 with the formula (1+r_)*C14. Of course, you again obtain an NPV of $277.78.

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

For any Year n,

$ 1 + r

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

1 + r

If you assume the cash flows for Investment 1 occur mid year, you can compute the NPV of the mid-year version of Investment 1 in cell C17 with the formula SQRT(1+r_)*C14. You 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 4/08/13, the NPV is computed in April 8, 2013 dollars.

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

■ $300 on August 15, 2013

■ $400 on January 15, 2014

■ $200 on June 25, 2014

■ $100 on July 3, 2015

If the annual interest rate is 10 percent, what is the NPV of these cash flows? I entered 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, 2013 dollars because that is the first date listed. This project would have an NPV, in April 8, 2013 dollars, of $20.63.

FIGURE 8-2 Using the XNPV function.

The computations performed by the XNPV function are as follows:

1. Compute the number of years after April 8, 2013, that each date occurred. (I 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, 2013 cash flow is discounted by 1

(1 + 0.1)³⁵³⁴ = 0.967

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

Suppose that today’s date is actually July 11, 2010. 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 8-3 and the Today worksheet.) The NPV of the project in today’s dollars is $15.88.

FIGURE 8-3 NPV converted to today’s dollars.

I’ll close by noting that if a cash flow is left blank, the NPV function ignores both the cash flow and the period. If a cash flow is left blank, the XNPV function returns a #NUM error.

62 Microsoft Excel 2010: Data Analysis and Business Modeling

Problems

1. An NBA player is to receive a $1,000,000 signing bonus today and $2,000,000 one year, two years, and three years from now. Assuming r=0.10 and ignoring tax considerations, would he be better off receiving $6,000,000 today?

2. A project has the following cash flows:

Now One year from now Two years from now Three years from now

–$4 million $4 million $4 million –$3 million

If the company’s cost of capital is 15 percent, should it proceed with the project?

3. Beginning one month from now, a customer will pay his Internet provider $25 per month for the next five years. Assuming all revenue for a year is received at the middle of a year, estimate the NPV of these revenues. Use r=0.15.

4. Beginning one month from now, a customer will pay $25 per month to her Internet provider for the next five years. Assuming all revenue for a year is received at the middle of a year, use the XNPV function to obtain the exact NPV of these revenues. Use r=0.15.

5. Consider the following set of cash flows over a four-year period. Determine the NPV of these cash flows if r=0.15 and cash flows occur at the end of the year.

Year 1 2 3 4

-$600 $550 -$680 $1,000

6. Solve Problem 5 assuming cash flows occur at the beginning of each year.

7. Consider the following cash flows:

If today is November 1, 2001, and r=0.15, what is the NPV of these cash flows?

8. After earning an MBA, a student will begin working at an $80,000-per-year job on September 1, 2005. She expects to receive a 5 percent raise each year until she retires on September 1, 2035. If the cost of capital is 8 percent a year, determine the total present value of her before-tax earnings.

9. Consider a 30-year bond that pays $50 at the end of Years 1–29 and $1,050 at the end of Year 30. If the appropriate discount rate is 5 percent per year, what is a fair price for this bond?

63

Chapter 9