Simon Benninga. Financial Modelling 3-rd edition

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

828 Chapter 34

34.2.2 MMult and MInverse: Multiplying and Inverting Matrices

These two functions have been discussed in Chapter 31, so we only

recapitulate brieﬂ y:

•

MMult(range1,range2) multiplies the matrix in range1 times that in

range2. Of course, this is only possible if the number of columns in range1

equals the number of rows in range2.

•

MInverse(range) calculates the inverse of the matrix in range. Note

that range must be rectangular.

34.2.3 Frequency

The Excel array function Frequency(data_array,bins_array) allows us to

calculate the frequency distribution of a data set. The following spread-

sheet shows monthly return data for Ford stock over the period January

1977–December 2006. In column E we have put the bins, taking care that

the ﬁ rst bin will be below the minimum monthly return over the period

and that the last bin will be above the maximum monthly return. The

range F8:F38 contains the array function Frequency (C4:C363,E8:E38).

From the output we can, for example, deduce that in the 20-year period

there were two monthly returns between −25.71 and −23.71 percent, and

33 monthly returns between 4.29 and 6.29 percent.

ABC D E F G H I

Date

Ford

stock

price

Monthly

return

3-Jan-77 0.50

1-Feb-77 0.48 -4.08% <

=LN(B4/B3) Minimum -24.71% <

=MIN(C4:C363)

1-Mar-77 0.45 -6.45% Maximimum 32.48% <

=MAX(C4:C363)

1-Apr-77 0.47 4.35%

2-May-77 0.46 -2.15%

Bin Frequency

1-Jun-77 0.50 8.34% -25.71% 0 <

{=FREQUENCY(C4:C363,E8:E38)}

1-Jul-77 0.48 -4.08% -23.71% 2

1-Aug-77 0.47 -2.11% -21.71% 1

1-Sep-77 0.50 6.19% -19.71% 2

3-Oct-77 0.47 -6.19% -17.71% 6

1-Nov-77 0.48 2.11% -15.71% 3

1-Dec-77 0.50 4.08% -13.71% 5

3-Jan-78 0.47 -6.19% -11.71% 8

1-Feb-78 0.47 0.00% -9.71% 10

1-Mar-78 0.51 8.17% -7.71% 18

3-Apr-78 0.58 12.86% -5.71% 23

1-May-78 0.56 -3.51% -3.71% 26

1-Jun-78 0.53 -5.51% -1.71% 35

3-Jul-78 0.53 0.00% 0.29% 35

1-Aug-78 0.51 -3.85% 2.29% 37

1-Sep-78 0.53 3.85% 4.29% 30

2-Oct-78 0.48 -9.91% 6.29% 33

1-Nov-78 0.49 2.06% 8.29% 22

1-Dec-78 0.50 2.02% 10.29% 21

2-Jan-79 0.50 0.00% 12.29% 9

1-Feb-79 0.50 0.00% 14.29% 13

1-Mar-79 0.53 5.83% 16.29% 5

2-Apr-79 0.55 3.70% 18.29% 4

1-May-79 0.53 -3.70% 20.29% 5

1-Jun-79 0.53 0.00% 22.29% 2

2-Jul-79 0.53 0.00% 24.29% 1

1-Aug-79 0.55 3.70% 26.29% 1

4-Sep-79 0.56 1.80% 28.29% 0

1-Oct-79 0.48 -15.42% 30.29% 2

1-Nov-79 0.40 -18.23% 32.29% 0

3-Dec-79 0.42 4.88% 34.29% 1

2-Jan-80 0.45 6.90%

1-Feb-80 0.41 -9.31%

3-Mar-80 0.39 -5.00%

1-Apr-80 0.34 -13.72%

1-May-80 0.34 0.00%

2-Jun-80 0.34 0.00%

1-Jul-80 0.40 16.25%

1-Aug-80 0.39 -2.53%

2-Sep-80 0.39 0.00%

1-Oct-80 0.37 -5.26%

3-Nov-80 0.32 -14.52%

1-Dec-80 0.29 -9.84%

2-Jan-81 0.29 0.00%

2-Feb-81 0.29 0.00%

2-Mar-81 0.35 18.81%

1-Apr-81 0.32 -8.96%

1-May-81 0.34 6.06%

1-Jun-81 0.35 2.90%

1-Jul-81 0.32 -8.96%

3-Aug-81 0.30 -6.45%

1-Sep-81 0.30 0.00%

1-Oct-81 0.25 -18.23%

2-Nov-81 0.25 0.00%

1-Dec-81 0.25 0.00%

4-Jan-82 0.28 11.33%

1-Feb-82 0.29 3.51%

1-Mar-82 0.32 9.84%

1-Apr-82 0.34 6.06%

3-May-82 0.35 2.90%

1-Jun-82 0.37 5.56%

1-Jul-82 0.34 -8.46%

2-Aug-82 0.42 21.13%

1-Sep-82 0.39 -7.41%

THE FREQUENCY ARRAY FUNCTION

Frequency Distribution

of Ford Returns

Jan 1977 - Dec 2006

35 35

-25.7% - -23.7%

-23.7% - -21.7%

-21.7% - -19.7%

-19.7% - -17.7%

-17.7% - -15.7%

-15.7% - -13.7%

-13.7% - -11.7%

-11.7% - -9.7%

-9.7% - -7.7%

-7.7% - -5.7%

-5.7% - -3.7%

-3.7% - -1.7%

-1.7% - 0.3%

0.3% - 2.3%

2.3% - 4.3%

4.3% - 6.3%

6.3% - 8.3%

8.3% - 10.3%

10.3% - 12.3%

12.3% - 14.3%

14.3% - 16.3%

16.3% - 18.3%

18.3% - 20.3%

20.3% - 22.3%

22.3% - 24.3%

24.3% - 26.3%

26.3% - 28.3%

28.3% - 30.3%

30.3% - 32.3%

32.3% - 34.3%

830 Chapter 34

34.3 Homemade Array Functions

In our experience array functions often arise out of situations where you

are called upon to do long, repetitive calculations. You then discover that

the same calculation can be done in a single array function. In many cases

it is not clear why a particular array technique should work. For example,

in this chapter we will use the fact that A3+B6:B8 adds the contents of

cell A3 to each of cells B6:B8. Why? Heaven only knows! We also use

the (undocumented, as far as we are aware) trick that B3:B7^A3:A7

raises cell B3 to the power A3, cell B4 to the power A4, . . . .

In this section we illustrate homemade array functions with two exam-

ples having to do with investment returns.

34.3.1 Computing the Compound Annual Return from 10 Years of Return Data

The following table gives the annual returns for the Podunk University

endowment. You are asked to compute the compound annual return over

the 10-year period. Assuming that the returns have been discretely

computed (meaning that

−

Endowment value

t = 1, . . . , 10), you

realize that the compounded annual return is r = [(1 + r

)

(1 + r

)

. . .

(1 + r

))

1/10

− 1. In cell B14, we do this calculation with a single array

function:

CBA

Year

Return for

the

year

1 25.80%

2 20.50%

3 12.20%

4 32.20%

5 -2.70%

6 -0.50%

7 12.50%

8 21.10%

9 19.20%

10 16.70%

Using an array formula 15.23% <

{=PRODUCT(1+B3:B12)^(1/10)-1}

PODUNK UNIVERSITY ENDOWMENT RETURNS

831 Using Array Functions and Formulas

The Excel function Product multiplies the entries in a range of cells.

The entry in cell B14 adds 1 to each cell in B3:B13, multiplies the cells,

takes the tenth root, and subtracts 1 from the result—all in one cell

(entered, of course, with [Ctrl] + [Shift] + [Enter]).

34.3.2 Computing the Compound Annual Continuous Return

Column B of the next spreadsheet gives the amounts that accumulated

in a customer account of the Youngtalk Investment Fund. The annual

continuously compounded return is computed by

⎛

⎝

⎜

⎞

⎠

⎟

−

ln ,

Account

and the average return over the period is

∑

. In cell B15 we do this

calculation by averaging the annual returns, and in cell B16 we show an

array function that does the whole calculation in a single cell. Pretty

neat!

DCBA

Year

Investment

beginning of

year

Continuous

return for

year

1996 100.00

1997 121.51 19.48% <

=LN(B4/B3)

1998 132.22 8.45%

1999 98.63 -29.31%

2000 75.65 -26.53%

2001 140.48 61.90%

2002 221.40 45.49%

2003 243.46 9.50%

2004 280.11 14.02%

2005 398.72 35.31%

2006 543.58 30.99%

Compound annual return 16.93% <

=AVERAGE(C4:C13)

Same calculation with

array function

16.93% <

{=AVERAGE(LN(B4:B13/B3:B12))}

16.93% <

=LN(B13/B3)/10 , even simpler!

YOUNGTALK INVESTMENT FUND

A ﬁ nal note: Look at cell C17—if you know some continuous-time

mathematics, you will know that ln(B13/B3)/10 produces the same result.

Simpler yet!

832 Chapter 34

34.3.3 Computing

We are given a set of interest rates r

, r

, . . . , and we want to compute

the formula

∑

1()

This issue comes up in section 27.5. Excel’s NPV

function won’t work for this purpose, so we’ll have to build our own

function. The following spreadsheet shows two methods:

CBAD

Year

Interest

rate

6.23%

4.00%

4.20%

4.65%

4.80%

Present value 4.3746 <

{=SUM(1/((1+B3:B7)^A3:A7))}

Checking the formula with a recursive formula

Year

Interest

rate

Sum of 1/(1+r

)

6.23% 0.9414 <

=1/(1+B13)^A13

4.00% 1.8659 <

=C13+(1/(1+B14)^A14)

4.20% 2.7498 <

=C14+(1/(1+B15)^A15)

4.65% 3.5836

4.80% 4.3746 <

=C16+(1/(1+B17)^A17)

COMPUTING PRESENT-VALUE FACTORS

WITH AN ARRAY FUNCTION

In cell B9 we use an array function {= Sum(1/((1+B3:B7)^A3:A7))}.

Writing (1 + B3:B7)^A3:A7) adds 1 to each of cells B3:B7 and raises the

result to the power in cells A3:A7. Applying Sum gives the result (of

course, this being an array function, you have to enter it with [Ctrl] +

[Shift] + [Enter]).

An alternative, given in rows 13–17, is to build the result recursively.

This gives the same result, but with more work.

833 Using Array Functions and Formulas

34.4 Array Formulas with Matrices

In this section we create some array functions that have to do with

matrices.

34.4.1 Subtracting a Constant from a Matrix

In the portfolio computations of Chapters 9–13, we often have to sub-

tract a constant from a matrix. This task is easy with an array formula:

Of course, since this is an array formula, you enter it with [Ctrl] +

[Shift] + [Enter]. Here is the result:

834 Chapter 34

ABCDE

1 6 Constant 3

510

-2 3

-1 3 <

{=A3:B7-E3}

Matrix

SUBTRACTING A CONSTANT FROM

A MATRIX

Matrix minus

constant

As in all array formulas and functions, the result is dynamic: Changing

entries in the source changes the target:

ABCDE

1 6 Constant 16

510

-15 -10

-14 -10 <

{=A3:B7-E3}

-13 -8

-12 -7

-11 -6

Matrix

SUBTRACTING A CONSTANT FROM

A MATRIX

Matrix minus

constant

835 Using Array Functions and Formulas

34.4.2 Creating a Matrix with Ones on the Diagonal and Zeros Elsewhere

This is a problem that comes up in Chapter 10: We want a matrix that

has a diagonal of 1’s but has zero off-diagonal elements. The following

spreadsheet shows three ways of accomplishing this result:

ABCDE F

ABCD

1000

0100

0010

0001

ABCD

1000

0100

0010

0001

1000

0100

0010

0001

CREATING A MATRIX OF 1'S AND 0'S

We want 1 on diagonal, and 0 elsewhere

This problem comes up in Chapter 10

Creating a diagonal matrix of 1's. The following cells contain the array formula

=IF(B3:E3=A4:A7,1,0)}

Creating a diagonal matrix of 1's. The following cells contain the array formula

=IF(B$10=$A11,1,0)}

Creating a diagonal matrix of 1's when there are no borders. The following cells

contain the array formula =IF(ROW()-ROW($B$17)=COLUMN()-

COLUMN($B$17),1,0)}

The ﬁ rst and second methods rely on the labeling of the rows and the

columns. In the ﬁ rst example, the formula =IF(B3:E3=A4:A7,1,0) tests

whether the row label equals the column heading; if this is true, we put

a 1 in the cell, and otherwise we put in a 0. In the second example, we

use an If with mixed absolute and relative references to create the same

effect. In the third example, there are no column or row labels, and we

rely on the functions Column and Row to test the equality of the relative

row and column places in the matrix.

836 Chapter 34

34.4.3 Finding the Maximum and Minimum Off-Diagonal Elements of a Matrix

We want to ﬁ nd the maximal and minimal elements of the off-diagonal

elements of a matrix. Two ways to do so are illustrated in the following

spreadsheet:

ABCDE F

ABCD

10 2 3 4

-3 20 4 -3

15606

4 2 -10 25

ABCD

234<

{=IF(B3:E3=A4:A7,"",B4:E7)}

-3 4 -3

15 6

4 2 -10

Max of off-diagonals 6 <

=MAX(B11:E14)

Min of off-diagonals -10 <

=MIN(B11:E14)

Using only nonarray formulas

ABCD

234<

=IF(E$3=$A4,"",E4)

-3 4 -3

15 6

4 2 -10

Max of off-diagonals 6 <

=MAX(B23:E26)

Min of off-diagonals -10 <

=MIN(B23:E26)

RANGE below contains array formula

=IF(B3:E3=A4:A7,"",B4:E7)

Range below contains the nonarray formula

=IF(B$3=$A4,"",B4)

The source matrix

FINDING THE MAX,MIN OF

OFF-DIAGONAL ELEMENTS OF A MATRIX

the long way

In this example, we ﬁ rst use an array function to replace all the diago-

nal elements with a blank cell. We can then use Max and Min to deter-

mine the extreme off-diagonal elements. As shown in rows 20–29, we

could also have used the nonarray formula =IF(B$3=$A4,“”,B4) in cell

B11 and copied it to the rest of the matrix.

837 Using Array Functions and Formulas

We can also ﬁ nd the maximum and minimum by incorporating the

array formula directly into the Max and Min:

ABCDE

ABCD

10 2 3 4

-3 20 4 -3

15606

42-1025

Max of off-diagonals 6 <

{=MAX(IF(B3:E3=A4:A7,"",B4:E7))}

Min of off-diagonals -10 <

{=MIN(IF(B3:E3=A4:A7,"",B4:E7))}

FINDING THE MAX,MIN OF OFF-DIAGONAL

ELEMENTS OF A MATRIX

In one step

The source matrix

34.4.4 Replacing the Off-Diagonals Using VLookup

Now suppose that we want to replace the off-diagonal elements using a

lookup table, as illustrated in this picture:

ABCDE F

ABCD

A10234

B-320 4-3

C15606

D42-1025

Lookup table for replacements

-10 i

-6 ii

-2 iii

2iv

10 vi

ABCD

A10 iv iv iv <

=IF(E$3=$A4,E4,VLOOKUP(E4,$A$10:$B$15,2))

Bii 20 iv ii

C iii iv 60 v

Div iv i 25

REPLACING THE MAX,MIN OF OFF-DIAGONAL ELEMENTS OF A MATRIX

Long way, nonarray formulas

The following range contains nonarray formula

=IF(B$3=$A4,B4,VLOOKUP(B4,$A$10:$B$15,2)),

which has been copied to all the cells

The source matrix