Simon Benninga. Financial Modelling 3-rd edition

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

838 Chapter 34

Array formulas can simplify this procedure:

ABCDE F

ABCD

A 10234

B -3 20 4 -3

15606

42-1025

Lookup table for replacements

-10 i

-6 ii

-2 iii

2iv

10 vi

ABCD

10 iv iv iv <

{=IF(B3:E3=A4:A7,B4:E7,VLOOKUP(B4:E7,A10:B15,2))}

ii 20 iv ii

iii iv 60 v

iv iv i 25

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

Using array formula

The following range contains array formula

=IF(B3:E3=A4:A7,B4:E7,VLOOKUP(B4:E7,A10:B15,2))

The source matrix

Exercises

1. Use a homemade array function to multiply the vector {1,2,3,4,5} times the constant

2. Use a the array functions Transpose and MMult to multiply the row vector {1,2,3,4,5}

times the column vector

−

⎧

⎨

⎪

⎩

⎪

⎫

⎬

⎪

⎭

⎪

3. The following spreadsheet shows the variance-covariance matrix of six stocks (you

can ﬁ nd this matrix on the ﬁ le fm3_problems34.xls). Use an array function to create

a matrix with only variances on the diagonal and with zeros elsewhere.

839 Using Array Functions and Formulas

4. For exercise 3: Use an array function to create a matrix with zeros on the diagonal

and the covariances off-diagonal.

5. The Fidelity Diversiﬁ ed International Fund’s annual returns are given in the follow-

ing spreadsheet. On the assumption that these returns are discretely computed, use

an array function to compute the compound annual return over the period.

ABCDEFG

GE MSFT JNJ K BA IBM

0.1035

0.0758 0.0222 -0.0043 0.0857 0.1414

MSFT

0.0758

0.1657

0.0412 -0.0052 0.0379 0.1400

JNJ

0.0222 0.0412

0.0360

0.0181 0.0101 0.0455

-0.0043 -0.0052 0.0181

0.0570

-0.0076 0.0122

0.0857 0.0379 0.0101 -0.0076

0.0896

0.0856

IBM

0.1414 0.1400 0.0455 0.0122 0.0856

0.2993

ABCD

Year Return

1998 14.39%

1999 50.65%

2000 -8.96%

2001 -12.99%

2002 -9.37%

2003 42.38%

2004 19.66%

2005 17.23%

2006 22.52%

2007 11.10%

FIDELITY DIVERSIFIED

INTERNATIONAL FUND

1. Discretely compounded: The return in year t is

Fund value

t−

−

. If the returns were

continuously

compounded, then the year-t return would be

Fund value

t−

⎛

⎝

⎜

⎞

⎠

⎟

Some Excel Hints

35.1 Overview

This chapter covers a grab bag of Excel hints dealing with problems and

needs that we sometimes run into. The chapter makes no pretence at

uniformity or extensiveness of coverage. Topics covered include

•

Fast ﬁ lls and copy

•

Graph titles that change when data change

•

Creating multiline cells (useful for putting line breaks in cells and

linked graph titles)

•

Typing Greek symbols

•

Typing sub- and superscripts (but not both)

•

Naming cells

•

Hiding cells

•

Formula auditing

•

Writing on multiple spreadsheets

35.2 Fast Copy: Filling in Data Next to Filled-In Column

Usually, we copy cells by dragging on the ﬁ ll handle of the cell with the

formula. There is sometimes an easier method. Consider the following

situation:

ABC

AUTO FILL/COPY

25<

=B2+3

842 Chapter 35

Now double-click on “ﬁ ll handle” (shown in the following ﬁ gure with the

cross). After double-clicking, the range B5:B10 will automatically ﬁ ll

with the formula in B4.

Here’s the result:

ABC

25<

=B2+3

411

514

617

720

823

AUTO FILL/COPY

Double-clicking on the "fill

handle" of a cell will fill in the rest

of the column provided there's a

filled cell next to it.

843 Some Excel Hints

35.3 Multiline Cells

It is sometimes useful to put a line break in a cell, thus creating a multi-

line cell. Perform this task with [Alt] + [Enter] where you want a line

break.

PUTTING LINE BREAKS IN CELLS

This is a multiline cell. The break was

entered by inserting [Alt]+[Enter] at the

desired break points.

There are, of course, other ways to make a cell multilined. The most

obvious of these is to use Format|Cells|Alignment and check Wrap

text:

Here’s the cell after we word-wrap it. (Note that in the dialogue box

we’ve also set the vertical alignment of the cell to Center.)

844 Chapter 35

35.3.1 Multiline Cells with Text Formulas

Sometimes you want to put a line break in a cell that has text formulas

in it. In the next example, the text formula in cell A4 combines the text

in cells A1 and A2.

The line in this cell runs over into

neighboring cells, but by using

Format|Cells|Alignment

we can word-wrap

the cell.

simon

jack

simonjack <-- =A1&A2

simonjack <-- =A1&CHAR(10)&A2 not correctly formatted

simon

jack

<-- =A1&CHAR(10)&A2 correctly formatted

Formatting for cell A8

845 Some Excel Hints

We can put a line break into the text formula by doing two things:

1. Put Char(10) between A1 and A2—that is, write the formula

=A1&Char(10)&A2 into the cell. Char(10) is the code for a hard line

return.

2. Format the cell with Format|Cells|Alignment and click the box Wrap

text. Now you will have a break between the contents of the two cells.

35.4 Writing on Multiple Spreadsheets

This Excel trick enables you to write in multiple spreadsheets at the same

time.

We hit [Enter] and choose all three spreadsheets:

Now anything we write in one of the sheets is also written in the same

cells of all the others, so that we can produce three identical

spreadsheets:

846 Chapter 35

35.4.1 Moving Multiple Sheets of an Excel Notebook

We write on multiple sheets by holding down [Shift] and marking the

tabs of the relevant spreadsheets. A similar trick works to move multiple

sheets of the same Excel notebook:

•

Mark the multiple sheets by holding down [Shift] and clicking on the

appropriate sheets.

•

Now use Edit|Move or copy sheet to move or copy the sheets to

another location on the same spreadsheet or to a different

spreadsheet.

847 Some Excel Hints

35.5 Text Functions in Excel

Excel lets you change formulas to text. Here are some examples:

CBA

Income 15,000

Tax rate 35%

Taxes owed 5,250 <

=B3*B2

Tax rate as text 35.00% <

=TEXT(B3,"0.00%")

0.4 <

=TEXT(B3,"0.0")

Income as date Jan. 24, 1941 <

=TEXT(B2,"mmm. dd, yyyy")

TEXT FUNCTIONS

Note that you can choose different ways of formatting cell B3 in text

form. In cell B7 we have formatted the tax rate as a percentage with two

decimal points, whereas in cell B8 we have formatted the tax rate as one

decimal, causing it to be rounded off.

Note also the somewhat stupid example in cell B10: Since dates in

Excel are just numbers that express the number of days from January 1,

1900, we can express the income of $15,000 in cell B2 as a date.

In the next section we use text functions to create chart titles that

update themselves.

35.6 Chart Titles That Update

You want to have the chart title change when a parameter on the spread-

sheet changes. For example, in the next spreadsheet, you want the chart

title to indicate the growth rate.