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

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

PFE Chapter 29, Functions in Excel page 23

The first column of VLookup must be sorted

The first column of the

VLookup table must be sorted, meaning it must be in increasing

order (either numerical or alphabetical). To see what this means, we have a slightly complicated

example: The data in columns A and B below were imported from a data base; column A gives

the date and column B gives an interest rate on a particular date.

ABCDEFG

Date

Interest

rate

Month Day Year

JAN. 07,1991 6.721 JAN 07 1991

FEB. 07,1991 6.145 Feb 07 1991

FEB. 11,1991 6.03 FEB 11 1991

MAR. 04,1991 6.287 MAR 04 1991

APR. 01,1991 5.985 APR 01 1991

JUN. 08,1991 5.777 JUN 08 1991

AUG. 15,1991 5.744 AUG 15 1991

OCT. 22,1991 5.868 OCT 22 1991

FIRST COLUMN OF VLOOKUP MUST BE SORTED

=LEFT(A10,3)

=MID(A10,6,2)

=RIGHT(A10,4)

We would like to give each date a standard Excel value. That is, instead of “Jan. 07,

1991,” we’d like to write

Standard Excel date

format

Number

equivalent

7-Jan-91 33245

If this is somewhat unclear, refer to Chapter ???.

In order to write the date in the standard Excel format, we use the functions

Left, Mid,

and

Right to parse the dates in column A into month, day and year (see next section). We now

need to identify each month with its number (i.e., Jan = 1, Feb = 2, etc.). We can use

VLookup

to do this, but only if the VLookup table has its left column in alphabetical order.

PFE Chapter 29, Functions in Excel page 24

ABCDEFGH I JK

Date

Interest

rate

Month Day Year

Which

month?

Date

value

JAN. 07,1991 6.721 JAN 07 1991 1 <-- =VLOOKUP(D3,$J$6:$K$17,2) 1/7/1991 <-- =DATE(F3,H3,E3)

FEB. 07,1991 6.145 Feb 07 1991 2

FEB. 11,1991 6.03 FEB 11 1991 2

Lookup table

MAR. 04,1991 6.287 MAR 04 1991 3 Apr 4

APR. 01,1991 5.985 APR 01 1991 4 Aug 8

JUN. 08,1991 5.777 JUN 08 1991 6 Dec 12

AUG. 15,1991 5.744 AUG 15 1991 8 Feb 2

OCT. 22,1991 5.868 OCT 22 1991 10 Jan 1

Jul 7

Jun 6

Mar 3

May 5

Nov 11

Oct 10

Sep 9

FIRST COLUMN OF VLOOKUP MUST BE SORTED

=LEFT(A10,3)

=MID(A10,6,2)

=RIGHT(A10,4)

This gives a rather strange-looking table (cells J6:K17), but you can convince yourself

that this works.

29.4. Text functions

Excel distinguishes between numbers and text. To sound stupid: You can add, subtract,

etc. numbers, but you can’t do this for text. On the other hand, Excel allows you to concatenate

text (if this sounds mysterious, read on).

Concatenation: combining text from several cells

Here’s an example. In the example below, we’ve written “twelve” in cell A2 and “cows”

in cell B2. In cell A4, we tried to write

=A3+B3; we intended this to come out “Twelvecows”,

but Excel won’t accept this, because neither the contents of A2 (“Twelve”) nor those of B2

(“cows”) is a number. We can combine the text as in cell A5, by writing

=A3&B3.

Twelve cows

#VALUE! <-- =A2+B2

Twelvecows <-- =A2&B2

Twelve blue cows <-- =A2&" blue "&B2

PFE Chapter 29, Functions in Excel page 25

In cell A7, we’ve added the word “blue” plus some spaces, putting the additional

text/spaces inside quotation marks.

TEXT

Now look at the example below:

AB C

Number of cows 1200

Text 1200 cows <-- =TEXT(B10,"0")&" cows"

1200.00 cows <-- =TEXT(B10,"0.00")&" cows"

1,200.0 cows <-- =TEXT(B10,"0,000.0")&" cows"

120,000.00% cows <-- =TEXT(B10,"0,000.00%")&" cows"

In cell B12 we want to create a text that contains the number of cows (cell B10) and the

word “ cows”. The Excel function

Text(B10,“0”) turns the number 1200 into a text form which

can then be used in the formula in cell B12. The second part of the

Text function—where we’ve

currently written “0”—is used to indicate the appearance of the text. Cells B14:B16 give some

other examples.

LEFT, RIGHT, MID, LEN

The first three functions allow you to pick out parts of texts. In the example below,

we’ve used these functions to pick out parts of the text in cell A18:

15 pink flamingos went to the zoo

15 <-- =LEFT(A18,2)

pink flamingos <-- =MID(A18,4,14)

zoo <-- =RIGHT(A18,3)

33 <-- =LEN(A18)

PFE Chapter 29, Functions in Excel page 26

The function =Left(A19,2) picks out the 2 left-most characters of cell A19. The function

=Mid(A19,4,14) picks out the 14 characters of cell A19, starting with the 4

character. And the

function

=Right(A19,3), well ... you’ll figure that one out yourself.

As illustrated in cell A23, the function

Len tells you the number of characters in the text.

You might ask why a finance book needs to consider these functions. Here’s an example

that arose in the writing of this book: In Chapter ??? we discuss the prices of options on General

Motors stock. This data was originally downloaded from the website of the Chicago Board of

Options Exchange (CBOE). When we downloaded the data, here’s what it looked like:

ABCD

Calls Last Sale Puts Last Sale

01 Aug 60.00 (GM HL-E) 3.5 01 Aug 60.00 (GM TL-E) 0.5

01 Aug 60.00 (GM HL-A) 3.4 01 Aug 60.00 (GM TL-A) 0.4

01 Aug 60.00 (GM HL-P) 3 01 Aug 60.00 (GM TL-P) 0.4

01 Aug 60.00 (GM HL-X) 2.9 01 Aug 60.00 (GM TL-X) 0.6

01 Aug 60.00 (GM HL-8) 3.4 01 Aug 60.00 (GM TL-8) 0.5

01 Aug 65.00 (GM HM-E) 0.45 01 Aug 65.00 (GM TM-E) 2.85

01 Aug 65.00 (GM HM-A) 0.45 01 Aug 65.00 (GM TM-A) 1.8

01 Aug 65.00 (GM HM-P) 0.45 01 Aug 65.00 (GM TM-P) 2.4

01 Aug 65.00 (GM HM-X) 1.15 01 Aug 65.00 (GM TM-X) 2.25

01 Aug 65.00 (GM HM-8) 0.4 01 Aug 65.00 (GM TM-8) 2.7

01 Aug 70.00 (GM HN-E) 0.05 01 Aug 70.00 (GM TN-E) 7.9

01 Aug 70.00 (GM HN-A) 0.05 01 Aug 70.00 (GM TN-A) 6.3

01 Aug 70.00 (GM HN-P) 0.05 01 Aug 70.00 (GM TN-P) 0

01 Aug 70.00 (GM HN-X) 0.2 01 Aug 70.00 (GM TN-X) 7.5

01 Aug 70.00 (GM HN-8) 0.05 01 Aug 70.00 (GM TN-8) 6.8

Other information

Option expiration year and month

Option exercise price

GENERAL MOTORS OPTION DATA

Downloaded from Chicago Board of Options Exchange

Web Site

The information in columns A and C gives information about the option, including the

expiration year and month, the exercise price, and a parenthetical item that shows you the stock

PFE Chapter 29, Functions in Excel page 27

on which the option is written, the option symbol, and the exchange on which the option traded.

For example:

GM HN-E a General Motors call option with exercise price 70 expiring in August

2001 and trading on the Chicago Board of Options Exchange

GM TL-A is the stock symbol for a General Motors put option with exercise price

60, expiring in August 2001 and trading on the American Stock Exchange

Now suppose we want to separate the dates, the option’s symbol, and the exchange on

which the option traded:

CDEFGHIJK

Puts Last Sale Date Symbol Exchange

01 Aug 60.00 (GM TL-E) 0.5 01Aug TL E

01 Aug 60.00 (GM TL-A) 0.4

01 Aug 60.00 (GM TL-P) 0.4

01 Aug 60.00 (GM TL-X) 0.6

01 Aug 60.00 (GM TL-8) 0.5

01 Aug 65.00 (GM TM-E) 2.85

01 Aug 65.00 (GM TM-A) 1.8

01 Aug 65.00 (GM TM-P) 2.4

01 Aug 65.00 (GM TM-X) 2.25

01 Aug 65.00 (GM TM-8) 2.7

01 Aug 70.00 (GM TN-E) 7.9

=LEFT(C3,2)&MID(C3,4,3)

=MID(C3,LEN(C3)-4,2)

=MID(C3,LEN(C3)-1,1)

In Chapter ??? (which explains how to use times and dates in Excel), we use this

information to design a function that gives us the option’s expiration date.

29.5. Statistical functions

Many of Excel’s statistical functions have already been discussed in previous chapters:

PFE Chapter 29, Functions in Excel page 28

Average

Finds the average of a range of

cells

Chapter 11

Covar

The covariance of two sets of

data

Chapter 11

Correl

The correlation coefficient of

two sets of data

Chapter 11

Frequency

An array function that

computes the frequency

distribution.

Chapter 10

Intercept, Slope, Rsq

Compute the intercept, slope

and R

of a regression

Chapters 11 and 13

Max, Min

The maximum and minimum

of a set of numbers

Chapter 10, option chapters

Stdev, StdevP

The standard deviation Chapters 10 and 11

Var, VarP

The variance Chapters 10 and 11

Median, Large, and Rank

In this section we discuss a three more statistical functions: Median, Large, and Rank.

We illustrate the following example, which gives the grades for 11 students:

AB C

Student Grade

1100

250

375

432

598

686

772

863

941

10 88

11 92

Average 72.45 <-- =AVERAGE(B3:B13)

Median 75 <-- =MEDIAN(B3:B13)

Large 92 <-- =LARGE(B3:B13,3)

Rank 7 <-- =RANK(B9,B3:B13)

Median

Large

Rank

The median is the grade which splits the list in 2: There are 5 grades higher than 75 and

5 lower. The median is different from the average, as you can see.

PFE Chapter 29, Functions in Excel page 29

The Excel function Large tells you the k

largest number in the set of grades:

The Excel function

Rank tells you where a particular number places in the range of

grades. In the example given the grade 72 is the 7

among the set of grades in B3:B13:

Count, CountIf, CountA

All three of these functions count cells. The difference (we’ll illustrate) is that:

PFE Chapter 29, Functions in Excel page 30

• Count counts the number of cells which contain values and ignores the cells that

contain text.

•

CountA counts all non-blank cells in a range, whether they contain values or text.

•

CountIf counts cells which fulfill a particular condition.

Now we’ll illustrate:

AB C

COUNT, COUNTIF, COUNTA

List

Terry

Oliver

Noah

Sara

Zvi

Count 4 <-- =COUNT(A3:A11)

CountA 9 <-- =COUNTA(A3:A11)

CountIf 2 <-- =COUNTIF(A3:A11,">2")

Summary

Excel has hundreds of functions. This chapter has illustrated the major functions used in

this book (and then some). We rely on you, as an educated reader, to figure the rest out for

yourself.

PFE Chapter 29, Functions in Excel page 31

Exercises

IRR: Use the IRR function to compute the internal rate of return of the project whose cash flows

are given below:

Year Cash flow

0-500

1100

2150

3200

4150

5100

IRR: Use

Goal Seek or Solver (see Chapter 32) to determine the IRR of the project whose cash

flows are given below. (Recall that IRR is the discount rate for which the NPV equals zero.)

AB C

Discount rate 15%

Year Cash flow

0-1,000

1200

2400

3500

4400

5200

NPV 133.27 <-- =B4+NPV(B1,B5:B9)

Datevalue as in text.

In the exercise with the dates and the rates and VLookup, some of the dates were not 4 spaces

(like the date in A11). Ask them to use

Left and Right as below to fix the Day:

PFE Chapter 29, Functions in Excel page 32

ABCDEFG

Date

Interest

rate

Month Day Year

JAN. 07,1991 6.721 JAN 07 1991

FEB. 07,1991 6.145 Feb 07 1991

FEB. 11,1991 6.03 FEB 11 1991

MAR. 04,1991 6.287 MAR 04 1991

APR. 01,1991 5.985 APR 01 1991

JUN. 08,1991 5.777 JUN 08 1991

AUG. 15,1991 5.744 AUG 15 1991

SEPT. 27,1991 5.868 SEP 27 1991

=LEFT(A11,3)

=LEFT(RIGHT(A11,7),2)

=RIGHT(A11,4)