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

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

PFE Chapter 29, Functions in Excel page 3

A word about nomenclature: In order to differentiate an Excel function from the

surrounding text, we usually (though not in the table above!) denote it with boldface. Most

Excel functions depend on some variable, but we do not always indicate these variables. For

example the variables for the NPV function are the interest rate and the range to be discounted;

when we want to make this explicit, we write NPV(interest,range) .

One more note: The functions in each class are not always discussed alphabetically.

Where there’s a logical order, we use this (for example: We discuss NPV before IRR .).

29.1. Financial functions

NPV( )

This function is extensively discussed in Chapter 1. The Excel definition of NPV( )

differs somewhat from the standard finance definition. In the finance literature, the net present

value of a sequence of cash flows C

, C

, ..., C

at a discount rate r refers to the expression:

() ()

or C

∑∑

In many cases C

represents the cost of the asset purchased and is therefore negative.

The Excel definition of

NPV( ) always assumes that the first cash flow occurs after one

period. The user who wants the standard finance expression must therefore calculate

NPV(r,{C

, ... , C

}) + C

. Here is an example:

PFE Chapter 29, Functions in Excel page 4

ABCDEFG

Discount rate 10%

Year 012345

Cash flow -100 35 33 34 25 16

NPV $11.65 <-- =NPV(B2,C4:G4)+B4

EXCEL'S NPV FUNCTION

IRR( )

The internal rate of return (IRR) of a sequence of cash flows C

, C

, ..., C

is an

interest rate r such that the net present value of the cash flows is zero:

()

∑

The Excel syntax for the

IRR( ) function is IRR(cash flows, guess). Here cash flows represents

the whole sequence of cash flows, including the first cash flow C

, and guess is a starting point

for the algorithm that calculates the IRR.

First a simple example—consider the cash flows given above:

ABCDEFG

Year 012345

Cash flow -100 35 33 34 25 16

IRR 15.00% <-- =IRR(B10:G10,0)

15.00% <-- =IRR(B10:G10)

EXCEL'S IRR FUNCTION

Note that

guess is not necessary when there is only one IRR. Thus in cell B13 (where we

haven’t indicated a

guess) we get the same answer as in cell B12 (guess = 0 ).

The choice of

guess can, however, make a difference when there is more than one IRR.

Consider, for example, the following cash flows:

PFE Chapter 29, Functions in Excel page 5

AB C DEFG

Year

Cash

flow

0 -11,000

1 15,000

2 15,000

3 15,000

4 15,000

5 15,000

6 15,000

7 15,000

8 15,000

9 15,000

10 -135,000

IRR 1.86% <-- =IRR(B3:B13,0), guess = 0

IRR 135.99% <-- =IRR(B3:B13,2), guess = 2

MULTIPLE IRRs

NPV of Cash Flows

(15,000)

(10,000)

(5,000)

5,000

10,000

15,000

20,000

25,000

30,000

0% 24% 48% 72% 96% 120% 144% 168%

Discount rate

NPV ($)

The graph (created from table that is not shown) shows that there are two IRRs, since the NPV

curve crosses the x-axis twice. To find both these IRRs, we have to change the

guess (though

the precise value of guess is still not critical). In the example below we have changed both

guesses, but still get the same answer:

AB C

IRR 1.86% <-- =IRR(B3:B13,0.1)

IRR 135.99% <-- =IRR(B3:B13,0.8)

Note: A given set of cash flows typically has more than one IRR if there is more than

one change of sign in the cash flows—in the above example, the initial cash flow is negative,

and CF

– CF

are positive (this accounts for one change of sign); but then CF

is negative—

making a second change of sign. If you suspect that a set of cash flows has more than one IRR,

the first thing to do is to use Excel to make a graph of the NPVs, as we did above. The number

of times that the NPV graph crosses the x-axis identifies the number of IRRs (and also their

approximate values).

For more examples of multiple IRRs, see Chapter 5.

PFE Chapter 29, Functions in Excel page 6

PV( )

This function calculates the present value of an annuity (a series of fixed periodic

payments). For example:

AB C

Payments made at the end of the period

Rate 10%

Number of periods 10

Payment 100

Present value (614.46) <-- =PV(B3,B4,B5)

THE PV FUNCTION

Thus

()

$614.

100

110

∑

. Here are two things to note about the PV( ) function:

• Writing PV(B3,B4,B5) assumes that payments are made at dates 1, 2, ..., 10. If the

payments are made at dates 0, 1, 2, ..., 9, you should write:

AB C

Payments made at the beginning of the period

Rate 10%

Number of periods 10

Payment 100

Present value (675.90) <-- =PV(B9,B10,B11,,1)

• Irritatingly, when the payments are positive as in the above example, the PV( )

function (and the

PMT( ) function—see below) gives the present value as a negative

number (there is a logic here, but it’s not worth explaining). To get a positive present

value in cell B12, we would either write

-PV(B3,B4,B5) or let the payment be

negative by writing

PV(B3,B4,-B5).

PFE Chapter 29, Functions in Excel page 7

PMT( )

This function calculates the payment necessary to pay off a loan with equal payments

over a fixed number of periods. For example, the first calculation below shows that a loan of

$1000, to be paid off over 10 years at an interest rate of 8% will require equal annual payments

of interest and principal of $149.03. The calculation performed is the solution of the following

equation:

()

Where is the payment

initial loan principal

∑

AB C

Payments made at the end of the period

Rate 8%

Number of periods 10

Principal 1000

Payment ($149.03) <-- =PMT(B3,B4,B5)

Payments made at the beginning of the period

Rate 8%

Number of periods 10

Principal 1000

Payment ($137.99) <-- =PMT(B9,B10,B11,,1)

THE PMT FUNCTION

Loan tables can be calculated using the

PMT( ) function. These tables—explained in

detail in Chapter 1—show what part of each payment is interest and what part is repayment of

the loan principal. In each period, the payment on the loan (calculated with

PMT( ) ) is split:

•

We first calculate the interest owing for that period on the principal outstanding at the

beginning of the period. In the table below, at the end of year 1, we owe $80 (= 8% *

$1000) of interest on the loan principal outstanding at the beginning of the year.

•

The remainder of the payment (for year 1: $69.03) goes to reduce the principal

outstanding.

PFE Chapter 29, Functions in Excel page 8

ABCDE

Interest 8%

Number of periods 10

Principal 1,000.00

Annual payment 149.03 <-- =-PMT(B2,B3,B4)

Split of payment into

Year

Principal at

beginning of year

Payment

at end

of year

Interest

Repayment of

principal

1 1,000.00 149.03 80.00 69.03

2 930.97 149.03 74.48 74.55

3 856.42 149.03 68.51 80.52

4 775.90 149.03 62.07 86.96

5 688.95 149.03 55.12 93.91

6 595.03 149.03 47.60 101.43

7 493.60 149.03 39.49 109.54

8 384.06 149.03 30.73 118.30

9 265.76 149.03 21.26 127.77

10 137.99 149.03 11.04 137.99

Loan Table

Note that the repayment of principal at the end of year 10 is exactly equal to the principal

outstanding at the beginning of the year (i.e., the loan has been paid off).

RATE( )

RATE calculates the internal rate of return of a series of constant payments. In the

example below

RATE(B4,B5,-B3) in cell B6 computes 10.56%, which is the internal rate of

return:

()

() ()

210

100 100 100

600 0

1.1056

1.1056 1.1056

−+ + ++ =…

PFE Chapter 29, Functions in Excel page 9

AB C D

RATE used for payments made at the end of the period

Initial payment 600

Number of periods 10

Annual payment 100

Rate of return 10.56% <-- =RATE(B4,B5,-B3)

RATE used for payments made at the beginning of the period

Initial payment 600

Number of periods 10

Annual payment 100

Rate of return 13.70% <-- =RATE(B10,B11,-B9,,1,20%)

What does RATE do? Computing the IRR

Year

Payment

at end of

period

Payment

at beginning

of period

0 -600 -500

1100100

2100100

3100100

4100100

5100100

6100100

7100100

8100100

9100100

10 100

IRR 10.56% 13.70% <-- =IRR(C16:C26)

THE RATE FUNCTION

should be compared to IRR

PV and PMT, RATE gives the possibility of specifying whether the cash flows

occur at the end of the period (the default) or its beginning. If you look in cell B12,

RATE(B10,B11,-B9,,1,20%) computes 13.70%; this is the internal rate of return of an initial

payment of $600 and 10 payments of $100

made at the beginning of the period (the beginning of

the period is indicated by the “1” at the end of the formula. The

20% in the function is a Guess

like that which is also allowed in the IRR function.

Here’s the dialog box which created this result:

PFE Chapter 29, Functions in Excel page 10

Think for a second what this means for an internal rate of return:



()

()() ()

23 9

First payment

made at "beginning"

of period--meaning,

made at time 0

100 100 100 100

600 100 0

1.1370

1.1370 1.1370 1.1370

↑

−+ + + + ++ =…

Effectively, then

RATE(B10,B11,-B9,,1,20%) refers to an initial payment of $500 and 9

subsequent payments of 100.

RATE versus IRR

If you look at the above example, you will see (rows 16-28) that IRR and RATE give the

same values. There are, of course, tradeoffs:

•

RATE is shorter; IRR requires you to specify all the cash flows.

• On the other hand, IRR can handle cash flows which vary over time.

PFE Chapter 29, Functions in Excel page 11

NPER( )

This function calculates the number of periods to repay a loan given a fixed amount.

Example: You borrow $1,000 from the bank, which charges you a 10% annual interest rate.

You intend to repay the loan with $250 per year. How long will it take you to repay the loan?

ABCDE

Loan amount 1,000.00

Interest rate 10%

Annual payment 250

How long to pay off the l

5.3596 <-- =NPER(B3,B4,-B2)

Year

Principal

at beginning

of year

Payment

at end

of year

Interest

Repayment

of principal

1 1,000.00 250.00 100.00 150.00

2 850.00 250.00 85.00 165.00

3 685.00 250.00 68.50 181.50

4 503.50 250.00 50.35 199.65

5 303.85 250.00 30.39 219.62

6 84.24 250.00 8.42 241.58

HOW LONG TO PAY OFF THIS LOAN?

As you can see from the loan table, it takes somewhere between 5 and 6 years to repay

the loan.

NPER(B3,B4,-B2) gives the exact number of periods as 5.3596.

29.2. Math functions

Using Exp to calculate future values

Suppose you invest $100 at 10% for 3 years. As explained in Chapter 2, if interest is

compounded annually, the future value after 3 years will be

Why? At the end of year 5 (which is also the beginning of year 6), there’s still $84.24 of principal outstanding.

But if you pay back $250 at the end of year 6, then you’ve paid back too much.

PFE Chapter 29, Functions in Excel page 12

ABC

Initial investment 100

Years invested, t 3

Interest rate, r 10%

Future value, FV 133.1 <-- =B2*(1+B4)^B3

ANNUAL COMPOUNDING

Suppose the 10% is compounded semi-annually (meaning: you get 5% each half year). Then

there will be 6 compounding periods—3 years * 2 periods/year. Your future value will be

()

* 1 5% 134.0096InitialInvestment += :

ABC

Initial investment 100

Years invested, t 3

Compounding periods per year, n 2

Interest rate, r 10%

Future value, FV 134.0096 <-- =B7*(1+B10/B9)^(B8*B9)

Denote the number of years by t, the interest rate by r, and the number of compounding

periods per year by n. As the number of compounding periods increases, the future value tends

towards 100*e

r*t

, where e is the number 2.71828.

In Excel this is written as 100*Exp(r*t) .

This is illustrated in the table and graph below:

In mathematical notation:

lim 1

→∞





