Simon Benninga. Financial Modelling 3-rd edition

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

Excel Functions

33.1 Overview

Excel contains several hundred functions. This chapter surveys only

those functions used in the book. The following functions are

discussed:

•

Financial functions: NPV, IRR, PV, PMT, XIRR, XNPV

•

Date functions: Now, Today, Date, Weekday, Month, Datedif

•

Statistical functions: Average, Var, Varp, Stdev, Stdevp, Correl, Covar

•

Regression functions: Slope, Intercept, Rsq, Linest

•

Conditional functions: If, VLoopkup, HLookup

•

Large, Rank, Percentile, Percentrank

•

Count, CountA, CountIf

•

Offset

A separate chapter, Chapter 34, is devoted to the important topic of

array functions.

33.2 Financial Functions

33.2.1 NPV

The Excel deﬁ nition of NPV differs somewhat from the standard ﬁ nance

deﬁ nition. In the ﬁ nance literature, the net present value of a sequence

of cash ﬂ ows C

, C

, . . . , C

at a discount rate r refers to the

expression

() ()11

∑∑

In many cases C

represents the cost of the asset purchased and is there-

fore negative.

The Excel deﬁ nition of NPV always assumes that the ﬁ rst cash ﬂ ow

occurs after one period. The user who wants the standard ﬁ nance ex-

pression must therefore calculate NPV(r,{C

, . . . , C

}) + C

. Here is an

example:

790 Chapter 33

The NPV function has a potential bug: It differentiates between blank

cells and cells containing zeros. This can cause some confusion, as can be

seen in the following example. The present value of the cash ﬂ ows in B4:

B6 is 65.75, which corresponds to

100

115

. But in the otherwise similar

example of the cash ﬂ ows in B11:B13, Excel’s NPV function regards the

ﬁ rst cash ﬂ ow as being 100, and returns the answer

100

115

86 96

. So—in

using NPV you have to be explicit in putting in zeros for zero cash

ﬂ ows.

ABCDEFG

Discount rate 10%

Year 0 1 2 3 4 5

Cash flow -100 35 33 34 25 16

NPV $11.65 <

=NPV(B2,C4:G4)+B4

EXCEL'S NPV FUNCTION

ABC

Discount rate 15%

Year Cash flow

3100

Present value 65.75 <

=NPV(B2,B4:B6)

Year Cash flow

3100

Present value 86.96 <

=NPV(B2,B11:B13)

NPV POTENTIAL PROBLEM

791 Excel Functions

33.2.2 IRR

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

, C

, . . . , C

is an interest rate r such that the net present value of the cash

ﬂ ows is zero:

()1

∑

The Excel syntax for the IRR function is IRR(cash ﬂ ows, guess). Here

cash ﬂ ows represents the whole sequence of cash ﬂ ows, including the ﬁ rst

cash ﬂ ow C

, and guess is a starting point for the algorithm that calculates

the IRR.

First a simple example—consider the cash ﬂ ows given in the NPV

example:

ABCDEFG

Year 012345

Cash flow -100 35 33 34 25 16

IRR 15.00% <

=IRR(B3:G3,0)

15.00% <

=IRR(B3:G3)

EXCEL'S IRR FUNCTION

Note (see cell B6) that guess is not necessary when there is only one

IRR. However, the choice of guess can make a difference when there is

more than one IRR. Consider, for example, the following cash ﬂ ows:

ABCDEFGH

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)

IRR 135.99% <

=IRR(B3:B13,2)

MULTIPLE IRRs

NPV of Cash Flows

(20,000)

(10,000)

10,000

20,000

30,000

108

120

132

144

156

168

180

Discount rate (%)

NPV ($)

792 Chapter 33

The graph (created from a Data|Table that is not shown) shows that

there are two IRRs, since the NPV curve crosses the x-axis twice. To ﬁ nd

both these IRRs, we have to change the guess (though the precise value

of guess is still not critical). In the next example we have changed both

guesses, but still get the same answer:

ABCD

IRR 1.86% <

=IRR(B3:B13,0.1)

IRR 135.99% <

=IRR(B3:B13,0.8)

Note A given set of cash ﬂ ows typically has more than one IRR if there

is more than one change of sign in the cash ﬂ ows. In this example, the

initial cash ﬂ ow is negative, and CF

–CF

are positive (this fact 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 ﬂ ows has more than one

IRR, the ﬁ rst thing to do is to use Excel to make a graph of the NPVs,

as we did. The number of times that the NPV graph crosses the x-axis

identiﬁ es the number of IRRs (and also their approximate values).

33.2.3 PV Function

This function calculates the present value of an annuity (a series of ﬁ xed

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 46

100

110

∑

. Here are two things to note about the PV

function:

793 Excel Functions

•

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 the following:

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)

The formula PV(B9,B10,B11,,1) can also be generated from the dialogue

box:

•

Irritatingly, the PV function (and the PMT, IPMT, and PPMT func-

tions—see next two subsections) produces a negative number (there

is a logic here, but it’s not worth explaining). The solution is obvious:

Either write -PV(B3,B4,B5) or let the payment be negative by writing

PV(B3,B4,-B5).

794 Chapter 33

33.2.4 PMT

The PMT function calculates the payment necessary to pay off a loan

with equal payments over a ﬁ xed number of periods. For example, the

ﬁ rst calculation in the following spreadsheet shows that a loan of $1,000

to be paid off over 10 years at an interest rate of 8 percent will require

equal annual payments of interest and principal of $149.03. The calcula-

tion performed is the solution of the following equation:

()1

∑

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 the split between interest and

principal of each payment. In each period, the payment on the loan

(calculated with PMT) is split:

•

We ﬁ rst calculate the interest owing for that period on the principal

outstanding at the beginning of the period. In the following table, at the

end of year 1 we owe $80 (= 8 percent

$1,000) 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.

795 Excel Functions

Note that at the end of the l0 years the repayment of principal is exactly

equal to the principal outstanding at the beginning of the year (i.e., the

loan has been paid off).

33.2.5 The Functions IPMT and PPMT

As we have seen, a loan table shows the split of a loan’s ﬂ at payments

(computed with PMT) between interest and principal. In the loan table

of the previous subsection, we computed this split by ﬁ rst computing the

ﬂ at payment per period (column C), then taking the interest on the

principal at the beginning of the period (column D) and ﬁ nally subtract-

ing this interest from the period’s total payment (column E).

IPMT and PPMT perform this calculation without the necessity of

relying on the total payment. Here’s an example:

ABCDE

Interest 8%

Number of periods 10

Principal 1,000

Annual payment 149.03 <

=-PMT(B2,B3,B4)

Split of payment between

Year

Principal at

beginning of year

Payment Interest

Repayment

of principal

1 1,000.00 149.03 80.00 69.03 <

=C9-D9

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

PMT(B2,B3,B4)

=$B$2*B9

=B9-E9

796 Chapter 33

As you can see, the payments computed are the same as in the loan

table of the previous subsection.

33.3 Dates and Date Functions

Read the quote from the Excel help that follows and you will know almost

everything you need to know about entering dates into your spreadsheet:

EDCBA

Interest 8%

Number of periods 10

Principal 1,000

Year

Principal

payment

at end year

Interest

payment

at end

year

1$69.03<

=PPMT($B$2,A7,$B$3,-$B$4) $80.00 <

=IPMT($B$2,A7,$B$3,-$B$4)

2$74.55<

=PPMT($B$2,A8,$B$3,-$B$4) $74.48 <

=IPMT($B$2,A8,$B$3,-$B$4)

3 $80.52 $68.51

4$86.96 $62.07

5$93.91 $55.12

6 $101.43 $47.60

7 $109.54 $39.49

8 $118.30 $30.73

9 $127.77 $21.26

10 $137.99 $11.04

IPMT AND PPMT

797 Excel Functions

The basic fact you need to know is that Excel translates dates into a

number: Here’s an example: Suppose you decide to type a date into a

cell:

When you hit Enter, Excel decides that you’ve entered a date. Here’s

the way it appears:

Note that in the formula bar (indicated by the arrow above), Excel inter-

prets the date entered as 2/3/2001.

When you reformat the cell as For-

mat|Cells|Number|General, you see that Excel interprets this date as the

number 36925, the number 1 being January 1, 1900.

36925

Spreadsheet dates can be subtracted: In the next spreadsheet we’ve

entered two dates and subtracted them to ﬁ nd the number of days

between the dates:

1. The way these dates appear and are interpreted depends on the Regional Settings

entered in the Windows Control Panel. Our settings in this book follow the U.S.

conventions.