Basic Financial Calculations

1.1 Overview

This chapter aims to give you some fi nance basics and their Excel imple-mentation. If you have had a good introductory course in fi nance, this chapter is likely to be at best a refresher.1

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 fi nancial problems center on fi nding the value today of a series of cash receipts over time. The cash receipts (or cash fl ows, as we will call them) may be certain or uncertain. The present value of a cash fl ow CFt anticipated to be received at time t is

CF r

t t

(1+ ) . The numerator of this expression is usually understood to be the expected time-t cash fl ow, and the discount rate r in the denominator is adjusted for the riski-ness of this expected cash fl 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 fi 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 fl 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 fi 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/fi nance topics at greater length.

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 fl 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 fi ve payments of $100 each will actually be paid. If a bank pays an annual interest rate of 10 percent on a fi 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 fl ows using this opportunity cost as a discount rate:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 A B C D 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,A9,-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 fl 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

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 fi ve payments of 100 each at the discount rate in cell B2. The PV function returns a negative value for positive cash fl ows; to prevent this unfortunate occurrence, we have made the cash fl ows negative.2

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 fl ows, PV can handle only constant

cash fl ows:

2. This somewhat strange property—returning negative values for positive cash fl 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. 1 2 3 4 5 6 7 8 9 10 11 12 13 A B C D 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

1.2.2 Excel’s NPV Function Is Misnamed!

In standard fi nance terminology, the present value of a series of cash fl ows is the value today of the cash fl ows starting in year 1:

Present value= + =

∑

CF r t t t N (1 ) 1

The net present value is the present value and the cost of acquiring the asset (the cash fl ow at time zero):

Net present value

In many cases CF , meanin = + = = _↑ <

∑

CF r CF t t t N (1 ) 0 0 0 0 gg that it represents the price paid for the asset.

( )  + ₌ CF_+rt t t N 1 1

∑

∑

↑ This is the present value, given by Excel’s

NPV function

  

Excel’s language about discounted cash fl ows differs somewhat from the standard fi nance nomenclature. To calculate the fi nance net present value of a series of cash fl ows using Excel, we have to calculate the present value of the future cash fl ows (using the Excel NPV function), taking into account the time-zero cash fl ow (this is often the cost of the asset in question).

1.2.3 The Net Present Value, NPV

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 CF r t t t N = + + =

∑

0 1(1 )

where CFt is the investment’s cash fl ow at time t and CF0 is today’s cash fl ow.

Suppose, for example that the series of fi ve cash fl ows of $100 is sold for $250. Then, as shown in the following spreadsheet, the NPV = 129.08.

The NPV represents the wealth increment that accrues to the pur-chaser of the cash fl ows. If you buy the series of fi ve cash fl 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 fl ows ought to be zero: Since the present value should correspond to the market price of the cash fl ows, the NPV should be zero. In other words, the market price of our fi ve cash fl 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 The Present Value of an Annuity—Some Useful Formulas

An annuity is a security that pays a constant sum in each period in the future.3 _{Annuities may have a fi nite or infi nite series of payments. If the} annuity is fi nite and the appropriate discount rate is r, then the value today of the annuity is its present value:

PV of fi nite annuity = + + + + + + = − + ⎛ ⎝ ⎜ ⎜ ⎜ ⎞ ⎠ ⎟ ⎟ ⎟ C r C r C r C r r n n 1 1 1 1 1 1 2 ( ) . . . ( ) ( )

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 fi nite annuity: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 A B C D 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 (page ••).

A growing annuity pays out a sum C that grows at a periodic growth rate g. If the annuity is fi nite, its value today is given by

PV of fi nite growing annuity= + + + + + + + + + + + = − + − C r C g r C g r C g r C n n 1 1 1 1 1 1 1 1 1 2 2 3 1 ( ) ( ) ( ) ( ) . . . ( ) ( ) g g r r g n 1+ ⎛ ⎝ ⎞⎠ ⎡ ⎣ ⎢ ⎤ ⎦ ⎥ − 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 C B A Periodic payment, C 1,000

Number of future periods paid, n 5

Discount rate, r 12%

Present value of annuity

Using formula 3,604.78 <-- =B2*(1-1/(1+B4)^B3)/B4

Using Excel's PV function 3,604.78 <-- =PV(B4,B3,-B2)

Period Annuity payment 1 1,000.00 <-- =B2 2 1,000.00 3 1,000.00 4 1,000.00 5 1,000.00

Present value using Excel's NPV function 3,604.78 <-- =NPV(B4,B10:B14)

COMPUTING THE VALUE OF A FINITE ANNUITY

If the annuity promises an infi nite series of constant future payments, then this formula reduces to

PV of infi nite annuity =

+ + + + = C r C r C r 1 ₍1 ₎2 . . . 1 2 3 4 C B A Periodic payment, C 1,000 Discount rate, r 12%

Present value of annuity 8,333.33 <-- =B2/B3

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 C B A First payment, C 1,000

Growth rate of payments, g 6%

Number of future periods paid, n 5

Discount rate, r 12%

Present value of annuity

Using formula 4,010.91 <-- =B2*(1-((1+B3)/(1+B5))^B4)/(B5-B3) Period Annuity payment 1 1,000.00 <-- =B2 2 1,060.00 <-- =$B$2*(1+$B$3)^(A11-1) 3 1,123.60 <-- =$B$2*(1+$B$3)^(A12-1) 4 1,191.02 <-- =$B$2*(1+$B$3)^(A13-1) 5 1,262.48 <-- =$B$2*(1+$B$3)^(A14-1)

Present value using Excel's NPV function 4,010.91 <-- =NPV(B5,B10:B14)

COMPUTING THE VALUE OF A GROWING FINITE ANNUITY

Taking the previous formula and letting n → ∞, we can compute the value of an infi nite growing annuity:

PV of infi nite growing annuity= + + + + + + + + = − + + < C r C g r C g r C r g g r 1 1 1 1 1 1 1 1 2 2 3 ( ) ( ) ( ) ( ) . . . , provided

Here is an illustration in Excel:

This formula can easily be implemented in Excel, and—as shown here— can also be computed using the Excel NPV function:

1 2 3 4 5 C B A

Periodic payment, C 1,000 <-- Starting at date 1

Growth rate of payments, g 6%

Discount rate, r 12%

Present value of annuity 16,666.67 <-- =B2/(B4-B3)

COMPUTING THE VALUE OF A GROWING INFINITE ANNUITY

1.3 The Internal Rate of Return (IRR) and Loan Tables

The internal rate of return (IRR) is defi ned as the compound rate of return r that makes the NPV equal to zero:

CF CF r t t t N 0 1 1 0 + + = =

∑

_{( )}

To illustrate, consider the following example given in rows 2–10. A project costing 800 in year zero returns a variable series of cash fl ows at the end of years 1–5. The IRR of the project (cell B10) is 22.16 percent.

By playing with the discount rate or by using Excel’s Goal Seek, we can determine that at 22.16 percent the NPV in cell B12 is zero:

1 2 3 4 5 6 7 8 9 10 A B C

Year Cash flow

0 -800 1 200 2 250 3 300 4 350 5 400

Internal rate of return 22.16% <-- =IRR(B3:B8)

INTERNAL RATE OF RETURN

1 2 3 4 5 6 7 8 9 10 11 12 A B C Discount rate 12%

Year Cash flow

0 -800 1 200 2 250 3 300 4 350 5 400

Net present value (NPV) 240.81 <-- =B5+NPV(B2,B6:B10)

INTERNAL RATE OF RETURN

Note that the Excel IRR function includes as arguments all the cash fl ows of the investment, including the fi rst—in this case negative—cash fl ow of −800.

1.3.1 Determining the IRR by Trial and Error

There is no simple formula to compute the IRR. Excel’s IRR function uses trial and error, which can be simulated as shown in the following spreadsheet:

FPO

Here’s the way the Goal Seek screen looked before we got the correct answer: 1 2 3 4 5 6 7 8 9 10 11 12 A B C Discount rate 22.16%

Year Cash flow

0 -800 1 200 2 250 3 300 4 350 5 400

Net present value (NPV) 0.00 <-- =B5+NPV(B2,B6:B10)

INTERNAL RATE OF RETURN

1.3.2 Loan Tables and the Internal Rate of Return

The IRR is the compound rate of return paid by the investment. To under-stand this point fully, it helps to make a loan table, which shows the division of the investment’s cash fl ows between investment income and the return of the investment principal:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 A B C D E F

Year Cash flow

0 -800 2 1 00 2 2 50 3 3 00 3 4 50 4 5 00

Internal rate of return 22.16% <-- =IRR(B3:B8)

USING THE IRR IN A LOAN TABLE

Year

Investment at beginning of

year

Cash flow

at end of year Income

Return of principal 1 800.00 200.00 177.28 22.72 <-- =C15-D15 2 777.28 250.00 172.25 77.75 3 699.53 300.00 155.02 144.98 4 554.55 350.00 122.89 227.11 5 327.44 400.00 72.56 327.44 6 0.00

INTERNAL RATE OF RETURN

Division of cash flow between investment income and return of

principal

=-B3

=B15-E15

=$B$10*B15

The remaining investment principal in the year after the last cash flow is zero, indicating that all the principal has been repaid.

The loan table divides each of the cash fl ows of the asset into an income component and a return-of-principal component. The income component at the end of each year is IRR times the principal balance at the beginning of that year. Notice that the principal at the beginning of the last year ($327.44 in the example) exactly equals the return of prin-cipal at the end of that year.

We can actually use the loan table to fi nd the internal rate of return. Consider an investment costing $1,000 today that pays off the cash fl ows indicated at the end of years 1, 2, 5. At a rate of 15 percent (cell B2), the principal at the beginning of year 6 is negative, indicating that too little has been paid out in income. Thus the IRR must be larger than 15 percent:

1 2 3 4 5 6 7 8 9 10 11 12 13 A B C D E F IRR? 15.00% Year Principal at beginning of year Cash flow at end of

year Income Principal

1 1,000.00 300 150.00 150.00 <-- =C6-D6 2 850.00 200 127.50 72.50 3 777.50 150 116.63 33.38 4 744.13 600 111.62 488.38 5 255.74 900 38.36 861.64 6 -605.89

USING A LOAN TABLE TO FIND THE IRR

Division of cash flow between investment income and return of

principal

=$B$2*B6 =B6-E6

If the interest rate in cell B2 is indeed the IRR, then cell B11 should be 0. We can use Excel’s Goal Seek (found on the Tools menu) to cal-culate the IRR:

As shown here, the IRR is 24.44 percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 A B C D E F IRR? 24.44% Year Principal at beginning of year Cash flow at end of

year Income Principal

1 1,000.00 300 244.36 55.64 <-- =C6-D6 2 944.36 200 230.76 -30.76 3 975.13 150 238.28 -88.28 4 1,063.41 600 259.86 340.14 5 723.26 900 176.74 723.26 6 0.00

USING A LOAN TABLE TO FIND THE IRR

Division of cash flow between investment income and return of

principal

=$B$2*B6 =B6-E6

Of course, we could have simplifi ed life by just using the IRR function: 15 16 17 18 19 20 21 22 23 24 A B C D

Year Cash flow

0 -1,000 1 300 2 200 3 150 4 600 5 900 IRR 24.44% <-- =IRR(B17:B22)

1 2 3 4 5 A B C Initial investment 1,000 Periodic cash flow 100

Number of payments 30

IRR 9.307% <-- =RATE(B4,B3,-B2)

USING EXCEL'S RATE FUNCTION TO

COMPUTE THE IRR

1.3.3 Excel’s Rate Function

Excel’s Rate function computes the IRR of a series of constant future payments. In the following example, we pay $1,000 today for an annual payment of $100 for the next 30 years. Rate shows that the IRR is 9.307 percent:

Note: Rate works much like PMT and PV, discussed elsewhere in this chapter; it requires a sign change between the initial investment and the periodic cash fl ow (note that we have used −B2 in cell B5). It also has switches to allow for payments that start today and payments that start one period from now (not shown in the example).

1.4 Multiple Internal Rates of Return

Sometimes a series of cash fl ows has more than one IRR. In the next example we can tell that the cash fl ows in cells B6 : B11 have two IRRs, since the NPV graph crosses the x-axis twice:

Excel’s IRR function allows us to add an extra argument that will help us fi nd both IRRs. Instead of writing = IRR(B6 : B11), we write =

IRR(B6 : B11,guess). The argument guess is a starting point for the

algo-rithm that Excel uses to fi nd the IRR; by adjusting the guess, we can identify both the IRRs. Cells B30 and B31 give an illustration.

There are two things to note about this procedure:

• _{The argument guess merely has to be close to the IRR; it is not unique.} For example by setting the guesses equal to 0.1 and 0.5, we will still get the same IRRs:

29 30 31

A B C D

Identifying the two IRRs

First IRR 8.78% <-- =IRR(B6:B11,0.1) Second IRR 26.65% <-- =IRR(B6:B11,0.5)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 A B C D E F G H I Discount rate 6% NPV -3.99 <-- =NPV(B2,B7:B11)+B6 DATA TABLE Discount rate NPV

Year Cash flow -3.99 Table header, <-- =B3

0 -145 0% -20.00 1 100 3% -10.51 2 100 6% -3.99 3 100 9% 0.24 4 100 12% 2.69 5 -275 15% 3.77 18% 3.80 21% 3.02 24% 1.62 27% -0.24 30% -2.44 33% -4.90 36% -7.53 39% -10.27

Note: For a discussion of how

to create data tables in Excel see Chapter 31.

Identifying the two IRRs

First IRR 8.78% <-- =IRR(B6:B11,0)

Second IRR 26.65% <-- =IRR(B6:B11,0.3)

MULTIPLE INTERNAL RATES OF RETURN

Two IRRs -25.00 -20.00 -15.00 -10.00 -5.00 0.00 5.00 0% 5% 10% 15% 20% 25% 30% 35% 40% Discount rate N et p rese nt val u e

• _{In order to identify the number and the approximate value of the IRRs,} it helps greatly to graph (as we did above) the NPV of the investment as a function of various discount rates. The internal rates of return are then the points where the graph crosses the x-axis, and the approximate location of these points should be used as the guesses in the IRR function.4

From a purely technical point of view, a set of cash fl ows can have multiple IRRs only if it has at least two changes of sign. Many “typical” cash fl ows have only one change of sign. Consider, for example, the cash fl ows from purchasing a bond having a 10 percent coupon, a face value of $1,000, and eight more years to maturity. If the current market price of the bond is $800, then the stream of cash fl ows changes signs only once (from negative in year 0 to positive in years 1–8). Thus there is only one IRR:

4. If you don’t put in a guess (as we did in the example), Excel defaults to a guess of 0. Thus, in this case, IRR(B6 : B11) will return 8.78 percent.

1.5 Flat Payment Schedules

Another common problem is to compute a “fl at” repayment for a loan. For example, you take a loan for $10,000 at an interest rate of 7 percent per year. The bank wants you to make a series of payments that will pay off the loan and the interest over six years. We can use Excel’s PMT function to determine how much each annual payment should be: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 A B C D E F G H I J K

Year Cash flow Data table: Effect of

0 -800 discount rate on NPV

1 100 1,000.00 <-- =NPV(E4,B4:B11)+B3, table header

2 100 0% 1,000.00 3 100 2% 786.04 4 100 4% 603.96 5 100 6% 448.39 6 100 8% 314.93 7 100 10% 200.00 8 1100 12% 100.65 14% 14.45 IRR 14.36% <-- =IRR(B3:B11) 16% -60.62 18% -126.21 20% -183.72

BOND CASH FLOWS: NPV CROSSES x-AXIS ONLY ONCE, SO THERE IS ONLY ONE IRR

NPV of Bond Cash Flows

-400 -200 0 200 400 600 800 1000 1200 0% 5% 10% 15% 20% Discount rate NPV

Notice that we have put a minus sign in the space labeled Pv—Excel’s nomenclature for the initial loan principal. As discussed in footnote 3, if we do not do so, Excel returns a negative payment (a minor irritant). You can confi rm that the answer of $2,097.96 is correct by creating a loan table: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 A B C D E F G 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 7 0.00

FLAT PAYMENT SCHEDULES

=$B$3*C9

=D9-E9 =C9-F9

The zero in cell C15 indicates that the loan is fully repaid over its term of six years. You can easily confi rm that the present value of the pay-ments over the six years is the initial principal of $10,000.

1.6 Future Values and Applications

We start with a triviality. Suppose you deposit $1,000 in an account today, leaving it there for 10 years. Suppose the account draws annual interest of 10 percent. How much will you have at the end of 10 years? The answer, as shown in the following spreadsheet, is $2,593.74:

As cell C17 shows, you don’t need all these complicated calculations: The future value of $1,000 in 10 years at 10 percent per year is given by FV=1 000_, ∗ +₍1 10_%)10=2 593 74_{, .} 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 A B C D E Interest 10% Year Account balance, beginning of year Interest earned during year Total in account, end year 1 1,000.00 100.00 1,100.00 <-- =C5+B5 2 1,100.00 110.00 1,210.00 <-- =C6+B6 3 1,210.00 121.00 1,331.00 4 1,331.00 133.10 1,464.10 5 1,464.10 146.41 1,610.51 6 1,610.51 161.05 1,771.56 7 1,771.56 177.16 1,948.72 8 1,948.72 194.87 2,143.59 9 2,143.59 214.36 2,357.95 10 2,357.95 235.79 2,593.74 11 2,593.74 A simpler way 2,593.74 <-- =B5*(1+B2)^10

SIMPLE FUTURE VALUE

=D5

This problem is easily modeled in Excel:

Thus the answer is that we will have $17,531.17 in the account at the end of year 10. This same answer can be represented as a formula that sums the future values of each deposit:

Total at beginning of year10₌1 000, _{∗ +}1 10%10₊1 000, _{∗ +}1 10%9

+ ( ) ( ) . . .. ( ) ( ) + ∗ + = ∗ + =

∑

1 000 1 10 1 000 1 10 1 1 10 , % , %t t

An Excel Function Note from cell B18 that Excel has a function FV that gives this sum. The dialog box brought up by FV is the following:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 A B C D E F Interest 10%

Annual deposit 1,000 <-- Made today and at beginning of each of next 9 years

Number of deposits 10 Year Account balance, beginning of year Deposit at beginning of year Interest earned during year Total in account, end year 1 0.00 1,000 100.00 1,100.00 <-- =D7+C7+B7 2 1,100.00 1,000 210.00 2,310.00 <-- =D8+C8+B8 3 2,310.00 1,000 331.00 3,641.00 4 3,641.00 1,000 464.10 5,105.10 5 5,105.10 1,000 610.51 6,715.61 6 6,715.61 1,000 771.56 8,487.17 7 8,487.17 1,000 948.72 10,435.89 8 10,435.89 1,000 1,143.59 12,579.48 9 12,579.48 1,000 1,357.95 14,937.42 10 14,937.42 1,000 1,593.74 17,531.17 Future value 17,531.17 <-- =FV(B2,B4,-B3,,1)

FUTURE VALUE WITH ANNUAL DEPOSITS

=$B$2*(B7+C7)

=E7

Now consider the following, slightly more complicated, problem: Again, you intend to open a savings account. Your initial deposit of $1,000 today will be followed by a similar deposit at the beginning of years 1, 2, 9. If the account earns 10 percent per year, how much will you have in the account at the start of year 10?

We note three things about this function:

• _{For positive deposits FV returns a negative number (look back at} footnote 2). This is an irritating property of this function that it shares with PV and PMT. To avoid negative numbers, we have put the Pmt in as −1,000.

• _{The line Pv in the dialog box refers to a situation where the account} has some initial value other than 0 when the series of deposits is made. In this example, this space has been left blank, indicating that the initial account value is zero.

• _{As noted in the picture, “Type” (either 1 or 0) refers to whether the} deposit is made at the beginning or the end of each period (in our example the former is the case).

1.7 A Pension Problem—Complicating the Future-Value Problem

A typical exercise is the following: You are currently 55 years old and intend to retire at age 60. To make your retirement easier, you intend to start a retirement account:

• _{At the beginning of each of years 1, 2, 3, 4 (that is, starting today and} at the beginning of each of the next four years), you intend to make a deposit into the retirement account. You think that the account will earn 8 percent per year.

• _{After retirement at age 60, you anticipate living eight more years.}5 _At the beginning of each of these years you want to withdraw $30,000 from your retirement account. Your account balances will continue to earn 8 percent.

How much should you deposit annually in the account? The following spreadsheet fragment shows how easily you can go wrong in this kind of problem—in this case, you’ve calculated that in order to provide $30,000 per year for eight years, you need to contribute $240,000/5 = $48,000 in each of the fi rst fi ve years. As the spreadsheet shows, you’ll end up with a lot of money at the end of eight years! (The reason—you’ve ignored the powerful effects of compound interest. If you set the interest rate in the spreadsheet equal to 0 percent, you’ll see that you’re right.)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 A B C D E F Interest 8% Annual deposit 48,000.00

Annual retirement withdrawal 30,000.00

=$B$2*(C7+B7) Year Account balance, beginning of year Deposit at beginning of year Interest earned during year Total in account, end year 1 0.00 48,000.00 3,840.00 51,840.00 <-- =D7+C7+B7 2 51,840.00 48,000.00 7,987.20 107,827.20 3 107,827.20 48,000.00 12,466.18 168,293.38 4 168,293.38 48,000.00 17,303.47 233,596.85 5 233,596.85 48,000.00 22,527.75 304,124.59 6 304,124.59 -30,000.00 21,929.97 296,054.56 7 296,054.56 -30,000.00 21,284.36 287,338.93 8 287,338.93 -30,000.00 20,587.11 277,926.04 9 277,926.04 -30,000.00 19,834.08 267,760.12 10 267,760.12 -30,000.00 19,020.81 256,780.93 11 256,780.93 -30,000.00 18,142.47 244,923.41 12 244,923.41 -30,000.00 17,193.87 232,117.28 13 232,117.28 -30,000.00 16,169.38 218,286.66 A RETIREMENT PROBLEM

Note: This problem has five deposits and eight annual withdrawals, all made at the beginning of the year.

The beginning of year 13 is the last year of the retirement plan; if the annual deposit is correctly computed, the balance at the beginning of year 13 after the withdrawal should be zero.

5. Of course you’re going to live much longer! And I wish you good health! The dimen-sions of this problem have been chosen to make it fi t nicely on a page.

There are several ways to solve this problem. The fi rst involves Excel’s

Solver. This can be found on the Tools menu.6

6. If the Solver does not appear on the Tools menu, then you have to load it. Go Tools|Add-Ins and click Solver Add-In on the list of programs. Note that you could also use the Goal Seek tool to solve this problem. For simple problems such as this one, there is not much difference between the Solver and Goal Seek; the one (not inconsiderable) advantage of the Solver is that it remembers its previous arguments, so that if you bring it up again on the same spreadsheet, you can see what you did in the previous iteration. In later chapters we will illustrate problems that cannot be solved by Goal Seek and where the use of the Solver is a necessity. Solver and Goal Seek are compared in Chapter 6.

Clicking on the Solver makes a dialog box appear. In the following illustration we’ve fi lled it in:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 A B C D E F Interest 8% Annual deposit 29,386.55

Annual retirement withdrawal 30,000.00

=$B$2*(C7+B7) Year Account balance, beginning of year Deposit at beginning of year Interest earned during year Total in account, end year 1 0.00 29,386.55 2,350.92 31,737.48 <-- =D7+C7+B7 2 31,737.48 29,386.55 4,889.92 66,013.95 3 66,013.95 29,386.55 7,632.04 103,032.54 4 103,032.54 29,386.55 10,593.53 143,012.62 5 143,012.62 29,386.55 13,791.93 186,191.10 6 186,191.10 -30,000.00 12,495.29 168,686.39 7 168,686.39 -30,000.00 11,094.91 149,781.30 8 149,781.30 -30,000.00 9,582.50 129,363.81 9 129,363.81 -30,000.00 7,949.10 107,312.91 10 107,312.91 -30,000.00 6,185.03 83,497.94 11 83,497.94 -30,000.00 4,279.84 57,777.78 12 57,777.78 -30,000.00 2,222.22 30,000.00 13 30,000.00 -30,000.00 0.00 0.00 A RETIREMENT PROBLEM

1.7.1 Solving the Retirement Problem Using Financial Formulas

We can solve this problem in a more intelligent fashion if we understand the discounting process. The present value of the whole series of pay-ments, discounted at 8 percent, must be zero:

Initial deposit , Initial de ( .1 08) ( . ) 30 000 1 08 0 0 4 5 12 t t= t= t

∑

−

∑

= ⇒ pposit= ⎡ , ⎣⎢ ⎤ ⎦⎥ ⎡ ⎣⎢ ⎤ ⎦⎥ = =

∑

30 000

∑

1 08 1 1 08 5 12 0 4 ( . )t ( . ) t t t

Both the numerator on the right-hand side as 30 000 1 08 5 12 _, ( . )t t=

∑

= 1 1 08 30 000 1 08 4 1 8 ( . ) ( . ) , t t=

∑

and the denominator 1 1 08 0 4 ( . )t t=

∑

can be calculated using Excel’s PV function:

1 2 3 4 5 6 7 8 C B A Interest 8% Annual deposit 48,000.00

Annual retirement withdrawal 30,000.00

Numerator 126,718.54 <-- =1/(1+B2)^4*PV(B2,8,-B4)

Denominator 4.31 <-- =PV(B2,5,-1,,1)

Annual deposit 29,386.55 <-- =B6/B7

A RETIREMENT PROBLEM

Solution using formulas

1.8 Continuous Compounding

Suppose you deposit $1,000 in a bank account that pays 5 percent per year. At the end of the year you will have $1,000 * (1.05) = $1.050. Now suppose that the bank interprets “5 percent per year” to mean that it pays you 2.5 percent interest twice a year. Thus after six months you’ll have $1,025, and after one year you will have $1 000 1 0 05. $ .

2 1 050 625

2

, ∗ +⎛ ,

⎝⎜ ⎞⎠⎟ = . By this logic, if you get paid interest n times per year, your accretion at the end of the year will be $1 000, ∗ +⎛1 0 05.

⎝⎜ n ⎞⎠⎟

n

(rather quickly, as you will soon see) to e0.05_{, which in Excel is written as} the function Exp. When n is infi nite, we refer to this practice as continu-ous compounding. (By typing Exp(1) in a spreadsheet cell, you can see that e = 2.7182818285. . . .)

As you can see in the next display, $1,000 continuously compounded for one year at 5 percent grows to $1,000 * e0.05_{= $1,051.271 at the end} of the year. Continuously compounded for t years, it will grow to $1,000 * e005*t, where t need not be a whole number (for example, when t = 4.25, then the accumulation factor e0.05*4.25 _{measures the growth of the initial} investment at 5 percent annually, continuously compounded for four years and three months).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 C B A Initial deposit 1,000 Interest rate 5%

Number of compounding periods per year 2

Interest per compounding period 2.500% <-- =B3/B4 Accretion in one year 1,050.625 <-- =B2*(1+B5)^B4 Continuous compounding with Exp 1,051.271 <-- =B2*EXP(B3)

Compounding periods per year End-year

accretion 1 1,050.000 <-- =$B$2*(1+$B$3/A25)^A25 2 1,050.625 <-- =$B$2*(1+$B$3/A26)^A26 10 1,051.140 20 1,051.206 50 1,051.245 100 1,051.258 150 1,051.262 300 1,051.267 800 1,051.269

MULTIPLE COMPOUNDING PERIODS

Effect of Multiple Compounding Periods

1,049.80 1,050.00 1,050.20 1,050.40 1,050.60 1,050.80 1,051.00 1,051.20 1,051.40 1 10 100 1000

Number of compounding intervals

En

d

-year accretio

1.8.1 A Technical Note on the Graph

The graph is an Excel XY (Scatter) chart; the x-axis in the chart has been set to be in logarithmic scale. This emphasizes the compounding process. The following picture shows the graph’s x-axis marked and the relevant dialog box (right-click after marking the axis and go to Format

Axis).

1.8.2 Back to Finance—Continuous Discounting

If the accretion factor for continuous compounding at interest r over t years is ert_{, then the discount factor for the same period is e}−rt_{. Thus a cash} fl ow Ct occurring in year t and discounted at continuously compounded rate r will be worth Cte−rt today, as follows:

1 2 3 4 5 6 7 8 9 10 11 A B C D Interest 8%

Year Cash flow

Continously discounted PV 1 100 92.312 <-- =B5*EXP(-$B$2*A5) 2 200 170.429 <-- =B6*EXP(-$B$2*A6) 3 300 235.988 4 400 290.460 5 500 335.160

Present value 1,124.348 <-- =SUM(C5:C9)

CONTINUOUS DISCOUNTING

1.8.3 Calculating the Continuously Compounded Return from Price Data

Suppose at time 0 you had $1,000 in the bank and suppose that one year later you had $1,200. What was your percentage return? Although the answer may appear obvious, it actually depends on the compounding method. If the bank paid interest only once a year, then the return would be 20 percent:

1 200

1 000 1 20 ,

, − = %

However, if the bank paid interest twice a year, you would need to solve the following equation to calculate the return:

1 000 1 2 1 200 2 1 200 1 000 1 9 5445 2 1 2 , , , , % ∗ +⎛_⎝⎜ r⎞_{⎠⎟ = ⇒ = ⎛⎝⎜}r ⎞_{⎠⎟ − =} / .

The annual percentage return when interest is paid twice a year is there-fore 2 * 9.5445% = 19.089%.

In general, if there are n compounding periods per year, you have to solve r n n = ⎛⎝⎜ ⎞⎠⎟ − 1 200 1 000 1 1 , , /

and then multiply the result appropriately. If n

is very large, this converges to r= ⎛⎝⎜ ⎞ ⎠⎟ = ln 1 200 .

1 000 18 2322 ,

1.8.4 Why Use Continuous Compounding?

All this may see somewhat esoteric. However, continuous compound-ing/discounting is often used in fi nancial calculations. In this book, it is used to calculate portfolio returns (Chapters 8–15) and in practically all of the options calculations (Chapters 16–24).

There’s another reason to use continuous compounding—its ease of calculation. Suppose, for example, that your $1,000 grew to $1,500 in one year and nine months. What’s the annualized rate of return? The easiest— and most consistent—way to fi nd this answer is to calculate the continu-ously compounded annual return. Since 1 year and 9 months equals 1.75 years, this return is

1 000 1 75 1 500 1 1 75 1 500 1 000 23 1694 , , , , % ∗ ∗ = ⇒ = ⎡ ⎣⎢ ⎤ ⎦⎥= exp[ . ] . ln . r r 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 A B C Initial deposit 1,000 End-of-year value 1,200

Number of compounding periods 2

Implied annual interest rate 19.09% <-- =((B3/B2)^(1/B4)-1)*B4 Continuous return 18.23% <-- =LN(B3/B2)

Number of compounding periods Rate

19.09% <-- =B5, data table header

1 20.00% 2 19.09% 4 18.65% 8 18.44% 20 18.32% 1,000 18.23%

CALCULATING RETURNS FROM PRICES

1.9 Discounting Using Dated Cash Flows

Most of the computations in this chapter consider cash fl ows that occur at fi xed periodic intervals. Typically we look at cash fl ows that occur on dates 0, 1, . . . , n, where the period indicates an annual, semiannual, or other fi xed interval. Two Excel functions, XIRR and XNPV, allow us to do computations on cash fl ows which occur on specifi c dates that need not be at even intervals.7

In the following example we compute the IRR of an investment of $1,000 made on 1 January 2006 with payments on specifi c dates:

1 2 3 4 5 6 7 8 9 A B C

Date Cash flow

1-Jan-06 -1,000 3-Mar-06 150 4-Jul-06 100 12-Oct-06 50 25-Dec-06 1,000 IRR 37.19% <-- =XIRR(B3:B7,A3:A7)

USING XIRR TO COMPUTE THE

ANNUALIZED INTERNAL RATE OF

RETURN

The function XIRR outputs an annualized return. It works by comput-ing the daily IRR and annualizcomput-ing it, XIRR = (1 + DailyIRR)365_{− 1.}

XNPV computes the net present value of a series of cash fl ows

occur-ring on specifi c dates:

7. If you do not see these functions, add them in by going to Tools|Add-ins on the tool bar and checking Analysis ToolPak.

1 2 3 4 5 6 7 8 9 10 11 12 13 A B C

Annual discount rate 12%

Date Cash flow

1-Jan-06 -1,000

3-Mar-07 100

4-Jul-07 195

12-Oct-08 350

25-Dec-09 800

Net present value 16.80 <-- =XNPV(B2,B5:B9,A5:A9)

USING XNPV TO COMPUTE THE NET

PRESENT VALUE

Note that XNPV has a different syntax from NPV

!

XNPV requires all the cash flows, including the initial cash flow,

whereas NPV assumes that the first cash flow occurs one period hence.

Exercises

1. You are offered an asset costing $600 that has cash fl ows of $100 at the end of each of the next 10 years.

a. If the appropriate discount rate for the asset is 8 percent, should you purchase it?

b. What is the IRR of the asset?

2. You just took a $10,000, fi ve-year loan. Payments at the end of each year are fl at (equal in every year) at an interest rate of 15 percent. Calculate the appropriate loan table, showing the breakdown in each year between principal and interest. 3. You are offered an investment with the following conditions:

• The cost of the investment is $1,000.

• The investment pays out a sum X at the end of the fi rst year; this payout grows at the rate of 10 percent per year for 11 years.

If your discount rate is 15 percent, calculate the smallest X that would entice you to purchase the asset. For example, as you can see in the following display, X _{= $100} is too small—the NPV is negative.

4. The following cash-fl ow pattern has two IRRs. Use Excel to draw a graph of the NPV of these cash fl ows as a function of the discount rate. Then use the IRR func-tion to identify the two IRRs. Would you invest in this project if the opportunity cost were 20 percent?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 A B C Discount rate 15% Initial payment 129.2852 NPV -226.52 <-- =B6+NPV(B1,B7:B17)

Year Cash flow

0 -1000.00 1 100.00 <-- 100 2 110.00 <-- =B7*1.1 3 121.00 <-- =B8*1.1 4 133.10 5 146.41 6 161.05 7 177.16 8 194.87 9 214.36 10 235.79 11 259.37 4 5 6 7 8 9 10 A B

Year Cash flow

0 -500 1 600 2 300 3 300 4 200 5 -1,000

5. In this exercise we solve iteratively for the internal rate of return. Consider an investment that costs 800 and has cash fl ows of 300, 200, 150, 122, 133 in years 1–5 (see cells A8:B13 in the following spreadsheet). Setting up the loan table shows that 10 percent is greater than the IRR (since the return of principal at the end of year 5 is less than the principal at the beginning of the year).

Setting the IRR? cell equal to 3 percent shows that 3 percent is less than the IRR, since the return of principal at the end of year 5 is greater than the principal at the beginning of year 5: 1 2 3 4 5 6 7 8 9 A B C D E F G H IRR? 10.00% LOAN TABLE

Year Cash flow Year

Principal at beginning of year Payment at end of year Interest Principal 0 -800 1 800.00 300.00 80.00 220.00 1 300 2 580.00 200.00 58.00 142.00 2 200 3 438.00 150.00 43.80 106.20 3 150 4 331.80 122.00 33.18 88.82 4 122 5 242.98 133.00 24.30 108.70

5 133 6 134.28 <-- Should be zero for IRR

Division of payment between:

By changing the IRR? cell, fi nd the internal rate of return of the investment.

1 2 3 4 5 6 7 8 9 A B C D E F G H IRR? 3.00% LOAN TABLE

Year Cash flow Year

Principal at beginning of year Payment at end of year Interest Principal 0 -800 1 800.00 300.00 24.00 276.00 1 300 2 524.00 200.00 15.72 184.28 2 200 3 339.72 150.00 10.19 139.81 3 150 4 199.91 122.00 6.00 116.00 4 122 5 83.91 133.00 2.52 130.48

5 133 6 -46.57 <-- Should be zero for IRR

Division of payment between:

6. An alternative defi nition of the IRR is the rate that makes the principal at the beginning of year 6 equal to zero.9 _{In the preceding printout cell E14 gives the}

principal at the beginning of year 6. Using the Goal Seek function of Excel, fi nd the rate that changes this fi gure to zero (the following picture shows how the screen should look).

9. In general, of course, the IRR is the rate of return that makes the principal in the year

(Of course you should check your calculations by using the Excel IRR function.) 7. Calculate the fl at annual payment required to pay off a fi ve-year loan of $100,000

bearing an interest rate of 13 percent.

8. You have just taken a car loan of $15,000. The loan is for 48 months at an annual interest rate of 15 percent (which the bank translates to a monthly rate of 15%/12 = 1.25%). The 48 payments (to be made at the end of each of the next 48 months) are all equal.

a. Calculate the monthly payment on the loan.

b. In a loan table calculate, for each month, the principal remaining on the loan at the beginning of the month and the split of that month’s payment between inter-est and repayment of principal.

c. Show that the principal at the beginning of each month is the present value of the remaining loan payments at the loan interest rate (use the PV function). 9. You are considering buying a car from a local auto dealer. The dealer offers you

one of two payment options: • You can pay $30,000 cash.

• The “deferred payment plan”: You can pay the dealer $5,000 cash today and a payment of $1,050 at the end of each of the next 30 months.

As an alternative to the dealer fi nancing, you have approached a local bank, which is willing to give you a car loan of $25,000 at the rate of 1.25 percent per month. a. Assuming that 1.25 percent is the opportunity cost, calculate the present value

of all the payments on the dealer’s deferred payment plan.

b. What is the effective interest rate being charged by the dealer? Do this calcula-tion by preparing a spreadsheet like this (only part of the spreadsheet is shown— you have to do this calculation for all 30 months):

Now calculate the IRR of the numbers in column F; this is the monthly effective

interest rate on the deferred payment plan.

10. You are considering a savings plan that calls for a deposit of $15,000 at the end of each of the next fi ve years. If the plan offers an interest rate of 10 percent, how much will you accumulate at the end of year 5?

Do this calculation by completing the following spreadsheet. This spreadsheet does the calculation twice—once using the FV function and once using a simple table that shows the accumulation at the beginning of each year.

2 3 4 5 6 7 8 9 10 11 D E F G

Month Cash payment

Payment under deferred payment plan Difference 0 30,000 5,000 25,000 <-- =E3-F3 1 0 1,050 -1,050 <-- =E4-F4 2 0 1,050 -1,050 3 0 1,050 -1,050 H 0 1,050 -1,050 0 1,050 -1,050 0 1,050 -1,050 7 0 1,050 -1,050 8 0 1,050 -1,050 4 5 6 1 2 3 4 5 6 7 8 9 10 11 12 A B C Annual payment 15,000 Interest rate 10% Number of years 5 Total va D $91,576.50 <-- =FV(B2,B3,-B1,,0) Year lue Accumulation at begining of year Payment at end of year Annual interest 1 0 15,000 0.00 2 15,000 15,000 1,500.00 3 31,500 4 5 6

11. Redo the previous calculation, this time assuming that you make fi ve deposits at the beginning of this year and the following four years. How much will you accu-mulate by the end of year 5?

12. A mutual fund has been advertising that, had you deposited $250 per month in the fund for the last 10 years, you would now have accumulated $85,000. Assuming that these deposits were made at the beginning of each month for a period of 120 months, calculate the effective annual return fund investors got.

Hint: Set up the following spreadsheet and then use Goal Seek.

1 2 3 4 5 B A Monthly payment 250 Number of months 120

Effective monthly return?

Accumulation <-- =FV(B4,B2,-B1,,1)

C

The effective annual return can then be calculated in one of two ways:

• (1 + Monthly return)12_{− 1: This is the compound annual return, which is}

prefera-ble, since it makes allowance for the reinvestment of each month’s earnings. • 12*Monthly return: This method is often used by banks.

13. You have just turned 35, and you intend to start saving for your retirement. Once you retire in 30 years (when you turn 65), you would like to have an income of $100,000 per year for the next 20 years. Calculate how much you would have to save between now and age 65 in order to fi nance your retirement income. Make the fol-lowing assumptions:

• All savings draw compound interest of 10 percent per year.

• You make the fi rst payment today and the last payment on the day you turn 64 (30 payments).

• You make the fi rst withdrawal when you turn 65 and the last withdrawal when you turn 84 (20 payments).

14. You currently have $25,000 in the bank, in a savings account that draws 5 percent interest. Your business needs $25,000, and you are considering two options: (a) Use the money in your savings account or (b) borrow the money from the bank at 6 percent, leaving the money in the savings account.

Your fi nancial analyst suggests that solution (b) is better. His logic: The sum of the interest paid on the 6 percent loan is lower than the interest earned at the same time on the $25,000 deposit. His calculations are illustrated in the following spread-sheet. Show that this logic is wrong. (If you think about it, it couldn’t be preferable to take a 6 percent loan when you are getting 5 percent interest from the bank. However, the explanation may not be trivial.)

15. Use XIRR to compute the internal rate of return for the following investment: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 E D C B A F Interest earned 5% Interest paid 6% Initial deposit 25,000 Year Principal at beginning of year Payment at

end of year Interest paid

Repayment of principal

1 25,000.00 13,635.92 1,500.00 12,135.92 <-- =C8-D8 2 12,864.08 13,635.92 771.84 12,864.08

Total interest paid 2,271.84

Year In savings account at beginning of year End-year interest earned In account at end of year 1 25,000.00 1,250.00 26,250.00 2 26,250.00 1,312.50 27,562.50 Interest earned 2,562.50 THE 6% LOAN Savings Account =PMT($B$3,2,-$B$4)

EXERCISE 14, financial analyst's calculations

1 2 3 4 5 6 7 8 A B

Date Cash flow

30-Jun-07 -899 14-Feb-08 70 14-Feb-09 70 14-Feb-10 70 14-Feb-11 70 14-Feb-12 70 14-Feb-13 1,070

16. Use XNPV to value the following investment. Assume that the annual discount rate is 15%. 4 5 6 7 8 9 10 11 A B

Date Cash flow

30-Jun-07 -500 14-Feb-08 100 14-Feb-09 300 14-Feb-10 400 14-Feb-11 600 14-Feb-12 800 14-Feb-13 -1,800