Principles of Finance with Excel (Основы финансов c Excel)

Подождите немного. Документ загружается.

PFE, Chapter 3: Capital budgeting 5

the project for in the market.

The net present value is the wealth increment produced by the

project, so that NPV > 0 means that a project adds to our wealth:



()

Initial cash

flow required

Market value

to implement

of future cash

the project.

flows.

This is usually

a negative number.

NPV CF

↑

∑



 

An initial example

To set the stage let’s assume that you’re trying to decide whether to undertake one of two

projects. Project A involves buying expensive machinery which produces a better product at a

lower cost. The machines for Project A cost $1000 and if purchased you anticipate that the

project will produce cash flows of $500 per year for the next 5 years. Project B’s machines are

cheaper, costing $800, but they produce smaller annual cash flows of $420 per year for the next

5 years. We’ll assume that the correct discount rate is 12 percent.

Suppose we apply the NPV criterion to Projects A and B:

This assumes that the discount rate is “correctly chosen,” by which we mean that it is appropriate to the riskiness

of the project’s cash flows. For the moment we fudge the question of how to choose discount rates; this topic is

discussed in Chapter 5.

PFE, Chapter 3: Capital budgeting 6

ABC D

Discount rate 12%

Year Project

Project B

0 -1000 -800

1 500 420

2 500 420

3 500 420

4 500 420

5 500 420

NPV 802.39 714.01 <-- =NPV($B$2,C6:C10)+C5

TWO PROJECTS

Both projects are worthwhile, since each has a positive NPV. If we have to choose

between the projects, then Project A is preferred to Project B because it has the higher NPV.

An Excel Note

We reiterate our Excel note from Chapter 1: Excel’s NPV function computes the present

value of future cash flows; this does not correspond to the finance notion of NPV, which

includes the initial cash flow. In order to calculate the finance NPV concept in the spreadsheet,

we have to include the initial cash flow. Hence in cell B12, the NPV is calculated as

=NPV($B$2,B6:B10)+B5 and in cell C12 the calculation is =NPV($B$2,C6:C10)+C5.

3.2. The IRR rule for judging investments

An alternative to using the NPV criterion for capital budgeting is to use the internal rate

of return (IRR). Recall from Chapter 1 that the IRR is defined as the discount rate for which the

NPV equals zero. It is the compound rate of return which you get from a series of cash flows.

Here are the two decision rules for using the IRR in capital budgeting:

PFE, Chapter 3: Capital budgeting 7

The IRR rule for deciding whether a specific investment is worthwhile: Suppose we

are considering a project that has cash flows CF

, CF

, ...., CF

()

() () ()

0 0

11 1

IRR is an interest rate such that :

CF CF

IRR

IRR IRR k

++ ++ =+ =

++ +

∑

…

Rule: If the appropriate discount rate for a project is r, you should accept the project if

its IRR > r and reject it if its IRR < r.

The logic behind the IRR rule is that the IRR is the compound return you get from the

project. Since r is the project’s required rate of return, it follows that if IRR > r, you get more

than you require.

The IRR rule for deciding between two competing projects: Suppose you are trying

to decide between two mutually exclusive projects A and B (meaning: both projects are

ways of achieving the same objective, and you will choose at most one of the projects).

Suppose project A has cash flows

01 2

, , ,...,

AAA A

CF CF CF CF and that project B has cash

flows

01 2

, , ,...,

BBB B

CF CF CF CF .

Rule: Project A is preferred to project B if IRR(A) > IRR(B).

Again the logic is clear: Since the IRR gives a project’s compound rate of return, if we

choose between two projects using the IRR rule, we prefer the higher compound rate of return.

Applying the IRR rule to our Projects A and B, we get:

PFE, Chapter 3: Capital budgeting 8

ABC D

Discount rate 12%

Year Project

Project B

0 -1000 -800

1 500 420

2 500 420

3 500 420

4 500 420

5 500 420

IRR 41% 44% <-- =IRR(C5:C10)

TWO PROJECTS

Both Project A and Project B are worthwhile, since each has an IRR > 12%, which is our

relevant discount rate. If we have to choose between the two projects by using the IRR rule,

Project B is preferred to Project A because it has a higher IRR.

3.3. NPV or IRR, which to use?

We can sum up the NPV and the IRR rules as follows:

PFE, Chapter 3: Capital budgeting 9

“Yes or No”:

Choosing whether to

undertake a single project

“Project ranking”:

Comparing two mutually

exclusive projects

NPV criterion

The project should be

undertaken if its NPV > 0:

Project A is preferred to

project B if

NPV(A) > NPV(B)

IRR criterion

The project should be

undertaken if its IRR > r,

where r is the appropriate

discount rate.

Project A is preferred to

project B if

IRR(A) > IRR(B).

Both the NPV rules and the IRR rules look logical. In many cases your investment

decision—to undertake a project or not, or which of two competing projects to choose—will be

the same whether or not you use NPV or IRR. There are some cases, however (such as that of

Projects A and B illustrated above), where NPV and IRR give different answers. In our present

value analysis Project A won out because is NPV was greater than Project B’s. In our IRR

analysis of the same projects, Project B was chosen because it had the higher IRR. In such cases

we should always use the NPV to decide between projects. The logic is that if individuals are

interested in maximizing their wealth, they should use NPV, which measures the incremental

wealth from undertaking a project.

PFE, Chapter 3: Capital budgeting 10

3.4. The “Yes-No” criterion: When do IRR and NPV give the same answer?

Consider the following project: The initial cash flow of –$1,000 represents the cost of

the project today, and the remaining cash flows for years 1-6 are projected future cash flows.

The discount rate is 15 percent:

AB C

Discount rate 15%

Year Cash flow

0 -1,000

1 100

2 200

3 300

4 400

5 500

6 600

PV of future cash flows 1,172.13 <-- =NPV(B2,B6:B11)

NPV 172.13 <-- =B5+NPV(B2,B6:B11)

IRR 19.71% <-- =IRR(B5:B11)

SIMPLE CAPITAL BUDGETING EXAMPLE

The NPV of the project is $172.13, meaning that the present value of the project’s future

cash flows ($1,172.13) is greater than the project’s cost of $1,000.00. Thus the project is

worthwhile.

If we graph the project’s NPV we can see that the IRR—the point where the NPV curve

crosses the x-axis—is very close to 20% As you can see in cell B15, the actual IRR is 19.71%.

PFE, Chapter 3: Capital budgeting 11

AB C DEFG

Discount

rate

NPV

0% 1,100.00 <-- =$B$5+NPV(A19,$B$6:$B$11)

3% 849.34 <-- =$B$5+NPV(A20,$B$6:$B$11)

6% 637.67

9% 457.83

12% 304.16

15% 172.13

18% 58.10

21% -40.86

24% -127.14

27% -202.71

30% -269.16

NPV of Cash Flows

-400

-200

200

400

600

800

1,000

1,200

0% 3% 5% 8% 10% 13% 15% 18% 20% 23% 25% 28% 30%

Discount rate

NPV

Accept or reject? Should we undertake the project?

It is clear that the above project is worthwhile:

• Its NPV > 0, so that by the NPV criterion the project should be accepted.

• Its IRR of 19.71% > the project discount rate of 15%, so that by the IRR criterion the

project should be accepted.

A general principle

We can derive a general principle from this example:

For conventional projects, projects with an initial negative cash flow and subsequent

non-negative cash flows (

012

0, 0, 0,..., 0

CF CF CF CF

≥≥ ≥), the NPV and IRR criteria

lead to the same “Yes-No” decision: If the NPV criterion indicates a “Yes” decision,

then so will the IRR criterion (and vice versa).

PFE, Chapter 3: Capital budgeting 12

3.5. Do NPV and IRR produce the same project rankings?

In the previous section we saw that for conventional projects, NPV and IRR give the

same “Yes-No” answer about whether to invest in a project. In this section we’ll see that NPV

and IRR do not necessarily rank projects the same, even if the projects are both conventional.

Suppose we have two projects and can choose to invest in only one. The projects are

mutually exclusive: They are both ways to achieve the same end, and thus we would choose only

one. In this section we discuss the use of NPV and IRR to rank the projects. To sum up our

results before we start:

• Ranking projects by NPV and IRR can lead to possibly contradictory results. Using the

NPV criterion may lead us to prefer one project whereas using the IRR criterion may lead

us to prefer the other project.

• Where a conflict exists between NPV and IRR, the project with the larger NPV is

preferred. That is, the NPV criterion is the correct criterion to use for capital budgeting.

This is not to impugn the IRR criterion, which is often very useful. However, NPV is

preferred over IRR because it indicates the increase in wealth which the project produces.

An example

Below we show the cash flows for Project A and Project B. Both projects have the same

initial cost of $500, but have different cash flow patterns. The relevant discount rate is 15

percent.

PFE, Chapter 3: Capital budgeting 13

ABC D

Discount rate 15%

Year Project

Project B

0 -500 -500

1 100 250

2 100 250

3 150 200

4 200 100

5 400 50

NPV 74.42 119.96 <-- =C5+NPV(B2,C6:C10)

IRR 19.77% 27.38% <-- =IRR(C5:C10)

RANKING PROJECTS WITH NPV AND IRR

Comparing the projects using IRR: If we use the IRR rule to choose between the

projects, then B is preferred to A, since the IRR of Project B is higher than that of Project A.

Comparing the projects using NPV: Here the choice is more complicated. When the

discount rate is 15% (as illustrated above), the NPV of Project B is higher than that of Project A.

In this case the IRR and the NPV agree: Both indicate that Project B should be chosen. Now

suppose that the discount rate is 8%; in this case the NPV and IRR rankings conflict:

ABC D

Discount rate 8%

Year Project

Project B

0 -500 -500

1 100 250

2 100 250

3 150 200

4 200 100

5 400 50

NPV 216.64 212.11 <-- =C5+NPV(B2,C6:C10)

IRR 19.77% 27.38% <-- =IRR(C5:C10)

RANKING PROJECTS WITH NPV AND IRR

In this case we have to resolve the conflict between the ranking on the basis of NPV (A is

preferred) and ranking on the basis of IRR (B is preferred). As we stated in the introduction to

PFE, Chapter 3: Capital budgeting 14

this section, the solution to this question is that you should choose on the basis of NPV. We

explore the reasons for this later on, but first we discuss a technical question.

Why do NPV and IRR give different rankings?

Below we build a table and graph that show the NPV for each project as a function of the

discount rate:

ABC D EFGH

Project

NPV

Project B

NPV

0% 450.00 350.00 <-- =$C$5+NPV(A17,$C$6:$C$10)

2% 382.57 311.53 <-- =$C$5+NPV(A18,$C$6:$C$10)

4% 321.69 275.90

6% 266.60 242.84

8% 216.64 212.11

10% 171.22 183.49

12% 129.85 156.79

14% 92.08 131.84

16% 57.53 108.47

18% 25.86 86.57

20% -3.22 66.00

22% -29.96 46.66

24% -54.61 28.45

26% -77.36 11.28

28% -98.39 -4.93

30% -117.87 -20.25

TABLE OF NPVs AND DISCOUNT RATES

-200

-100

100

200

300

400

500

0% 5% 10% 15% 20% 25% 30%

Discount rate

NPV

Project A

NPV

Project B

NPV

From the graph you can see why contradictory rankings occur:

• Project B has a higher IRR (27.38%) than project A (19.77%). (Remember that the IRR

is the point at which the NPV curve cuts the x-axis.)

• When the discount rate is low, Project A has a higher NPV than project B, but when the

discount rate is high, project B has a higher NPV. There is a crossover point (in the next

subsection you will see that this point is 8.51%) that marks the disagreement/agreement

range. Thus: