Simon Benninga. Financial Modelling 3-rd edition

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

818 Chapter 33

In cell B3 we count all stock returns that are over 2 percent. In cells

B5:B6 we illustrate a different technique. The cutoff in cell B5 is intro-

duced into CountIf by means of the function Text.

Changing the entry

in cell B5 allows us to count the number of returns above a certain level.

Here is this information in a Data Table:

Here is the resulting table:

HGFEDCBA

Number of returns 52 <

=COUNT(C10:C61)

Returns over 2% 13 <

=COUNTIF(C10:C61,">2%") Data table: number of returns above cutoff

Cutoff 5% Cutoff 2 <

=B6, data table header

Returns over cutoff 2 <

=COUNTIF(C10:C61,">"&TEXT(B5,"0.00%")) 0% 30

1% 21

Date

Merck

price

Return 2% 13

3-Jan-06 31.82 3% 8

9-Jan-06 32.15 1.03% <

=LN(B10/B9) 4% 6

17-Jan-06 31.94 -0.66% 5% 2

23-Jan-06 33.33 4.26% 6% 1

30-Jan-06 33.04 -0.87% 7% 1

6-Feb-06 32.96 -0.24% 8% 1

13-Feb-06 34.63 4.94% 9% 1

21-Feb-06 33.72 -2.66% 10% 0

USING COUNTIF ON MERCK'S WEEKLY STOCK

RETURNS

6. This function and other text functions are discussed in Chapter 35.

819 Excel Functions

Of the 52 weekly returns, 30 are over 0 percent, 21 over 1

percent, . . . .

33.10 Boolean Functions

When you include a question in parentheses, you are setting up a Boolean

function:

ABC

2x2

-y15

Number 25

Is number <= x? FALSE <

=(B5<=B2)

Is number > y? TRUE <

=(B5>B3)

Multiplying 0 <

=B6*B7

BASIC BOOLEAN

FUNCTIONS

In cell B6 we have written =(B5<=B2); this asks whether B5 is less than

or equal to B2: If the answer is positive, Excel returns False, else it returns

True. Multiplying False

True or False

False gives 0 (see cell B9), and

multiplying True

True gives 1:

ABC

2x2

-y15

Number 20

Is number <= x? TRUE <

=(B5<=B2)

Is number > y? TRUE <

=(B5>B3)

Multiplying 1 <

=B6*B7

BASIC BOOLEAN

FUNCTIONS

820 Chapter 33

33.10.1 Using Boolean Functions

Boolean functions can be useful in the most unexpected places. In the

next spreadsheet the ﬁ rst two columns contain the monthly returns for

Marriott for a two-year period. The problem we encounter is counting

the number of returns that are between two arbitrary bounds and taking

the average of the returns that are between these bounds:

ABC DE F G H

Date

Marriott

stock

price Return

7-Jan-05 31.20 How many data points? 24 <

=COUNT(C4:C27)

1-Feb-05 31.65 1.43% <

=LN(B4/B3) Maximal return 8.02% <

=MAX(C4:C27)

1-Mar-05 33.06 4.36% Minimal return -8.02% <

=MIN(C4:C27)

1-Apr-05 31.03 -6.34%

2-May-05 33.40 7.36%

1-Jun-05 33.78 1.13% Upper bound 5%

1-Jul-05 33.91 0.38% Lower bound -2%

1-Aug-05 31.30 -8.01%

1-Sep-05 31.25 -0.16% How many < upper bound? 16 <

=COUNTIF(C4:C27,"<"&G8)

3-Oct-05 29.58 -5.49% How many > lower bound? 19 <

=COUNTIF(C4:C27,">"&G9)

1-Nov-05 32.05 8.02%

1-Dec-05 33.27 3.74%

How many between upper

and lower bounds?

=SUMPRODUCT((C4:C27>G9)*(C4:C27<G8),(C4:

C27>G9)*(C4:C27<G8))

3-Jan-06 33.11 -0.48%

1-Feb-06 33.98 2.59%

Average of returns

between the bounds

1.36%

=SUMPRODUCT((C4:C27>G9)*(C4:C27<G8),C4:C

27)/SUMPRODUCT((C4:C27>G9)*(C4:C27<G8),(C

4:C27>G9)*(C4:C27<G8))

1-Mar-06 34.14 0.47%

3-Apr-06 36.36 6.30%

1-May-06 35.99 -1.02%

1-Jun-06 38.00 5.43%

3-Jul-06 35.07 -8.02%

1-Aug-06 37.61 6.99%

1-Sep-06 38.59 2.57%

2-Oct-06 41.71 7.77%

1-Nov-06 45.09 7.79%

1-Dec-06 47.72 5.67%

3-Jan-07 45.10 -5.65%

USING BOOLEAN FUNCTIONS

In cell G3 we use Count to determine the number of returns. Cells

G11 and G12 use Countif to determine how many returns are below the

upper bound in cell G8 and how many are above the lower bound in cell

G9. But how many of the returns are between the two bounds? You can’t

do it with Countif, but we can do it (cell G14) with a trick involving

Boolean functions:

821 Excel Functions

↑

SUMPRODUCT((C4:C27>G9)*(C4:C27<G8)

Creates a vector of 1’s wherre

the returns are > lower bound (G9)

and 0’s where

the returns are < uppper bound (G9)

Ditto

,(C4:C27>G9)*(C4:C27<G8)

  

↑

 

)

Sumproduct multiplies the two vectors and sums the

↑

rresults.

This gives the number of data points between the two bounds..

 

A similar trick is employed in cell G16 to ﬁ nd the average of the

returns that are between the two bounds:

SUMPRODUCT((C4:C27>G9)*(C4:C27<G8), C4:C27)

SUMPRODUCT((C4:

CC27>G9)*(C4:C27<G8),(C4:C27>G9)*(C4:C27<G8))

Numerator Mu

:lltiplies vector of 1’s and 0’s times the returns, thus sums the

returrns that are between the bounds

Counts the returns that are between thhe bounds

This is all very tricky, but it is also very useful!

33.11 Offset

The function Offset allows us to specify a cell or a block of cells in an

array. It cannot be used by itself—rather, it must be part of another Excel

function. The following example shows a large array of numbers. We

want to sum a four-row, ﬁ ve-column array of the larger array (these

numbers are speciﬁ ed in cells B6 and B7); as speciﬁ ed in cells B3 and

B4, we want this summed array to start below the third row and to the

right of the second column of the large block of numbers:

822 Chapter 33

ABCDEFG

Starting corner

Rows down 3

Columns over 2

Range to be summed

Number of rows 4

Number of columns 5

Sum 811 <

=SUM(OFFSET(A11:H31,B3,B4,B6,B7))

Check 811 <

=SUM(C14:G17)

89 34 72 42 41 89 75 41

33 6 49 7 62 50 38 17

71 69 42 68 39 75 32 77

1 69 8 79 40 8 67 4

70 12 44 48 88 27 38 51

85 023358330175

30 50 16 28 73 4 55 68

35 56 31 24 15 47 89 88

99 31 55 60 45 24 28 3

9372 7759081527

62 56 55 19 73 81 33 76

87 27 80 38 65 61 38 68

10 59 27 81 6 83 51 1

70 88 44 35 70 35 0 82

98 45 17 45 89 19 58 42

83 75 21 13 80 9 18 64

3223 4868852526

76 61 72 28 83 1 32 38

64 87 32 67 50 73 19 83

54 55 57 64 80 29 17 92

12 95 66 59 48 78 87 23

USING OFFSET

The function OFFSET(A11:H31,B3,B4,B6,B7) in cell B8 speciﬁ es a

block of cells within the range A11:H31. This range starts 3 rows below

(the value in cell B3) and 2 columns to the right (the value in cell B4) of

the top left-hand cell of the range A11:H31. The range itself is 4 rows

deep (the value in cell B6) and 5 columns wide (the value in cell B7).

The values in cells B6 and B7 always have to be positive, but the values

in B3 and B4 can be negative as well as positive. In the next example the

initial reference range is B22:H31, and Offset indicates a range that starts

above this block of cells (since the value in B3 is negative):

823 Excel Functions

ABCDEFGH

Starting corner

Rows down -5

Columns over 1

Range to be summed

Number of rows 4

Number of columns 5

Sum 899 <

=SUM(OFFSET(B22:H31,B3,B4,B6,B7))

Check

899 <

=SUM(C17:G20)

89 34 72 42 41 89 75 41

33 6 49 7 62 50 38 17

71 69 42 68 39 75 32 77

169 87940 86746

70 12 44 48 88 27 38 51

85 0233583301752

30 50 16 28 73 4 55 68

35 56 31 24 15 47 89 88

99 31 55 60 45 24 28 3

9372 77590815271

62 56 55 19 73 81 33 76

87 27 80 38 65 61 38 68

10 59 27 81 6 83 51 1

70 88 44 35 70 35 0 82

98 45 17 45 89 19 58 42

83 75 21 13 80 9 18 64

3223 48688525269

76 61 72 28 83 1 32 38

64 87 32 67 50 73 19 83

54 55 57 64 80 29 17 92

12 95 66 59 48 78 87 23

USING OFFSET

with negative value

For an innovative use of Offset, see the chapter on event studies,

section 14.5.

Using Array Functions and Formulas

34.1 Overview

An Excel array function or formula performs an operation on a rectan-

gular block of cells. In the simplest cases, built-in Excel array functions

such as Transpose or MMult take an array and transpose it or take two

matrices and multiply them. Once you get the hang of array functions,

you can design your own array formulas. In this chapter, for example, we

show how to use array formulas to ﬁ nd the minimum or maximum of

the off-diagonal elements of a matrix or to pick out the diagonal of a

matrix—all useful tricks to know when doing portfolio calculations such

as those discussed in Chapters 8–15.

The one critical thing to remember about array functions or formulas

in that they are entered into a spreadsheet by pushing the [Ctrl] + [Shift]

+ [Enter] keys simultaneously; this procedure contrasts with the usual

one whereby we enter a function or formula only by pushing [Enter].

34.2 Some Built-in Excel Array Functions

In this section we discuss some built-in Excel array functions: Transpose,

MMult, MInverse, and Frequency. Other functions are discussed else-

where in this book; for example, the function Linest is discussed in

Chapter 33.

34.2.1 Transpose

Suppose we’re trying to calculate the transpose of a 3 × 2 matrix (3 rows,

2 columns) that is in cells A2:B4 of the spreadsheet.

Excel has a function called Transpose( ), but, like all array functions,

its use requires care:

•

First, block off the cells D3:F4 into which you intend to put the trans-

posed matrix.

826 Chapter 34

•

Now type =Transpose(A2:B4). This will appear in the top left-hand

corner of the blocked-off cells. Of course you can use the usual tricks to

show Excel which cells you want (for instance, pointing or using named

ranges).

At this point your spreadsheet looks like this:

•

When you’ve ﬁ nished typing the formula, don’t press Enter! Instead

use [Control] + [Shift] + [Enter]. This will put the array function into all

of the blocked-off cells.

Here’s what the ﬁ nal product will look like:

ABCDEF G

26 123<

{=TRANSPOSE(A2:B4)}

37 567

USING TRANSPOSE

Notice that the array function is surrounded by curly brackets { }. You

don’t type these in—Excel puts them in automatically.

There is, of course, another way to transpose an array: You can copy

the original array, and then use Edit|Paste Special to transpose the range,

clicking on Transpose:

827 Using Array Functions and Formulas

This will transpose the range, but it will not link the original with the

target range—when you change something in the original range, the

target range is unchanged. The neat thing about Transpose is that it’s a

dynamic function, as are all the array functions and formulas: When you

change one of the initial set of cells, the transposed array also changes.