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

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

PFE Chapter 1, Time value of money page 20

ABCDEFGH

X, future payment 100

n, time of future payment 3

r, interest rate 6%

Present value, X/(1+r)

83.96 <-- =B2/(1+B4)^B3

Discount rate

Present

value

0% 100.00 <-- =100/(1+A8)^3

1% 97.06 <-- =100/(1+A9)^3

2% 94.23 <-- =100/(1+A10)^3

3% 91.51 <-- =100/(1+A11)^3

4% 88.90 <-- =100/(1+A12)^3

5% 86.38

6% 83.96

7% 81.63

8% 79.38

9% 77.22

12% 71.18

15% 65.75

18% 60.86

20% 57.87

22% 55.07

25% 51.20

30% 45.52

35% 40.64

40% 36.44

45% 32.80

50% 29.63

THE PRESENT VALUE OF $100 IN 3 YEARS

in this example we vary the discount rate

Present Value of $100 to be Paid in 3 Years when

Discount Rate Varies

100

120

0% 10% 20% 30% 40% 50%

Discount rate

Present value

Why does PV decrease as the discount rate increases?

The Excel table above shows that the $100 Uncle Simon promises you in 3 years is worth

$83.96 today if the discount rate is 6% but worth only $40.64 if the discount rate is 35%. The

mechanical reason for this is that taking the present value at 6% means dividing by a smaller

denominator than taking the present value at 35%:

()()

100 100

83.96 40.64

1.06 1.35

=>=

The economic reason relates to future values: If the bank is paying you 6% interest on

your savings account, you would have to deposit $83.96 today in order to have $100 in 3 years.

If the bank pays 35% interest, then

()

$40.64* 1.35 $100=

PFE Chapter 1, Time value of money page 21

What this short discussion shows is that the present value is the inverse of the future

value

Time0123

$100.00

PV =

$100.00/(1+6%)

$83.96

FV=

$83.96*(1+6%)

= $100.00

Present value of an annuity

In the jargon of finance, an annuity is a series of equal periodic payments. Examples of

annuities are widespread:

•

The allowance your parents give you ($1000 per month, for your next 4 years of

college) is a monthly annuity with 48 payments.

•

Pension plans often give the retiree a fixed annual payment for as long as he lives.

This is a bit more complicated annuity, since the number of payments is uncertain.

•

Certain kinds of loans are paid off in fixed periodic (usually monthly, sometimes

annual) installments. Mortgages and student loans are two examples.

The

present value of an annuity tells you the value today of all the future payments on the

annuity. Here’s an example that relates to your generous Uncle Simon. Suppose he has

promised you $100 at the end of each of the next 5 years. Assuming that you can get 6% at the

bank, this promise is worth $421.24 today:

PFE Chapter 1, Time value of money page 22

ABCD

CALCULATING PRESENT VALUES WITH EXCEL

Annual payment 100

r, interest rate 6%

Year

Payment

at end of

year

Present

value of

payment

1 100 94.34 <-- =B6/(1+$B$3)^A6

2 100 89.00 <-- =B7/(1+$B$3)^A7

3 100 83.96

4 100 79.21

5 100 74.73

Present value of all payments

Summing the present values 421.24 <-- =SUM(C6:C10)

Using Excel's PV function 421.24 <-- =PV(B3,5,-100)

Using Excel's NPV function 421.24 <-- =NPV(B3,B6:B10)

The example above shows three ways of getting the present value of $421.24:

•

You can sum the individual discounted values. This is done in cell C13.

•

You can use Excel’s PV function, which calculates the present value of an annuity (cell

C14).

•

You can use Excel’s NPV function (cell C16). This function calculates the present value

of any series of periodic payments (whether they’re flat payments, as in an annuity, or

non-equal payments).

We devote separate subsections to the

PV function and to the NPV function.

The Excel PV function

The PV function calculates the present value of an annuity (a series of equal payments).

It looks a lot like the

FV discussed above, and like FV, it also suffers from the peculiarity that

positive payments give negative results (which is why we set

Pmt equal to –100). As in the case

of the

FV function, Type denotes whether the payments are made at the beginning or the end of

the year. Because end-year is the default, can either enter “0” or leave the

Type entry blank:

PFE Chapter 1, Time value of money page 23

Dialog box for the PV function

The “Formula result” in the dialogue box shows that the answer is $421.24.

The Excel NPV function

The NPV function computes the present value of a series of payments. The payments

need not be equal, though in the present example they are. The ability of the

NPV function to

handle non-equal payments makes it one of the most useful of all Excel’s financial functions.

We will make extensive use of this function throughout this book.

PFE Chapter 1, Time value of money page 24

Dialog box for the NPV function

Important note: Finance professionals use “NPV” to mean “net present value,” a

concept we explain in the next section. Excel’s

NPV function actually calculates the present

value

of a series of payments. Almost all finance professionals and textbooks would call the

number computed by the Excel

NPV function “PV.” Thus the Excel use of “NPV” differs from

the standard usage in finance.

Choosing a discount rate

We’ve defined the present value of $X to be received in n years as

()

. The interest

rate

r in the denominator of this expression is also known as the discount rate. Why is 6% an

appropriate discount rate for the money promised you by Uncle Simon? The basic principle is to

choose a discount rate that is appropriate to the

riskiness and the duration of the cash flows being

discounted. Uncle Simon’s promise of $100 per year for 5 years is assumed as good as the

PFE Chapter 1, Time value of money page 25

promise of your local bank, which pays 6% on its savings accounts. Therefore 6% is an

appropriate discount rate.

The present value of non-annuity (meaning: non-constant) cash flows

The present value concept can also be applied to non-annuity cash flow streams, meaning

cash flows that are not the same every period. Suppose, for example, that your Aunt Terry has

promised to pay you $100 at the end of year 1, $200 at the end of year 2, $300 at the end of year

3, $400 at the end of year 4 and $500 at the end of year 5. This is not an annuity, and so it

cannot be accommodated by the

PV function. But we can find the present value of this promise

by using the

NPV function:

ABCD

CALCULATING PRESENT VALUES WITH EXCEL

r, interest rate 6%

Year

Payment

at end of

year

Present

value

1 100 94.34 <-- =B5/(1+$B$2)^A5

2 200 178.00 <-- =B6/(1+$B$2)^A6

3 300 251.89

4 400 316.84

5 500 373.63

Present value of all payments

Summing the present values 1,214.69 <-- =SUM(C5:C9)

Using Excel's NPV function 1,214.69 <-- =NPV($B$2,B5:B9)

There’s more to be said on the choice of a discount rate, but we postpone the discussion until Chapters 5 and 6.

PFE Chapter 1, Time value of money page 26

Excel note

Excel’s NPV function allows you to input up to 29 payments directly in the function

dialogue box. Here’s an illustration for the example above:

1.3. Net present value

In this section we discuss net present value.

The net present value (NPV) of a series of future cash flows is their present value minus

the initial investment required to obtain the future cash flows. The NPV = PV(future

cash flows) – initial investment. The NPV of an investment represents the increase in

wealth which you get if you make the investment.

Here’s an example based on the spreadsheet on page000. Would you pay $1500 today to

get the series of future cash flows in cells B6:B10? Certainly not—they’re worth only $1214.69,

so why pay $1500? If asked to pay $1500, the NPV of the investment would be

PFE Chapter 1, Time value of money page 27

Net present value

Cost of the Present value of

investment investment's future

cash flows at discount

rate of 6%

$1,500 $1,214.69 $285.31NPV

↑

↑↑

=− + = −

 

  

If you paid $1,500 for this investment, you would be overpaying $285.31 for the investment, and

you would be poorer by the same amount. That’s a bad deal!

On the other hand, if you were offered the same future cash flows for $1,000, you’d snap

up the offer, you would be paying $214.69 less for the investment than its worth:

Net present value

Cost of the Present value of

investment investment's future

cash flows at discount

rate of 6%

$1,000 $1,214.69 $214.69NPV

↑

↑↑

=− + =

 

  

In this case the investment would make you $214.69 richer. As we said before, the NPV of an

investment represents the increase in your wealth if you make the investment.

To summarize:

The net present value (NPV) of a series of cash flows is used to make investment

decisions: An investment with a positive NPV is a good investment and an investment

with a negative NPV is a bad investment. You should be indifferent to making in a zero-

NPV investment. An investment with a zero NPV is a “fair game”—the future cash flows

of the investment exactly compensate you for the investment’s initial cost.

Net present value (NPV) is a basic tool of financial analysis. It is used to determine

whether a particular investment ought to be undertaken; in cases where we can make only one of

several investments, it is the tool-of-choice to determine which investment to undertake.

Here’s another example: You’ve found an interesting investment—If you pay $800

today to your local pawnshop, the owner promises to pay you $100 at the end of year 1, $150 at

the end of year 2, $200 at the end of year 3, ... , $300 at the end of year 5. In your eyes, the

PFE Chapter 1, Time value of money page 28

pawnshop owner is as reliable as your local bank, which is currently paying 5% interest. The

following spreadsheet shows the NPV of this $800 investment:

ABC D

CALCULATING NET PRESENT VALUE (NPV) WITH EXCEL

r, interest rate 5%

Year

Payment

Present

value

-800

-800.00

1 100 95.24 <-- =B6/(1+$B$2)^A6

2 150 136.05 <-- =B7/(1+$B$2)^A7

3 200 172.77

4 250 205.68

5 300 235.06

Summing the present values 44.79 <-- =SUM(C5:C10)

Using Excel's NPV function 44.79 <-- =NPV($B$2,B6:B10)+C5

The spreadsheet shows that the value of the investment—the

net present value(NPV) of

its payments, including the initial payment of -$800—is $44.79:

()

()()()()

2345

The of the future payments:

Calculated with Excel NPV function = 844.79

100 150 200 250 300

800 44.97

1.05

1.05 1.05 1.05 1.05

present value

NPV =− + + + + + =



At a 5% discount rate, you should make the investment, since its NPV is $44.79, which is

positive.

PFE Chapter 1, Time value of money page 29

An Excel Note

As mentioned earlier, the Excel

NPV function’s name does not correspond to the

standard finance use of the term “net present value.”

In finance, “present value” usually refers to

the value today of future payments (in the example, this is

()

()()()()

2345

100 150 200 250 300

844.79

1.05

1.05 1.05 1.05 1.05

++++=

). Finance professionals use net present

value (NPV) to mean the present value of future payments minus the cost of the initial payment;

in the previous example this is $844.79 - $800 = $44.79. In this book we use the term “net

present value” (NPV) to mean its true finance sense. The Excel function

NPV will always

appear in boldface. We trust that you will rarely be confused

NPV depends on the discount rate

Let’s revisit the pawnshop example on page000, and use Excel to create a table which

shows the relation between the discount rate and the NPV. As the graph below shows, the higher

the discount rate, the lower the net present value of the investment:

There’s a long history to this confusion, and it doesn’t start with Microsoft. The original spreadsheet—Visicalc—

(mistakenly) used “NPV” in the sense which Excel still uses today; this misnomer has been copied every since by all

other spreadsheets: Lotus, Quattro, and Excel.