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

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

PFE Chapter 1, Time value of money page 70

10 9

100*(1.06) 100*(1.06) ... 100*(1.06) ???S =+++=

For this geometric series:

100*1.06a first term==

1.06

qratio==

n = number of terms = 10

The formula gives

()

100*1.06 1

1.06

1397.16

1.06

⎛⎞

−

⎜⎟

−

⎝⎠

== =

−

, where we have done the

calculation in Excel:

AB C

First term, a 179.0848 <-- =100*1.06^10

Ratio, q 0.943396 <-- =1/1.06

Number of terms, n 10

Sum 1,397.16 <-- =B2*(1-B3^B4)/(1-B3)

Excel PV function 1,397.16 <-- =FV(6%,B4,-100,,1)

FUTURE VALUE FORMULA

Substituting symbols for the numerical values we get:

()

The Excel function

*1 1

Future value of n payments

at end of year n, at interest r FV(r,n,-1,,1)

first payment today

Payment r

↑

⎛⎞

+−

⎜⎟

⎝⎠

−



  

Present value of an annuity

We can also apply the formula to find the present value of an annuity. Suppose, for

example, that we want to calculate the present value of an annuity of $150 per year for 5 years:

PFE Chapter 1, Time value of money page 71

()

()()()()

2345

150 150 150 150 150

1.06

1.06 1.06 1.06 1.06

++++.

For this annuity:

150

1.06

a first term==

1.06

qratio==

n= number of terms = 5.

Thus the present value of the annuity becomes:

()

The Excel function

150 1

1.06 1.06

631.85 PV(6%,5,-150)

1.06

↑

⎛⎞

−

⎜⎟

−

⎝⎠

== ==

−



  

We can work this out in a spreadsheet:

AB C

First term, a 141.509434 <-- =150/1.06

Ratio, q 0.943396226 <-- =1/1.06

Number of terms, n 5

Sum 631.85 <-- =B2*(1-B3^B4)/(1-B3)

Excel PV function 631.85 <-- =PV(6%,5,-150)

ANNUITY FORMULAS

Cleaning up the formula (a bit)

Standard textbooks often manipulate the annuity formula to make it look “better.” Here’s

an example of something you might see in a textbook:

If you’re like most of the rest of humanity, you (mistakenly) thought that the first term was a = 150. But look at

the series—the first term actually is

150

1.06

. So there you are.

PFE Chapter 1, Time value of money page 72

()

annual payment

⎛⎞

−

⎜⎟

−

⎝⎠

−

⎛⎞

=−

⎜⎟

⎝⎠

This is not a different annuity formula—it’s just an algebraic simplification of the

formula we’ve been using. If you put it in Excel you’ll get the same answer (and in our opinion,

there’s no point in the simplification).

The present value of series of growing payments

Suppose we’re trying to apply the formula to the following series:

()

234

23 4 5

150* 1.10 150* 1.10 150* 1.10 150* 1.10

150

1.06

1.06 1.06 1.06 1.06

++ + +

Here there are five payments, the first of which is $150; this payment grows at an annual rate of

10%. We can apply the formula:

150

1.06

a first term==.

1.10

1.06

qratio==

n= number of terms = 5.

In the following spreadsheet, you can see that the formula and the Excel

NPV function

give the same answer for the present value:

PFE Chapter 1, Time value of money page 73

AB C

A CONSTANT-GROWTH CASHFLOW

First term, a 141.5094 <-- =150/1.06

Ratio, q 1.037736 <-- =1.1/1.06

Number of terms, n 5

Sum 763.00 <-- =B2*(1-B3^B4)/(1-B3)

Year Payment

1 150.00

2 165.00 <-- =B9*1.1

3 181.50 <-- =B10*1.1

4 199.65

5 219.62

Present value 763.00 <-- =NPV(6%,B9:B13)

Notice that the formula in cell B6 is more compact than Excel’s

NPV function. NPV

requires you to list all the payments, whereas the formula in cell B6 requires only several lines

(think about finding the present value of a very long series of growing payments—clearly the

formula is more efficient).

The present value of a constant growth annuity

An annuity is a series of annual payments; a constant growth annuity is an annuity whose

payments grow at a constant rate. Here’s an example of such a series:

()

234

23 4 5

20* 1.05 20* 1.05 20* 1.05 20* 1.05

...

1.10

1.10 1.10 1.10 1.10

++ + + +

We can fit this into our formula:

1.10

a first term==

1.05

1.10

qratio==

n=number of terms=

∞

PFE Chapter 1, Time value of money page 74

The formula gives:

()

20 1.05

1.10 1.10

1.05

1.10

⎛⎞

−

⎜⎟

−

⎝⎠

−

When n

→

∞

1.05

1.10

⎛⎞

→

⎜⎟

⎝⎠

, so that:

()

20 1.05

1.10 1.10

1.10

400

1.05 1.05

1 0.10 0.05

1.10 1.10

⎛⎞

−

⎜⎟

−

⎝⎠

== == =

−−

Warning: You have to be careful! This version of the formula only works because the

growth rate of 5% is smaller than the discount rate of 10%. The discounted sum of an infinite

series of constantly-growing payments only exists when the growth rate g is less than the

discount rate r.

Here’s a general formula:

()

sumof

*1 *1

constant-growth ...

annuity

CF g

CF g CF g

when g r

undefined otherwise

∞

⎛⎞

−

⎜⎟

⎝⎠

=+ + +=

−

⎧

⎪

−

⎨

⎪

⎩

To summarize:

The present value of a constant-growth annuity—a series of cash flows with first term CF

which grows at rate g—that is discounted at rate r is

−

, provided g < r.

PFE Chapter 1, Time value of money page 75

We use this formula in Chapter 6, when we discuss the valuation of stocks using discounted

dividends (the “Gordon dividend model”).

PFE, Chapter 3: Capital budgeting 1

CHAPTER 3: INTRODUCTION TO CAPITAL BUDGETING

slight bug fix: September 7, 2003

Chapter contents

Overview......................................................................................................................................... 2

3.1. The NPV rule for judging investments and projects............................................................... 3

3.2. The IRR rule for judging investments .................................................................................... 6

3.3. NPV or IRR, which to use?..................................................................................................... 8

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

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

3.6. Capital budgeting principle: Ignore sunk costs and consider only marginal cash flows..... 17

3.7. Capital budgeting principle: Don’t forget the effects of taxes—Sally and Dave’s condo

investment..................................................................................................................................... 18

3.8. Capital budgeting and salvage values................................................................................... 30

3.9. Capital budgeting principle: Don’t forget the cost of foregone opportunities..................... 36

3.10. In-house copying or outsourcing? A mini-case illustrating foregone opportunity costs ... 37

3.11. Accelerated depreciation..................................................................................................... 42

3.12. Conclusion .......................................................................................................................... 43

Notice: This is a preliminary draft of a chapter of Principles of Finance with Excel by Simon

Benninga, which will be published in 2004. Much more material is posted on the PFE website

(http://finance.wharton.upenn.edu/~benninga/pfe.html). Check with the author before

distributing this draft (though you will probably get permission). All the material is copyright

and the rights belong to the author and MIT Press.

PFE, Chapter 3: Capital budgeting 2

Exercises ....................................................................................................................................... 45

Overview

Capital budgeting is finance jargon for the process of deciding whether to undertake an

investment project. There are two standard concepts used in capital budgeting: net present value

(NPV) and internal rate of return (IRR). Both of these concepts were introduced in Chapter 1; in

this chapter we discuss their application to capital budgeting. Here are some of the topics

covered:

• Should you undertake a specific project? We call this the “yes-no” decision, and we

show how both NPV and IRR answer this question.

• Ranking projects: If you have several alternative investments, only one of which you can

choose, which should you undertake?

• Should you use IRR or NPV? Sometimes the IRR and NPV decision criteria give

different answers to the yes-no and the ranking decisions. We discuss why this happens

and which criterion should be used for capital budgeting (if there’s disagreement).

• Sunk costs. How should we account for costs incurred in the past?

• The cost of foregone opportunities

• Salvage values and terminal values

• Incorporating taxes into the valuation decision. This issue is dealt with briefly in Section

3.7. We return to it at greater length in Chapters 4 – 6.

PFE, Chapter 3: Capital budgeting 3

Finance concepts discussed

• IRR

• NPV

• Project ranking using NPV and IRR

• Terminal value

• Taxation and calculation of cash flows

• Cost of foregone opportunities

• Sunk costs

Excel functions used

• NPV

• IRR

• Data table

3.1. The NPV rule for judging investments and projects

In preceding chapters we introduced the basic NPV and IRR concepts and their

application to capital budgeting. We start off this chapter by summarizing each of these rules—

the NPV rule in this section and the IRR rule in the following section.

Here’s a summary of the decision criteria for investments implied by the net present

value:

The NPV rule for deciding whether or not a specific project is worthwhile: Suppose

we are considering a project which has cash flows CF

, CF

, ...., CF

. Suppose

PFE, Chapter 3: Capital budgeting 4

that the appropriate discount rate for this project is r. Then the NPV of the project is

()

() () ()

11 1

CF CFCF CF

NPV CF CF

rr r

=+ + ++ =+

++ +

∑

… .

Rule: A project is worthwhile by the NPV rule if its NPV > 0.

The NPV rule for deciding between two mutually exclusive projects: Suppose you

are trying to decide between two projects A and B, each of which can achieve the same

objective. For example: your company needs a new widget machine, and the choice is

between widget machine A or machine B. You will buy either A or B (or perhaps neither

machine, but you will certainly not buy both machines. In finance jargon these projects

are “mutually exclusive.”

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:

()

CF CF

NPV A CF CF NPV B

=+ >+ =

∑∑

The logic of both the NPV rules presented above is that the present value of a project’s

cash flows—

()

∑

— is the economic value today of the project. Thus—if we have

correctly chosen the discount rate r for the project—the PV is what we ought to be able to sell