Simon Benninga. Financial Modelling 3-rd edition

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

Preface to the First Edition

Like its predecessor Numerical Techniques in Finance, this book presents

some important ﬁ nancial models and shows how they can be solved

numerically and/or simulated using Excel. In this sense this is a ﬁ nance

“cookbook”; like any cookbook, it gives recipes with a list of ingredients

and instructions for making and baking. As any cook knows, a recipe is

just a starting point; having followed the recipe a number of times, you

can think of your own variations and make the results suit your tastes

and needs.

Financial Modeling covers standard ﬁ nancial models in the areas of

corporate ﬁ nance, ﬁ nancial statement simulation, portfolio problems,

options, portfolio insurance, duration, and immunization. Clear and

concise explanations are provided in each case for the implementation

of the models using Excel. Very little theory is offered except where

necessary to understand the numerical implementations.

While Excel is often inappropriate for high-level, industrial-strength

calculations (portfolios are an example), it is an excellent tool for under-

standing the computational intricacies involved in ﬁ nancial modeling. It

is often the case that the fullest understanding of the models comes by

calculating them, and Excel is one of the most accessible and powerful

tools available for this purpose.

Along the way a lot of students, colleagues, and friends (these are

nonexclusive categories) have helped me with advice and comments.

In particular I would like to thank Olivier Blechner, Miryam Brand,

Elizabeth Caulk, John Caulk, Benjamin Czaczkes, John Ferrari, John P.

Flagler, Kunihiko Higashi, Julia Hynes, Don Keim, Anthony Kim, Ken

Kunimoto, Philippe Nore, Nir Sharabi, Mark Thaler, Terry Vaughn, and

Xiaoge Zhou.

Finally, my thanks go to a wonderful set of editors: Nancy Lombardi,

Peter Reinhart, Victoria Richardson, and Terry Vaughn.

Corporate Finance Models

The seven chapters that open the third edition of Financial Modeling

cover basic problems and techniques in corporate ﬁ nance. Chapters 1

and 2 are both review chapters. Chapter 1 is an introduction to basic

ﬁ nancial calculations using Excel. Almost all of the applications dis-

cussed center on variations of the discounted cash ﬂ ow method. The cost

of capital, discussed in Chapter 2, is the rate at which corporate cash

ﬂ ows are discounted to arrive at enterprise value. Calculating this rate

is not trivial and involves a combination of theoretical models and

numerical computation, both discussed in the chapter.

Chapter 3 shows how to build pro forma models, which simulate the

corporate income statement and balance sheets. Pro forma models are

at the heart of many corporate ﬁ nance applications, including business

plans, credit analyses, and valuations. The models require a mixture of

ﬁ nance, accounting, and Excel. Chapter 4 develops a pro forma model

to value PPG Corporation. The example we develop is typical of an

exercise that accompanies many merger and acquisition valuations.

Chapter 5 shows how to apply the valuation technology to banks; it also

includes a short discussion of applying price-earnings techniques to bank

valuation.

Chapters 6 and 7 discuss the ﬁ nancial analysis of leasing. In Chapter

6 we concentrate on the basic lease/purchase decision using the equiva-

lent loan method. An appendix to Chapter 6 discusses some tax and

accounting considerations relating to leases. Chapter 7 discusses the

ﬁ nancial analysis of leveraged lease arrangements, including a discussion

of the multiple-phases method of FASB 13. The multiple-phases method

rate of return is a hybrid IRR, and Excel can easily be used to calculate

this return.

Basic Financial Calculations

1.1 Overview

This chapter aims to give you some ﬁ nance basics and their Excel imple-

mentation. If you have had a good introductory course in ﬁ nance, this

chapter is likely to be at best a refresher.

This chapter covers

•

Net present value (NPV)

•

Internal rate of return (IRR)

•

Payment schedules and loan tables

•

Future value

•

Pension and accumulation problems

•

Continuously compounded interest

Almost all ﬁ nancial problems center on ﬁ nding the value today of a

series of cash receipts over time. The cash receipts (or cash ﬂ ows, as we

will call them) may be certain or uncertain. The present value of a cash

ﬂ ow CF

anticipated to be received at time t is

()1+

. The numerator

of this expression is usually understood to be the expected time-t cash

ﬂ ow, and the discount rate r in the denominator is adjusted for the riski-

ness of this expected cash ﬂ ow—the higher the risk, the higher the dis-

count rate.

The basic concept in present-value calculations is the concept of

opportunity cost. Opportunity cost is the return that would be required

of an investment to make it a viable alternative to other, similar, invest-

ments. In the ﬁ nancial literature there are many synonyms for opportu-

nity cost, among them discount rate, cost of capital, and interest rate.

When the opportunity cost is applied to risky cash ﬂ ows, we will some-

times call it the risk-adjusted discount rate (RADR) or the weighted

average cost of capital (WACC). It goes without saying that this discount

rate should be risk adjusted, and much of the standard ﬁ nance literature

discusses how to make this adjustment. As illustrated in this chapter,

when we calculate the net present value, we use the investment’s oppor-

tunity cost as a discount rate. When we calculate the internal rate of

1. In my book Principles of Finance with Excel (Oxford University Press, 2006), I have

discussed many basic Excel/ﬁ nance topics at greater length.

4 Chapter 1

return, we compare the calculated return to the investment’s opportunity

cost to judge its value.

1.2 Present Value and Net Present Value

Both concepts, present value and net present value, are related to the

value today of a set of future anticipated cash ﬂ ows. As an example,

suppose we are valuing an investment that promises $100 per year at the

end of this and the next four years. We suppose that there is no doubt

that this series of ﬁ ve payments of $100 each will actually be paid. If a

bank pays an annual interest rate of 10 percent on a ﬁ ve-year deposit,

then this 10 percent is the investment’s opportunity cost, the alternative

benchmark return to which we want to compare the investment. We may

calculate the value of the investment by discounting its cash ﬂ ows using

this opportunity cost as a discount rate:

ABCD

Discount rate 10%

Year Cash flow

Present

value

1 100 90.9091 <-- =B5/(1+$B$2)^A5

2 100 82.6446 <-- =B6/(1+$B$2)^A6

3 100 75.1315 <-- =B7/(1+$B$2)^A7

4 100 68.3013 <-- =B8/(1+$B$2)^A8

5 100 62.0921 <-- =B9/(1+$B$2)^A9

Net present value

Summing cells C5:C9 379.08 <-- =SUM(C5:C9)

Using Excel's NPV function 379.08 <-- =NPV(B2,B5:B9)

Using Excel's PV function 379.08 <-- =PV(B2,5,-100)

COMPUTING THE PRESENT VALUE

The present value, 379.08, is the value today of the investment. In a

competitive market, the present value should correspond to the market

price of the cash ﬂ ows. The spreadsheet illustrates three ways of obtain-

ing this value:

•

Summing the individual present values in cells C5 : C9. To simplify the

copying, note the use of “^” to represent the power and the use of both

5 Basic Financial Calculations

the relative and absolute references; for example: = B5/(1 + $B$2)^A5

in cell C5.

•

Using the Excel NPV function. As we will soon show, Excel’s NPV

function is unfortunately misnamed—it actually computes the present

value and not the net present value (discussed in section 1.2.2).

•

Using the Excel PV function. This function computes the present

value of a series of constant payments. PV(B2,5,-100) is the present

value of ﬁ ve payments of 100 each at the discount rate in cell B2.

The PV function returns a negative value for positive cash ﬂ ows; to

prevent this unfortunate occurrence, we have made the cash ﬂ ows

negative.

1.2.1 The Difference Between Excel’s PV and NPV Functions

The preceding spreadsheet may leave the impression that PV and NPV

perform exactly the same computation. But this is not true—whereas

NPV can handle any series of cash ﬂ ows, PV can handle only constant

cash ﬂ ows:

2. This strange property—returning negative values for positive cash ﬂ ows—is shared by

a number of otherwise impeccable Excel functions such as PMT and PV. The somewhat

convoluted logic which led Microsoft to write these functions this way is not worth

explaining.

ABCD

Discount rate 10%

Year

Cash

flow

Present

value

Present value

of each cash flow

1 100 90.9091 <-- =B5/(1+$B$2)^A5

2 200 165.2893 <-- =B6/(1+$B$2)^A6

3 300 225.3944 <-- =B7/(1+$B$2)^A7

4 400 273.2054 <-- =B8/(1+$B$2)^A8

5 500 310.4607 <-- =B9/(1+$B$2)^A9

Net present value

Summing cells C5:C9 1065.26 <-- =SUM(C5:C9)

Using Excel's NPV function 1065.26 <-- =NPV(B2,B5:B9)

COMPUTING THE PRESENT VALUE

In this example the cash flows are not equal

Either discount each cash flow separately or use Excel's NPV

function

Excel's PV doesn't work for this case

6 Chapter 1

1.2.2 Excel’s NPV Function Is Misnamed!

In standard ﬁ nance terminology, the present value of a series of cash ﬂ ows

is the value today of the cash ﬂ ows starting in year 1:

Present value =

∑

()1

The net present value is the present value and the cost of acquiring the

asset (the cash ﬂ ow at time zero):

Net present value

In many cases

CF , meanin

↑

∑

()1

that it represents the

price paid for the asset.

()



∑∑

↑

This is the present

value, given by Excel’s

NPV function



Excel’s language about discounted cash ﬂ ows differs somewhat from the

standard ﬁ nance nomenclature. To calculate the ﬁ nance net present value

of a series of cash ﬂ ows using Excel, we have to calculate the present

value of the future cash ﬂ ows (using the Excel NPV function), taking

into account the time-zero cash ﬂ ow (this is often the cost of the asset

in question).

1.2.3 Net Present Value

Suppose that the investment of section 1.2 is sold for $400. Clearly it

would not be worth its purchase price, since—given the alternative return

(discount rate) of 10 percent—the investment is worth only $379.08. The

net present value (NPV) is the applicable concept here. Denoting by r

the discount rate applicable to the investment, the NPV is calculated as

follows:

NPV CF

∑

1()

where CF

is the investment’s cash ﬂ ow at time t and CF

is today’s cash

ﬂ ow.

Suppose, for example, that the series of ﬁ ve cash ﬂ ows of $100 is sold

for $250. Then, as shown in the following spreadsheet, the NPV =

129.08.

7 Basic Financial Calculations

The NPV represents the wealth increment that accrues to the pur-

chaser of the cash ﬂ ows. If you buy the series of ﬁ ve cash ﬂ ows of 100

for 250, then you have gained 129.08 in wealth today. In a competitive

market the NPV of a series of cash ﬂ ows ought to be zero: Since the

present value should correspond to the market price of the cash ﬂ ows,

the NPV should be zero. In other words, the market price of our ﬁ ve cash

ﬂ ows of 100—in a competitive market, assuming that 10 percent is the

correct risk-adjusted discount rate—ought to be 379.08.

1.2.4 Present Value of an Annuity—Some Useful Formulas

An annuity is a security that pays a constant sum in each period in the

future.

Annuities may have a ﬁ nite or inﬁ nite series of payments. If the

annuity is ﬁ nite and the appropriate discount rate is r, then the value

today of the annuity is its present value:

PV of ﬁ nite annuity

−

⎛

⎝

⎜

⎞

⎠

⎟

11 1

()

...

()

This formula can also be computed using Excel’s PV function. The fol-

lowing illustration also shows the use of Excel’s NPV function in valuing

a ﬁ nite annuity:

ABCD

Discount rate 10%

Year Cash flow

Present

value

0 -250 -250.00 <-- =B5/(1+$B$2)^A5

1 100 90.91 <-- =B6/(1+$B$2)^A6

2 100 82.64 <-- =B7/(1+$B$2)^A7

3 100 75.13 <-- =B8/(1+$B$2)^A8

4 100 68.30 <-- =B9/(1+$B$2)^A9

5 100 62.09 <-- =B10/(1+$B$2)^A10

Net present value

Summing cells C5:C10 129.08 <-- =SUM(C5:C10)

Using Excel's NPV function 129.08 <-- =B5+NPV(B2,B6:B10)

COMPUTING THE NET PRESENT VALUE

3. All the formulas in this subsection depend on some well-known but oft-forgotten high

school algebra. See box on the Euler formula in Chapter 2 (pp. 41–42).