Simon Benninga. Financial Modelling 3-rd edition

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

228 Chapter 7

7.4.1 Back to the Leveraged-Lease Example

We now apply this same logic to the cash ﬂ ows of the leveraged lease.

AB C DE F

Year

Cash flow at

end of year

Investment at

beginning of period

1 31,000 100,000 <-- =NPV($B$11,B26:$B$30)

2 22,000 77,097 <-- =NPV($B$11,B27:$B$30)

3 16,000 61,339

4 22,000 50,305

5 35,000 32,378

THE INVESTMENT AT THE BEGINNING OF EACH PERIOD IS THE

PRESENT VALUE (USING IRR AS DISCOUNT RATE) OF ALL FUTURE

CASH FLOWS

ABCDEF

IRR 12.46% <-- =IRR(C6:C22)

Attribution of cash flow

Year

Investment at

beginning of

period

Cash flow Income

Repayment of

investment

0 -200,000

1 200,000 49,941 24,913 25,028 <-- =C7-D7

2 174,972 89,774 21,796 67,978

3 106,994 60,666 13,328 47,338

4 59,656 39,487 7,431 32,056

5 27,600 23,827 3,438 20,389

6 7,212 22,352 898 21,454

7 -14,242 20,730 -1,774 22,504

8 -36,746 1,186 -4,577 5,763

9 -42,510 -18,697 -5,295 -13,401

10 -29,108 -20,856 -3,626 -17,230

11 -11,878 -23,231 -1,480 -21,751

12 9,873 -25,843 1,230 -27,073

13 36,945 -28,716 4,602 -33,319

14 70,264 -31,877 8,753 -40,630

15 110,894 -35,354 13,814 -49,168

16 160,062 180,000 19,938 160,062

ATTRIBUTION OF LEVERAGED LEASE CASH FLOWS TO

INCOME AND REPAYMENT OF INVESTMENT

=$B$2*B7

229 The Financial Analysis of Leveraged Leases

Note that—as in our simple example—the IRR of 12.46 percent suc-

cessfully attributes income in such a way that the whole of the investment

is accounted for at the end of the project’s life (after 16 years, for the

case of the leveraged lease). However, note that there are some unusual

features of the table: ﬁ ve of the income ﬁ gures are negative, as are seven

of the “repayment of investment” terms. There are two ways to under-

stand these features:

•

In “mechanical” terms, the only way to make the table work is to have

some negative income numbers. This interpretation, though true, is not

very interesting.

•

In economic terms, the negative income ﬁ gures mean that in some

years the project is not worth holding onto, but that it cannot be given

away. As an example consider the lessor’s position at the beginning of

year 9. Seven years of negative cash ﬂ ows lie ahead. Only in eight years,

in year 16, will the lessor again see a positive cash ﬂ ow from the lease.

A rational lessor would like to give away the lease contract at this point;

the present value of the cash ﬂ ows at the beginning of year 9 at a 10

percent discount rate is −$39,333 (and this includes the realization of the

residual value at the end of year 16!). But of course no rational investor

would take over the contract at the beginning of year 6 unless she were

paid to do so, or unless her discount rate were negative. It is this fact—

that the lessor would have to pay someone to take the contract off her

hands in year 9—that makes us attribute negative income to the project

at this point.

In economic terms the lease at this point is worse than

valueless; it is a burden.

Using our insight that the investment at the beginning of each period

is the present value of all future cash ﬂ ows discounted at the IRR, we

can see this phenomenon in a different way.

4. Negative income attribution in fact starts in year 7, showing that already at this point

the project has negative economic value.

230 Chapter 7

As we shall see in the next section, the negative economic value of

leveraged leases has caused the accounting profession a considerable

headache.

7.5 Accounting for Leveraged Leases: The “Multiple Phases Method”

Financial Accounting Standards Board statement 13 (FASB 13) man-

dates that the lessor in a leveraged lease allocate the cash ﬂ ow from the

lease between income and investment. The logical way to do so would

be to use the IRR of the lease’s cash ﬂ ows in the way illustrated in the

preceding section. But here the promulgators of FASB 13 apparently ran

up against the troublesome facet of human nature that hates to record

a loss even if it is economically warranted. (The implausibility of the

method for leveraged leases mandated in the statement is explained only

AB C D EFGHI

IRR 12.46% <-- =IRR(B5:B21)

Year

Leveraged-

lease cash

flows

Value of

lease cash

flows at

beginning of

period

0-200,000

1 49,941 200,000 <-- =NPV($B$2,B6:$B$21)

2 89,774 174,972 <-- =NPV($B$2,B7:$B$21)

3 60,666 106,994 <-- =NPV($B$2,B8:$B$21)

4 39,487 59,656

5 23,827 27,600

6 22,352 7,212

7 20,730 -14,242

8 1,186 -36,746

9 -18,697 -42,510

10 -20,856 -29,108

11 -23,231 -11,878

12 -25,843 9,873

13 -28,716 36,945

14 -31,877 70,264

15 -35,354 110,894

16 180,000 160,062

ECONOMIC VALUE OF THE LEASE CASH FLOWS:

DISCOUNTING THE CASH FLOWS AT THE IRR

Economic Value of Lease Cash Flows at Beginning of Period

-45,000

-25,000

-5,000

15,000

35,000

55,000

75,000

95,000

115,000

135,000

155,000

175,000

195,000

215,000

0 2 4 6 8 10121416

231 The Financial Analysis of Leveraged Leases

by the assumption that lessors did not want, under any circumstances, to

record economic losses that stemmed from the leases.)

The method that was devised to avoid the reporting of negative income

is sometimes termed the multiple-phases method (MPM). A better term

might have been “bastardized IRR method.”

The fact that a somewhat silly method of recognizing income is used

shouldn’t bother us, since foolishness is rampant in this world. However,

the complexity and the opaqueness of the method have lent it respecta-

bility. (There must be a lesson in this!) A little debunking, in the form of

an explanation, is in order.

Suppose we let the multiple-phases rate of return Q (short for quirky)

be deﬁ ned as follows:

Year MPM Explanation

1 The lessor’s investment in the lease at the

beginning of year 1 is equal to her initial

investment in the lease’s equity. In our

example, Investment(1) = $200,000

This is the same as the

calculation of the IRR.

The lessor’s accounting income from the

lease at the end of year t is

Q ∗

()

{

Investment 1

if this number

otherwise

If income is positive,

MPM follows the

attribution of income

and investment of the

standard IRR method.

Otherwise, income is set

to zero.

The lessor’s investment in the lease at the

beginning of any year t > 1 is deﬁ ned as

Investment(t) = Investment(t − 1)

−[Cash ﬂ ow(t − 1) − Income(t − 1)]

Follows IRR method.

Last year

Cash ﬂ ow(last year) =

Investment(last year)

(1 + Q)

Similar to IRR method.

Calculating the MPM Q: There is no formula to do this. However, it can

be done in Excel by using an iterative method, Solver or Goal Seek (see

next subsection).

232 Chapter 7

7.5.1 Calculating the Multiple-Phases-Method Rate of Return

To calculate the MPM rate Q, we ﬁ rst set up a spreadsheet similar to the

one we used to illustrate the IRR. The one difference is that we have

extended the years to include year 17 (one year after the project ends).

A solution Q should give a zero investment at the beginning of year 17.

The following, for example is not a solution for Q:

ABCDEFG

12.00% <-- Possible MPM rate

Year

Investment at

beginning of

period

Leveraged-

lease cash

flows

Income

Repayment of

investment

1 200,000 49,941 24,000 25,941 <-- =C6-D6

2 174,059 89,774 20,887 68,887

3 105,172 60,666 12,621 48,045

4 57,127 39,487 6,855 32,632

5 24,495 23,827 2,939 20,887

6 3,608 22,352 433 21,919

7 -18,312 20,730 0 20,730

8 -39,042 1,186 0 1,186

9 -40,228 -18,697 0 -18,697

10 -21,531 -20,856 0 -20,856

11 -675 -23,231 0 -23,231

12 22,556 -25,843 2,707 -28,550

13 51,105 -28,716 6,133 -34,849

14 85,954 -31,877 10,314 -42,192

15 128,146 -35,354 15,378 -50,732

16 178,878 180,000 21,465 158,535

17 20,343

MPM METHOD: ATTRIBUTION OF LEVERAGED-LEASE CASH

FLOWS TO INCOME AND REPAYMENT OF INVESTMENT

MPM attribution of cash

flows

=IF($B$2*B6>0,$B$2*B6,0)

All the formulas in this table are the same as in the case of the IRR,

with the exception of the formulas in the income column. For example,

the income in year 1 has the following formula:

=IF($B$2*B6>0,$B$2*B6,0)

Using the Excel Solver, we ﬁ nd the solution for Q. The solver

(Tools|Solver) dialog box looks like this:

233 The Financial Analysis of Leveraged Leases

The target cell $B$22 is the investment at the beginning of year 17; since

this is one year after the project ends, this investment should be zero.

When this method is applied in our case, the solution is as follows:

ABCDEF

10.91% <-- Possible MPM rate

Year

Investment at

beginning of

period

Leveraged-

lease cash

flows

Income

Repayment of

investment

1 200,000 49,941 21,822 28,119 <-- =C6-D6

2 171,881 89,774 18,754 71,019

3 100,862 60,666 11,005 49,661

4 51,201 39,487 5,587 33,901

5 17,301 23,827 1,888 21,939

6 -4,638 22,352 0 22,352

7 -26,990 20,730 0 20,730

8 -47,721 1,186 0 1,186

9 -48,906 -18,697 0 -18,697

10 -30,210 -20,856 0 -20,856

11 -9,354 -23,231 0 -23,231

12 13,877 -25,843 1,514 -27,357

13 41,234 -28,716 4,499 -33,216

14 74,449 -31,877 8,123 -40,001

15 114,450 -35,354 12,488 -47,842

16 162,292 180,000 17,708 162,292

17 0

USING SOLVER TO FIND THE MULTIPLE-PHASES-METHOD (MPM)

RETURN ON A LEVERAGED LEASE

MPM attribution of cash

flows

234 Chapter 7

7.6 Comparing the MPM Rate of Return with the IRR

The MPM rate of return is widely used in the leveraged-leasing industry.

How does it compare with the IRR?

•

In general, the MPM rate of return is less than or equal to the IRR.

The two will be equal if all the lease’s cash ﬂ ows are positive. Otherwise

the MPM rate of return is less than IRR.

•

If the MPM rate of return is less than the IRR, then at some point the

IRR will attribute negative income to the lease, whereas the MPM will

attribute zero income to the lease.

Graphically, for the speciﬁ c example of this chapter, we have

ABCDEFGHIJKLM

Year

IRR

income

MPM

income

1 24,913 21,822

2 21,796 18,754

3 13,328 11,005

4 7,431 5,587

5 3,438 1,888

68980

7 -1,774 0

8 -4,577 0

9 -5,295 0

10 -3,626 0

11 -1,480 0

12 1,230 1,514

13 4,602 4,499

14 8,753 8,123

15 13,814 12,488

16 19,938 17,708

COMPARING THE MPM INCOME WITH THE IRR INCOME

Comparing the MPM Income with the IRR Income

The IRR income is the true economic income

-6,000

-1,000

4,000

9,000

14,000

19,000

24,000

1 3 5 7 9 11 13 15

IRR income

MPM income

7.7 Summary

A leveraged lease is an arrangement whereby the lessor—the owner of

the asset—ﬁ nances his investment with a combination of debt and equity.

In this chapter we have analyzed the equity income of the lessor in a

leveraged lease. The economic analysis of the lease cash ﬂ ows shows that

235 The Financial Analysis of Leveraged Leases

at some point in the lease life the equity owner has negative equity value.

If properly reported, this negative equity should lead to negative account-

ing income. However, the MPM mandated by FASB 13 allows lessors to

report only positive income. We explained the MPM method and showed

how Excel can be used to compute the (otherwise meaningless) MPM

rate of return.

Exercises

1. Reconsider the leveraged-leasing example in this chapter. Show that if depreciation

is straight line over 15 years, then the MPM rate of return is equal to the IRR.

Explain.

2. In the leveraged-lease example of section 7.6, ﬁ nd the lowest lease rental so that

the MPM is equal to the IRR (assume the original depreciation schedule).

Portfolio Models

Modern portfolio theory, which has its origins in the work of Harry

Markowitz, John Lintner, Jan Mossin, and William Sharpe, represents

one of the great advances in ﬁ nance. Chapters 8–15 implement some of

the ideas of these researchers and show you how to compute the stan-

dard portfolio problems in ﬁ nance. In these chapters we make intensive

use of Excel’s matrix functions, array functions, and data tables, which

are also discussed in Chapters 30, 31, and 34.

Chapter 8 reviews the basic mechanics of portfolio calculations. Start-

ing with price data, we calculate asset and portfolio returns. While the

bulk of the chapter deals with the simple two-asset portfolio problem,

the case of multiple assets is discussed in sections 8.4 and 8.5.

Chapter 9 discusses both the theory and the mechanics of the calcula-

tion of efﬁ cient portfolios when there are no restrictions on short sales.

Using Excel’s matrix functions we can calculate two efﬁ cient portfolios,

which can then be used to plot the whole efﬁ cient frontier.

The remaining chapters of this part of the book discuss computational

and implementation issues:

•

In Chapter 10 we show how to use return data to calculate the

variance-covariance matrix. Excel’s matrix-handling capabilities make it

easy to do this calculation.

•

In Chapter 11 we discuss the computation of beta, and we replicate a

simple test of the capital asset pricing model (CAPM). We use some

market data to derive the security market line (SML). We then relate

the results to Roll’s criticism of these tests. Excel makes it easy to do the

regression analysis required for these tests. (Regressions are discussed

in Chapter 33.)

•

The preceding chapters have assumed that portfolio optimizers could

sell securities short. In Chapter 12 we show how to use Excel’s Solver to

compute efﬁ cient portfolios when short sales are not allowed. We also

show how to integrate other portfolio constraints into the optimization

problem.

•

Chapter 13 discusses the Black-Litterman model. This widely used

model takes as its starting point the optimality of the benchmark port-

folio and uses this assumption to derive the market’s expected returns.

The optimizer can then adjust the asset allocation to account for his or

her own opinions.