Simon Benninga. Financial Modelling 3-rd edition

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

858 Chapter 35

In the Number|Custom|Type box we have put in a semicolon. This

preserves the cell contents but prevents them from being seen. Now

when you copy the cells, this is the way they will appear:

DCB

PV of

payments

<-- =B5 , data table header

0% 1,500.00

3% 1,193.79

6% 971.22

9% 806.07

12% 681.09

15% 584.74

18% 509.16

21% 448.90

859 Some Excel Hints

Note the comment in cell D8: We advise you to always annotate your

spreadsheet, so that when you come back to it after a few weeks or

months, you will know that cell C8 really does have something in it!

One ﬁ nal note: To hide a cell that contains a reference to another cell

containing a formula, three semicolons (;;;) may be necessary. In the next

spreadsheet, cell B4 contains the function IF. Cell B6 refers to this cell.

In order to hide B6, we use three semicolons instead of one in the For-

mat|Cells|Number|Custom|Type. (If there’s logic here, it escapes us!)

AB C

3a3

cbbb<

=IF(B2+B3<15,"aaa","bbb")

Cell to be hidden --> <

=B4

HIDING A CELL THAT REFERS TO A

FORMULA

35.12 Formula Auditing

Excel can tell you where you’ve used a cell in your formulas, and which

cells a particular formula depends on. Clicking on Tools|Formula Audit-

ing brings up a menu that allows you to make use of this ability:

860 Chapter 35

Here’s the result:

ABCDEFG

Loan principal 10,000

Interest rate 7%

Loan term 6 <

Number of years over which loan is repaid

Annual payment 2,097.96 <

=PMT(B3,B4,-B2)

Split payment into:

Year

Principal

at beginning

of year

Payment at

end of year

Interest

Return of

principal

1 10,000.00 2,097.96 700.00 1,397.96

2 8,602.04 2,097.96 602.14 1,495.82

3 7,106.23 2,097.96 497.44 1,600.52

4 5,505.70 2,097.96 385.40 1,712.56

5 3,793.15 2,097.96 265.52 1,832.44

6 1,960.71 2,097.96 137.25 1,960.71

70.00

FLAT PAYMENT SCHEDULES

=$B$3*C9

=D9-E9

=C9-F9

In a similar way we can check to see which cells are precedents of a

particular cell:

ABCDEFG

Loan principal 10,000

Interest rate 7%

Loan term 6 <

Number of years over which loan is repaid

Annual payment 2,097.96 <

=PMT(B3,B4,-B2)

Split payment into:

Year

Principal

at beginning

of year

Payment at

end of year

Interest

Return of

principal

1 10,000.00 2,097.96 700.00 1,397.96

2 8,602.04 2,097.96 602.14 1,495.82

3 7,106.23 2,097.96 497.44 1,600.52

4 5,505.70 2,097.96 385.40 1,712.56

5 3,793.15 2,097.96 265.52 1,832.44

6 1,960.71 2,097.96 137.25 1,960.71

70.00

FLAT PAYMENT SCHEDULES

=$B$3*C9

=D9-E9

=C9-F9

861 Some Excel Hints

Formula auditing can help you implement a general rule of good

spreadsheet writing: You should try to avoid cells that don’t have either

precedents or dependents.

35.13 Formatting Millions as Thousands

By using Format|Cells|Custom you can change millions into thousands.

To see where this function is handy, consider the following income

statement:

Sales 31,235,689

Cost of goods sold 15,250,888

Sales, general and administrative 2,356,188

Interest 1,999,824

Profits before taxes 11,628,789

Taxes 4,418,940

Profits after taxes 7,209,849

Income statement

We want to make the income statement appear in thousands. (In other

words, instead of 31,235,689 we will see 31,235.) Here’s how this can be

done:

862 Chapter 35

In the Type box we have indicated #,###,. The comma at the end indi-

cates that we want Excel to drop the last three digits in the number, and

the #,### indicates that we want the remaining numbers to appear with

a comma. This procedure is merely a formatting change—the actual

numbers are not changed: In cell B10 in the following output, we’ve

multiplied the Sales by 2; the result is 62,471,378.

863 Some Excel Hints

Sales 31,236

Cost of goods sold 15,251

Sales, general and administrative 2,356

Interest 2,000

Profits before taxes 11,629

Taxes 4,419

Profits after taxes 7,210

The cells retain their values 62,471,378 <

=2*B2

Income statement

Adding another comma to the Type box (that is, #,###,,) will drop

another three digits.

Introduction to Visual Basic for Applications

Chapters 36–41 (written by Benjamin Czaczkes) cover the Visual Basic

for Applications (VBA) techniques needed in this book. While they are

far from being a complete VBA programming guide, these chapters

should enable you to do a competent job of programming ﬁ nancial

functions.

Chapter 36 introduces VBA and shows you how to write user-deﬁ ned

functions. We have used these functions in several places throughout this

book, including in Chapter 19 (to deﬁ ne the Black-Scholes option prices),

Chapters 22 and 23 (Monte Carlo pricing), and Chapters 25 and 26 (to

deﬁ ne the duration). Chapter 37 discusses types and loops. An example

of the use of a loop is the function that calculates an option’s implied

volatility in Chapter 19. Chapter 38 discusses macros and user interven-

tion, allowing you to write routines that ask the user for input—typically

through a message box to be ﬁ lled in on the spreadsheet. Chapter 39

shows you how to use VBA arrays (used in the lognormal simulations

of Chapter 18). Chapter 40 discusses the use of VBA objects. The last

chapter in this part, Chapter 41, shows how to use VBA to import infor-

mation from the web into Excel.

User-Deﬁ ned Functions with VBA

36.1 Overview

Chapters 36–41 discuss the uses of Excel’s programming language, Visual

Basic for Applications (VBA). VBA provides a complete programming

language and environment fully integrated with Excel and all other

Microsoft Ofﬁ ce applications. In this chapter we introduce user-deﬁ ned

functions, which are used in various places in this book.

The examples and screen shots depict the Excel 2003 working environ-

ment but are fully compatible (unless otherwise noted) with all versions

of Excel using Visual Basic for Applications (Version 5 and above).

36.2 Using the VBA Editor to Build a User-Deﬁ ned Function

A user function is a saved list of instructions for Excel that produces a

value. Once deﬁ ned, a user function can be used inside an Excel work-

sheet like any other function.

In this section we will write our ﬁ rst user-deﬁ ned function. Before you

can perform this task, you need to activate the VBA editor. You can do

so either from the Excel menu (Tools|Macro|Visual Basic Editor) or by

using the keyboard shortcut [Alt] + F11. The result in both cases is a new

window like the following screen shot (your window may look slightly

different, but it will be functionally equivalent):

1. Up to and including Excel 2007.

2. User-deﬁ ned functions are usually attached to a speciﬁ c workbook and are only avail-

able if that workbook is currently open in Excel. One way of having access to a VBA

function across worksheets is to put it in an add-in; see Chapter 40 for an introduction

to add-ins in Excel.