Principles of Finance with Excel (Основы финансов c Excel)

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

PFE Chapter 1, Time value of money page 10

then deposited $100 and the resulting $1,318.08 earns $79.08 interest during the year,

accumulating to $1,397.16 by the end of year 10.

ABCDE

9 1,049.13 100.00 68.95 1,218.08

10 1,218.08 100.00 79.08 1,397.16

The Excel FV (future value) formula

The spreadsheet of the previous subsection illustrates in a step-by-step manner how

money accumulates in a typical savings plan. To simplify this series of calculations, Excel has a

FV formula which computes the future value of any series of constant payments. This formula

is illustrated in cell C16:

BCDE

Future value

using Excel's

FV function

$1,397.16 <-- =FV(B2,A14,-100,,1)

The

FV function requires as inputs the Rate of interest, the number of periods Nper, and

the annual payment

Pmt. You can also indicate the Type, which tells Excel whether payments

are made at the beginning of the period (type

1 as in our example) or at the end of the period

(type

0).

Exercises 2 and 3 at the end of the chapter illustrate both cases.

PFE Chapter 1, Time value of money page 11

Dialog box for FV function

Excel’s function dialog boxes have room for two types of arguments.

•

Bold faced parameters must be filled in—in the FV dialog box these are the interest

Rate, the number of periods Nper, and the payment Pmt. (Read on to see why we wrote

a negative payment.)

• Arguments which are not bold faced are optional. In the example above we’ve indicated

a 1 for the

Type; this indicates (as shown in the dialog box itself) that the future value is

calculated for payments made at the beginning of the period. Had we omitted this

argument or put in 0, Excel would compute the future value for a series of payments

made at the end of the period; see the next example for an illustration.

Notice that the dialog box already tells us (even before we click on OK) that the future

value of $100 per year for 10 years compounded at 6% is $1397.16.

PFE Chapter 1, Time value of money page 12

Excel note—a peculiarity of the FV function

In the FV dialog box we’ve entered in the payment Pmt in as a negative number, as -100.

The FV function has the peculiarity (shared by some other Excel financial functions) that a

positive deposit generates a negative answer. We won’t go into the (strange?) logic that

produced this thinking; whenever we encounter it we just put in a negative deposit.

PFE Chapter 1, Time value of money page 13

Sidebar: Functions and Dialog Boxes

The dialog box which comes with an Excel function is a handy way to utilize the

function. There are several ways to get to a dialog box. We’ll illustrate with the example of the

FV function in Section 1.1.

Going through the function wizard

Suppose you’re in cell B16 and you want to put the Excel function for future value in the cell.

With the cursor in B16, you move your mouse to the

icon on the tool bar:

PFE Chapter 1, Time value of money page 14

Clicking on the icon brings up the dialog box below. We’ve already chosen the

category to be the Financial functions, and we’ve scrolled down in the next section of the dialog

box to put the cursor on the

FV function.

Clicking OK brings up the dialog box for the

FV function.

A short way to get to the dialog box

If you know the name of the function you want, you can just write it in the cell and then

click the

icon on the tool bar. As illustrated below, you have to write

=FV(

and then click on the

icon—note that we’ve written an equal sign, the name of the function,

and the

opening parenthesis.

Here’s how the spreadsheet looks in this case:

PFE Chapter 1, Time value of money page 15

Look in the text displayed by Excel below cell C16: Some versions of Excel show the

format of the function when you type it in a cell.

One further option

You don’t have to use a dialog box! If you know the format of the function then just type

in its arguments and you’re all set. In the example of Section 1.1 you could just type

=FV(B2,A14,-100,,1) in the cell. Hitting [Enter] would give the answer.

[

END OF SIDEBAR]

PFE Chapter 1, Time value of money page 16

Beginning versus end of period

In the example above you make deposits of $100 at the beginning of each year. In terms

of timing, your deposits are made at dates 0, 1, 2, 3, ..., 9. Here’s a schematic way of looking at

this, showing the future value of each deposit at the end of year 10:

DEPOSITS AT BEGINNING OF YEAR

Beginning

of year 1

Beginning

of year 2

Beginning

of year 3

Beginning

of year 4

Beginning

of year 5

Beginning

of year 6

Beginning

of year 7

Beginning

of year 8

Beginning

of year 9

Beginning

of year 10

End o

year 10

345678910

$100 $100 $100 $100 $100 $100 $100 $100 $100 $100

179.08 <-- =100*(1.06)^10

168.95 <-- =100*(1.06)^9

159.38 <-- =100*(1.06)^8

150.36 <-- =100*(1.06)^7

141.85 <-- =100*(1.06)^6

133.82 <-- =100*(1.06)^5

126.25 <-- =100*(1.06)^4

119.10 <-- =100*(1.06)^3

112.36 <-- =100*(1.06)^2

106.00 <-- =100*(1.06)^1

Total 1397.16 <-- Sum of the above

In the above example and in the previous spreadsheet you made deposits of $100 at the

beginning of each year. Suppose you made 10 deposits of $100 at the end of each year. How

would this affect the accumulation in the account at the end of 10 years?

DEPOSITS AT END OF YEAR

Beginning

of year 1

Beginning

of year 2

Beginning

of year 3

Beginning

of year 4

Beginning

of year 5

Beginning

of year 6

Beginning

of year 7

Beginning

of year 8

Beginning

of year 9

Beginning

of year 10

End o

year 10

345678910

$100 $100 $100 $100 $100 $100 $100 $100 $100 $100

168.95 <-- =100*(1.06)^9

159.38 <-- =100*(1.06)^8

150.36 <-- =100*(1.06)^7

141.85 <-- =100*(1.06)^6

133.82 <-- =100*(1.06)^5

126.25 <-- =100*(1.06)^4

119.10 <-- =100*(1.06)^3

112.36 <-- =100*(1.06)^2

106.00 <-- =100*(1.06)^1

100.00 <-- =100*(1.06)^0

Total 1318.08 <-- Sum of the above

The account accumulation is less in this case (where you deposit at the end of each year)

than in the previous case, where you deposit at the beginning of the year. In the second example,

PFE Chapter 1, Time value of money page 17

each deposit is in the account one year less and consequently earns one year’s less interest. In a

spreadsheet, this looks like:

ABCDEF

Interest 6%

Year

Account

balance,

beg. year

Deposit at

end

of year

Interest

earned

during year

Total in

account at

end of year

1 0.00 100.00 0.00 100.00 <-- =B5+C5+D5

2 100.00 100.00 6.00 206.00 <-- =B6+C6+D6

3 206.00 100.00 12.36 318.36

4 318.36 100.00 19.10 437.46

5 437.46 100.00 26.25 563.71

6 563.71 100.00 33.82 697.53

7 697.53 100.00 41.85 839.38

8 839.38 100.00 50.36 989.75

9 989.75 100.00 59.38 1,149.13

10 1,149.13 100.00 68.95 1,318.08

Future value $1,318.08 <-- =FV(B2,A14,-100)

FUTURE VALUE WITH ANNUAL DEPOSITS

at end of year

=$B$2*B6

=E5

Cell C16 illustrates the use of the Excel

FV formula for this case. In the dialog box for

this formula, we have put in a zero under

Type, which indicates that the payments are made at

the end of each year:

PFE Chapter 1, Time value of money page 18

Dialog box for FV with end-period payments

In the example above we’ve omitted any entry in the

Type box. As indicated on the

dialog box itself, we could have also put a 0 in the

Type box. Meaning: Excel’s default for the

FV function is a deposit at the end of the year.

Some finance jargon and the Excel FV function

An annuity with payments at the end of each period is often called a regular annuity. As

you’ve seen in this section, the value of a regular annuity is calculated with

=FV(B2,A14,-100).

An annuity with payments at the beginning of each period is often called an annuity due and its

value is calculated with the Excel function

=FV(B2,A14,-100,,1).

1.2. Present value

In this section we discuss present value.

PFE Chapter 1, Time value of money page 19

The present value is the value today of a payment (or payments) that will be made in the

future.

Here’s a simple example: Suppose that you anticipate getting $100 in 3 years from your

Uncle Simon, whose word is as good as a bank’s. Suppose that the bank pays 6% interest on

savings accounts. How much is the anticipated future payment worth today? The answer is

()

100

$83.96=

1.06

; if you put $83.96 in the bank today at 6 percent annual interest, then in 3 years

you would have $100 (see the “proof” in rows 9 and 10).

$83.96 is also called the discounted

or present value of $100 in 3 years at 6 percent interest

ABC

SIMPLE PRESENT VALUE CALCULATION

X, future payment 100

n, time of future payment 3

r, interest rate 6%

Present value, X/(1+r)

83.96 <-- =B2/(1+B4)^B3

Proof

Payment today 83.96

Future value in

years

100 <-- =B8*(1+B4)^B3

To summarize:

The present value of $X to be received in n years when the appropriate interest rate is r%

()

The interest rate r is also called the discount rate. We can use Excel to make a table of

how the present value decreases with the discount rate. As you can see—higher discount rates

make for lower present values:

Actually,

()

100

83.96193

1.06

= , but we’ve used Format|Cells|Number to show only 2 decimals.